Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0NgW-005KAW-7P for pgsql-general@arkaria.postgresql.org; Tue, 07 Nov 2023 15:08:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r0NgU-0094yn-GG for pgsql-general@arkaria.postgresql.org; Tue, 07 Nov 2023 15:08:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0NgU-0094yd-5a for pgsql-general@lists.postgresql.org; Tue, 07 Nov 2023 15:08:06 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0NgR-004PqC-L4 for pgsql-general@lists.postgresql.org; Tue, 07 Nov 2023 15:08:04 +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 3A7F80kr120983; Tue, 7 Nov 2023 10:08:00 -0500 From: Tom Lane To: Alvaro Herrera cc: jian he , pgsql-general list Subject: Re: procedure string constant is parsed at procedure create time. In-reply-to: <202311071239.goklps3d33jz@alvherre.pgsql> References: <202311071239.goklps3d33jz@alvherre.pgsql> Comments: In-reply-to Alvaro Herrera message dated "Tue, 07 Nov 2023 13:39:41 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <120981.1699369680.1@sss.pgh.pa.us> Date: Tue, 07 Nov 2023 10:08:00 -0500 Message-ID: <120982.1699369680@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Alvaro Herrera writes: > On 2023-Nov-07, jian he wrote: >> ----2.this will have errors. > The problem seems to be that the procedure uses a single catalog > snapshot during execution, so the INSERT doesn't see the table that > CREATE has made. It looks to me like fmgr_sql does take a new snapshot per query. The problem is that we do parse analysis of the entire function body before we execute any of it. So (roughly speaking) we can cope with DML changes between statements, but not DDL changes. At one time it was possible to argue that this was a bug, or at least a deficiency we ought to rectify sometime. However, with new-style SQL functions the entire function body is certainly parsed before execution. So now I'd be against changing this aspect of old-style functions: it'd mainly have the result of widening the semantic gap between old style and new style, which doesn't seem like a good thing. (The verification done by check_function_bodies likewise can't work if earlier statements make DDL changes that affect later ones.) As per the advice in the fine manual, use plpgsql for this sort of task. regards, tom lane