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 1r0LNA-0057Rq-JY for pgsql-general@arkaria.postgresql.org; Tue, 07 Nov 2023 12:40:00 +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 1r0LN7-008AdG-Ro for pgsql-general@arkaria.postgresql.org; Tue, 07 Nov 2023 12:39:57 +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 1r0LN6-008Ad5-Ji for pgsql-general@lists.postgresql.org; Tue, 07 Nov 2023 12:39:57 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r0LMx-004OhH-67 for pgsql-general@lists.postgresql.org; Tue, 07 Nov 2023 12:39:53 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.48]) by mailout.nyi.internal (Postfix) with ESMTP id 15EAC5C01E1; Tue, 7 Nov 2023 07:39:45 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute7.internal (MEProxy); Tue, 07 Nov 2023 07:39:45 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1699360785; x= 1699447185; bh=8ZVViGcSlzmgOk5Wh5frvMOljaXaJD00B4ENr7Zz4t4=; b=G Ztlbl5xDGzyBhaGZ42yBFCeWvrcar5cBIJWHgIQNeTMAWB5Lw03+vjookk1yfD1Y 9JccxCmf39DVdjWFAVY5XbU8RaphdpJIBQNvw6iLr8g7dnq+paFQs3evzkk179/P 3RF/6p9V/AKeQPzBovDLWXi7j4yZHK/DKdFiLBpSCf+VreDDv1ZuFetIJE9OZ/3K gfjH+UImBuEDrU6/AQiMn0AkxCjNWoEhQOKJpDB0647vqqRxwELwWw/qUfo2wsJP qEuQVO56XsBI4xzdFsnSgMZGjVrlE/vc46FWlbBXh6umfoUqI+Sj+DL8pko3j+he 1/d+Z6ObhNZEs0lAWi64Q== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrudduiedggeduucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepfffhvfevuffkgggtugfgjgesthekredttddtjeenucfhrhhomheptehlvhgr rhhoucfjvghrrhgvrhgruceorghlvhhhvghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh eqnecuggftrfgrthhtvghrnhepvdektdffudfftdffffehfffhjeejhffgieeuueekjeek fffgudffhfduffffueevnecuffhomhgrihhnpegvnhhtvghrphhrihhsvggusgdrtghomh enucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrlhhv hhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhg X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 7 Nov 2023 07:39:44 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=alvh.no-ip.org; s=schmee; t=1699360781; bh=yPdByOhls8OvhmWn70S5YQFY6bE/iLSdwYlA3cfcUhA=; h=Date:From:To:Cc:Subject:In-Reply-To:From; b=o97GmTUf5V5OVBDQ+nAizQGb67cT+kPDVyiDroSt6Lc5LAzPG/TJejG+k1qK+lCbR M/Ud6jwebc72S2YpEON70WylnupDMbiju5rJDHd4grgLgTOkcClZAXrvjH7na9IpJw uKL1OZ2dvYV4WPua297744WXUHM8PKm0MzmJg8+KjLGx2Iyw+h4lkEdT3JiVsawelz wWCRSa/xQ4Gw9Dpq+qyC6LWtUtkhCiLJ6+qyjbEhRJKpcD+AVe+6M+8bVM8lcvgQFX XnTE/wN2gTy8jKXepLwM/R8/D1/Nv96R2CS4lDs29/JMbSA8lTOAoaSbMYrYWJj3MI OlT46BnJ7GfgQ== Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id DC8C4C1; Tue, 7 Nov 2023 13:39:41 +0100 (CET) Date: Tue, 7 Nov 2023 13:39:41 +0100 From: Alvaro Herrera To: jian he Cc: pgsql-general list Subject: Re: procedure string constant is parsed at procedure create time. Message-ID: <202311071239.goklps3d33jz@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2023-Nov-07, jian he wrote: > ----2.this will have errors. > CREATE or replace PROCEDURE template_create() LANGUAGE SQL AS $proc$ > DROP TABLE if exists test cascade; > CREATE TABLE test(id int4range,valid_at tsrange,name text); > INSERT INTO test VALUES (NULL, tsrange('2018-01-01', > '2019-01-01'), 'null key'); > $proc$; This is because of check_function_bodies. You can get this procedure created if you set it to 0. However, it still won't run: 55493 16.0 1480547=# call template_create(); ERROR: relation "test" does not exist LÍNEA 4: INSERT INTO test VALUES (NULL, tsrange('2018-01-01', ^ CONSULTA: DROP TABLE if exists test cascade; CREATE TABLE test(id int4range,valid_at tsrange,name text); INSERT INTO test VALUES (NULL, tsrange('2018-01-01', '2019-01-01'), 'null key'); CONTEXTO: SQL function "template_create" during startup Duración: 0,278 ms 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. If you create the table beforehand, then DROP and CREATE will both work, but the INSERT will fail claiming that the table that existed before the DROP no longer exists: 55493 16.0 1480547=# CREATE TABLE test(id int4range,valid_at tsrange,name text); CREATE TABLE Duración: 1,251 ms 55493 16.0 1480547=# select 'test'::regclass::oid; oid ──────── 210427 (1 fila) Duración: 0,461 ms 55493 16.0 1480547=# call template_create(); ERROR: relation with OID 210427 does not exist CONTEXTO: SQL function "template_create" statement 3 Duración: 2,207 ms Note we no longer get the "LINE 4" context item, but we do get "statement 3", so we know it's still the insert. (Of course, the table returns to life because the DROP is part of the transaction that gets aborted by the failure). -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/