From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: jian he <jian.universality@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Subject: Re: procedure string constant is parsed at procedure create time.
Date: Tue, 7 Nov 2023 13:39:41 +0100
Message-ID: <202311071239.goklps3d33jz@alvherre.pgsql> (raw)
In-Reply-To: <CACJufxGQcEnMCT7yR8+FV4cd996UwGabYtxwqi7pNzq7tf8ZtQ@mail.gmail.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: alvherre@alvh.no-ip.org, jian.universality@gmail.com
Subject: Re: procedure string constant is parsed at procedure create time.
In-Reply-To: <202311071239.goklps3d33jz@alvherre.pgsql>
References: <CACJufxGQcEnMCT7yR8+FV4cd996UwGabYtxwqi7pNzq7tf8ZtQ@mail.gmail.com> <202311071239.goklps3d33jz@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox