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 1p18Uv-0001vM-Ad for pgsql-general@arkaria.postgresql.org; Fri, 02 Dec 2022 16:02:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p18Us-0004kL-KY for pgsql-general@arkaria.postgresql.org; Fri, 02 Dec 2022 16:02:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p18Us-0004ao-AE for pgsql-general@lists.postgresql.org; Fri, 02 Dec 2022 16:02:42 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p18Uj-0000kb-3J for pgsql-general@lists.postgresql.org; Fri, 02 Dec 2022 16:02:38 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 2B2G2IpK1937425; Fri, 2 Dec 2022 11:02:19 -0500 From: Tom Lane To: Pasi Oja-Nisula cc: pgsql-general@lists.postgresql.org Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour In-reply-to: References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <1579912.1669908194@sss.pgh.pa.us> Comments: In-reply-to Pasi Oja-Nisula message dated "Fri, 02 Dec 2022 16:15:21 +0200" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1937423.1669996938.1@sss.pgh.pa.us> Date: Fri, 02 Dec 2022 11:02:18 -0500 Message-ID: <1937424.1669996938@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Pasi Oja-Nisula writes: > I would be perfectly satisfied, if the sql that produced the procedure > would be stored "as is" read-only copy when it was compiled. As I mentioned upthread, we used to have that behavior (store both compiled form and original text) for column default expressions and CHECK constraint expressions. We got rid of that because it caused far more confusion, and bugs, than it was worth. Not bugs in the database, but faulty behavior in applications that thought they could trust the text form to be accurate. I don't intend to fail to learn from that history. Also, we've *never* had storage of the original text for views. I'm a little mystified by people claiming they use original text for vetting functions when they clearly have no equivalent ability for views ... or are your applications completely view-free? If you want an audit comparison point, I'd suggest capturing the result of pg_get_functiondef or one of its sibling functions just after creating your function. "pg_dump -s" is another pretty credible mechanism for capturing schema details. > If an object > rename makes it invalid, tweak a bit telling so, but don't change the text > until next alter procedure is run. I'm astonished at the number of people who think that poorly-implemented Oracle behavior is something we should emulate. regards, tom lane