pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedprocedure string constant is parsed at procedure create time.
4+ messages / 4 participants
[nested] [flat]
* procedure string constant is parsed at procedure create time.
@ 2023-11-07 11:52 jian he <jian.universality@gmail.com>
2023-11-07 12:39 ` Re: procedure string constant is parsed at procedure create time. Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-11-07 12:42 ` Re: procedure string constant is parsed at procedure create time. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: jian he @ 2023-11-07 11:52 UTC (permalink / raw)
To: pgsql-general list <pgsql-general@lists.postgresql.org>
hi.
table "test" does not exist.
---1.this will work.
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);$proc$;
----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$;
----3.language plpgsql works fine.
CREATE or replace PROCEDURE template_create()
LANGUAGE plpgsql AS $proc$
begin
DROP TABLE if exists test101 cascade;
CREATE TABLE test101(id int4range,valid_at tsrange,name text);
INSERT INTO test101 VALUES (NULL, tsrange('2018-01-01',
'2019-01-01'), 'null key');
end
$proc$;
per manual: https://www.postgresql.org/docs/current/sql-createprocedure.html
"....This form only works for LANGUAGE SQL, the string constant form
works for all languages. This form is parsed at procedure definition
time, the string constant form is parsed at execution time...."
Why does the 2nd query fail? What am I missing?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: procedure string constant is parsed at procedure create time.
2023-11-07 11:52 procedure string constant is parsed at procedure create time. jian he <jian.universality@gmail.com>
@ 2023-11-07 12:39 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-11-07 15:08 ` Re: procedure string constant is parsed at procedure create time. Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Alvaro Herrera @ 2023-11-07 12:39 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: procedure string constant is parsed at procedure create time.
2023-11-07 11:52 procedure string constant is parsed at procedure create time. jian he <jian.universality@gmail.com>
2023-11-07 12:39 ` Re: procedure string constant is parsed at procedure create time. Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-11-07 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-11-07 15:08 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: jian he <jian.universality@gmail.com>; pgsql-general list <pgsql-general@lists.postgresql.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: procedure string constant is parsed at procedure create time.
2023-11-07 11:52 procedure string constant is parsed at procedure create time. jian he <jian.universality@gmail.com>
@ 2023-11-07 12:42 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2023-11-07 12:42 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
On Tuesday, November 7, 2023, jian he <jian.universality@gmail.com> wrote:
>
>
> Why does the 2nd query fail? What am I missing?
>
>
The language specific implementation detail note on this page.
https://www.postgresql.org/docs/current/xfunc-sql.html
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-11-07 15:08 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-07 11:52 procedure string constant is parsed at procedure create time. jian he <jian.universality@gmail.com>
2023-11-07 12:39 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-11-07 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-11-07 12:42 ` David G. Johnston <david.g.johnston@gmail.com>
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