Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0lfx-0008O5-DN for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 15:40:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0lfw-0003aT-BU for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 15:40:36 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0lfv-0003aK-W9 for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 15:40:36 +0000 Received: from dormouse.elm.relay.mailchannels.net ([23.83.212.50]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0lfr-0005W7-3H for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 15:40:35 +0000 X-Sender-Id: hostpapa|x-authuser|martijn@upscene.com Received: from relay.mailchannels.net (localhost [127.0.0.1]) by relay.mailchannels.net (Postfix) with ESMTP id 9F3A65C1ED2 for ; Thu, 1 Dec 2022 15:40:23 +0000 (UTC) Received: from s156.servername.online (unknown [127.0.0.6]) (Authenticated sender: hostpapa) by relay.mailchannels.net (Postfix) with ESMTPA id 91AF45C10EE for ; Thu, 1 Dec 2022 15:40:22 +0000 (UTC) ARC-Seal: i=1; s=arc-2022; d=mailchannels.net; t=1669909222; a=rsa-sha256; cv=none; b=TpM3F90RsXg537yJg9mqVsRgbL+CkRhTCS80/KZcmxPAotBVXhBFc48kpSuF3et8A6LSDt 1k7KxDTcEzjm9+eI++Vm0N+SHsYy21vFnlx1L8clt1FeUt4yNkTVbqTsYenmciHdWQ8d5x CyUabfBazJ9GmfUz+1A7Tg6mDS598ZjGqRSUo+bcOqAGrZUNFYKQqtite6mfK1i0dIUJrr m0S/nQiGgc9YFnRtrDCm+bEuqytakBDnbMBt53TTnKgfE3eYrheGcG9EFXUzMz8xDJuwXd nIv9SBwB8AQYua68E70bvKRIhVsUVvIm56dsCtIkjsgMTHGMk0KhjQeQXifkqw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=mailchannels.net; s=arc-2022; t=1669909222; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references:dkim-signature; bh=OyX2oWBgaByjKc6a/c2R7o9WSpBEmMX/q16hJGNRgCA=; b=aujV9PFEdUEo5P1HrlyuJJ9cHsDbB3d2/tu8EeoKMDHFxHvW0oyjHpcvwJvAhOXOXCZF6x uI1j/DdKSsieUQhq+7FhGhjeq7WAQoXU23PTNFLi7/aqJDfpMSeenfUlIcrDiLZBJ9wzlL Idwb75ujdudnLr7Kpt9vBQdq/aaKXVqhLjsLdM7x1TaZtXyPfUnEl8gT1+KAwUqigv1xu2 z2b68taJelXFj7UKAPnWZmKeI6OPAyyDCOkr2B70Vm6EBAmny0OcZl0Hh689CdsxQQ+ZrH qSnkuS3SdFsA0jN94q+DMQ2+YMncQqzZGxY0VmAZ7wYhfKD8jGIG29wKiFh8SQ== ARC-Authentication-Results: i=1; rspamd-84789cff4b-tmd5s; auth=pass smtp.auth=hostpapa smtp.mailfrom=m.tonies@upscene.com X-Sender-Id: hostpapa|x-authuser|martijn@upscene.com X-MC-Relay: Neutral X-MailChannels-SenderId: hostpapa|x-authuser|martijn@upscene.com X-MailChannels-Auth-Id: hostpapa X-Cold-Illegal: 737ccc5f0fb3bdeb_1669909223176_2342377245 X-MC-Loop-Signature: 1669909223175:1623791667 X-MC-Ingress-Time: 1669909223175 Received: from s156.servername.online (s156.servername.online [204.44.192.71]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384) by 100.109.196.241 (trex/6.7.1); Thu, 01 Dec 2022 15:40:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=upscene.com ; s=default; h=Content-Type:MIME-Version:Date:Subject:In-Reply-To:References: To:From:Message-ID:Sender:Reply-To:Cc:Content-Transfer-Encoding:Content-ID: Content-Description:Resent-Date:Resent-From:Resent-Sender:Resent-To:Resent-Cc :Resent-Message-ID:List-Id:List-Help:List-Unsubscribe:List-Subscribe: List-Post:List-Owner:List-Archive; bh=OyX2oWBgaByjKc6a/c2R7o9WSpBEmMX/q16hJGNRgCA=; b=1I58gK9Uj73gzgEIKFs99MJ0I5 8Dh+FP4anXMtwJW61I6X1f3uqAX7kxgFRDwzmcnDDEnbxO8Kw96pfk9MKPAdpmMyaB0avhUi8BF1j 4VaZWaZ6N8u5K20UTRyaFHUnnAyOAYrgem5riyjc3W+k52IPInOjtvwkBYHWL0D4Z6W30T6fqpJcj 0Bi65E2QRv5nZTQ1Y/BDhJa4zA15tqf+64ClF37lGGuU79lMyOmsXHF7PTax5r9S1vPo2A2r0fBL+ 0bgd0+Zq0L2BiFhflfl3c0EEsZ17q9W0BXaZqlrS6kkFwOCHAaP3XAMMMdDl/U3vGzgpmv0IAEAIN S7r5q52Q==; Received: from [45.144.219.154] (port=56521 helo=misc.upscene.om) by s156.servername.online with esmtpa (Exim 4.95) (envelope-from ) id 1p0lfg-009aSB-Fs for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 07:40:22 -0800 Received: from MARTIJNWS ( [192.168.1.70]) by upscene.com with SMTP (Mailtraq/2.17.7.3598) id UPSC7110E0CE for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 16:40:17 +0100 Message-ID: <7B0BE952E8BD4D1A81576AA86EEFAC67@MARTIJNWS> From: "Martijn Tonies \(Upscene Productions\)" To: References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> In-Reply-To: Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour Date: Thu, 1 Dec 2022 16:40:11 +0100 MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_NextPart_000_0026_01D905A3.94D97E30"; type="multipart/alternative" X-Priority: 3 X-MSMail-Priority: Normal Importance: Normal X-Mailer: Microsoft Windows Live Mail 16.4.3508.205 X-MimeOLE: Produced By Microsoft MimeOLE V16.4.3508.205 X-Hops: 1 X-AuthUser: martijn@upscene.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. ------=_NextPart_000_0026_01D905A3.94D97E30 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0027_01D905A3.94D97E30" ------=_NextPart_001_0027_01D905A3.94D97E30 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) = wrote: Since version 14, the source code for a stored procedure or function = written=20 in plain (compound) SQL, a new feature, is no longer stored in=20 pg_proc.prosrc, instead, there=E2=80=99s an additional column = prosqlbody which=20 returns some kind of pre-parsed SQL which has no use for the user. For database=20 development, including browsing an existing database and know what = each=20 routine can do, this is quite terrible. Frankly, this is not all that compelling. The version controlled source = code should be readily referenced to find out the extended details of = this nature. The function name, and a COMMENT ON comment, provide = arguably sufficient in-database knowledge for cursory browsing = redirecting the reader to the source code for implementation details and = history. Frankly, David, you=E2=80=99re wrong. Although I agree a version = controlled source code is a very good way to maintain a proper version = of your development database, =E2=80=93while- developing and changing = source code, it=E2=80=99s simply not the easiest way to run scripts, = compared to having a tool that allows more interactive development, code = commenting (instead of =E2=80=9CCOMMENT ON=E2=80=9D), SQL Insight, = browsing the current structure and so on. Can you modify the server code to store the original body in = proc.prosrc=20 again? It would be very helpful. I seem to recall that this option had been discussed and rejected when = this feature went in. The parsed body is a feature because its contents = can be updated due to, e.g., renaming of objects. The text source of = the original command would be unable to be updated in the same way and = so it is possible the text and the parsed contents would diverge over = time, which is a situation we do not want to have. Ah yes, automatic renaming of database objects with its dependencies can = be useful. Oracle invalidates routines that use the objects, marks those = routines =E2=80=98invalid=E2=80=99 and they need to be recompiled, that = would fail when the source code references an object that no longer = exists. This also means that you actually know which files you need to = touch in your version controlled source=20 With regards, Martijn Tonies Upscene Productions https://www.upscene.com Database Workbench - developer tool for Oracle, SQL Server, PostgreSQL, = MySQL, InterBase, NexusDB and Firebird. ------=_NextPart_001_0027_01D905A3.94D97E30 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Dec 1, 2022 at = 7:59 AM=20 Martijn Tonies (Upscene Productions) <m.tonies@upscene.com> = wrote:

Since=20 version 14, the source code for a stored procedure or function written =
in=20 plain (compound) SQL, a new feature, is no longer stored in=20
pg_proc.prosrc, instead, there=E2=80=99s an additional column = prosqlbody which=20
returns some kind of pre-parsed SQL which has no use for the=20 user.
 
For=20 database
development, including browsing an existing database and = know=20 what each
routine can do, this is quite = terrible.

 
Frankly, this is not = all that=20 compelling.  The version controlled source code should be readily=20 referenced to find out the extended details of this nature.  The = function=20 name, and a COMMENT ON comment, provide arguably sufficient in-database=20 knowledge for cursory browsing redirecting the reader to the source code = for=20 implementation details and history.
 
 
Frankly, David, you=E2=80=99re wrong. Although I agree a = version controlled=20 source code is a very good way to maintain a proper version of your = development=20 database, =E2=80=93while- developing and changing source code, = it=E2=80=99s simply not the=20 easiest way to run scripts, compared to having a tool that allows more=20 interactive development, code commenting (instead of =E2=80=9CCOMMENT = ON=E2=80=9D), SQL Insight,=20 browsing the current structure and so on.
 
 
 
 
Can=20 you modify the server code to store the original body in proc.prosrc=20
again? It would be very helpful.

 
I seem=20 to recall that this option had been discussed and rejected when this = feature=20 went in.  The parsed body is a feature because its contents can be = updated=20 due to, e.g., renaming of objects.  The text source of the original = command=20 would be unable to be updated in the same way and so it is possible the = text and=20 the parsed contents would diverge over time, which is a situation we do = not want=20 to have.
 
 
Ah yes, automatic renaming of database objects with its=20 dependencies can be useful. Oracle invalidates routines that use the = objects,=20 marks those routines =E2=80=98invalid=E2=80=99 and they need to be = recompiled, that would fail=20 when the source code references an object that no longer exists. This = also means=20 that you actually know which files you need to touch in your version = controlled=20 source 3D"Winking
 
With=20 regards,

Martijn Tonies
Upscene=20 Productions
https://www.upscene.com

Database Workbench - = developer=20 tool for Oracle, SQL Server, PostgreSQL, MySQL, InterBase, NexusDB and=20 Firebird.
------=_NextPart_001_0027_01D905A3.94D97E30-- ------=_NextPart_000_0026_01D905A3.94D97E30 Content-Type: image/png; name="wlEmoticon-winkingsmile[1].png" Content-Transfer-Encoding: base64 Content-ID: iVBORw0KGgoAAAANSUhEUgAAABMAAAATCAYAAAByUDbMAAAAAXNSR0IArs4c6QAAAARnQU1BAACx jwv8YQUAAAAJcEhZcwAADsIAAA7CARUoSoAAAAQESURBVDhPdZR7TJtVGMZPKS0gsA2kzWJNY8jc H5oYzWKicVNn/EMTL4thbmzFgZkjOIMmuK2xMROHgiVmTow6sS7iTEZJNlxNWy5CoVAouCELrFAd l8oo0HIpl9IPaB/f87U1YrY3eZLm63d+3/M+5z2H3alWHJmq5Q6lLmBT2hZaFBNzTVnwWbImpk2Z tqn6TJ2nNlMVe/XuhYGH5CtdSv1ytyIYGn4JG5OFiPiLgfl3Eb6di/WhZ7DSkY3Jum3B8Yvb9ANG Jo8t3VyL17crgt1KpzC2Dwh9Q/oKCFYCSx8BgfcRnnoLvV8egvW4WlTfd8fw1/dbe9zn0hQxRLS4 Iw7amCkAwkZg4ydAOE/As8BKGcFOwlXzOIYul2F13ivq5iUdhs/K4KpKcw6c/o/DlW5leduZB1Dz KvtXbnMewb4gd58Q7BQsRSoIS7OI1+rshOhwoTYZ/eWp5VEQD7tLIWCjll75BYgY4ba8GQX++jrB yNniKZiOpIkQg8EAjUYj/r5ygAHdEtw4kyr8fjpZzZY6FaWhEQ3c1sJNzuJym14m2AlYCrfD77KD vi9q6g+L+AxOhrkLcji1SaUs0Ka0b/g+jOYk6gKwRnkJ56iXCsqM/gu8h9Gre0QncdXnSjFuIGcO BuFKAhwlcjub/y3LG1nURwMX9XU0q9XPqMVS0RUW3gHm8kn0zKsFPEXAyBGgk2AdDGGLBB3Fci+b bcoSsFyJuWEdrLqn4Kh6EUMmDYKTtIiywgKfs6MEeQ249SQwmA1c2wJ0EchOaic1M7QVyQXmt9zr DftPoLVsD64WP4ZbDW8g+HdJzBEHHQOmDwJjzwE3HwauKymnJNERbKQWcmaSwHZU5mX8iKyPHqKF J9Go24Xeb58XM8L8cWqLHHHQOD0begTovx/ouSfaXgzEXa3+KEFzvszGvPUZ2mXnE7SwCGPWV1BX kI1r53cDPsqEtza6Fxh+lEBqoJfac0jhu8Tg+zkKgpXB86kUTYcTtey2MUM9WZchYDKXXORhuvMF 1OWrULMvE4PVOzFj2YG1HhXW7OmYMUoxqGeopZEZqSJQA0PExNCalyg07WdqcXD5oV1u2UHtPEva jcifu+D6QQ3z21tQvVeGip0SVDzIUP00g7mAwUXANYLAzDBWKoX1oFQvgnjx0z9i2OoUWu+jbGi3 BiibPgXC3elYb09CqJEhZGFYJydhEnfEQb5KKRoOJDiN+/93e/DTzw9toC6dQk4mycSBFHeNb38s bJ4Rb2384yjInMM23xrx8nzOUm5UpOr76az5DTKELieIAxnffr5rngopWjSJAm+tNoelxJbevfo+ SFE5tcm6rhK5jU92e5FcnCO+/Y2HE3XNOewONy1j/wBSx0pf+lqmqAAAAABJRU5ErkJggg== ------=_NextPart_000_0026_01D905A3.94D97E30--