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/
