pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?)
3+ messages / 3 participants
[nested] [flat]
* BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?)
@ 2023-09-22 19:22 PG Bug reporting form <noreply@postgresql.org>
2023-09-22 21:32 ` Re: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?) Christian Stork <cstork+postgresql@gmail.com>
2023-09-23 16:39 ` Re: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2023-09-22 19:22 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: cstork+postgresql@gmail.com
The following bug has been logged on the website:
Bug reference: 18131
Logged by: Christian Stork
Email address: cstork+postgresql@gmail.com
PostgreSQL version: 14.9
Operating system: Ubuntu 22.04
Description:
The following condensed recipe reproduces the error:
create procedure callee(t regclass)
language plpgsql as
$body$
begin
raise notice 'callee: oid = %', t::oid;
execute 'table ' || t;
end;
$body$;
create procedure caller()
language plpgsql as
$body$
begin
create table table_name ();
raise notice 'caller: oid = %', 'table_name'::regclass::oid;
call callee('table_name');
drop table table_name;
end;
$body$;
call caller(); -- OK
call caller(); -- ERROR: callee executed with OID of previous invocation!
-- Relevant output (Ubuntu 14.9-1.pgdg22.04+1):
-- ...
-- NOTICE: caller: oid = 24769724
-- NOTICE: callee: oid = 24769724
-- CALL <---- first statement completed
successfully
-- NOTICE: caller: oid = 24769727
-- NOTICE: callee: oid = 24769724 <---- should be the same as line
above
-- ERROR: syntax error at or near "24769724"
-- LINE 1: table 24769724 <---- no more table with this OID in
pg_catalog, hence OID instead of table name, I assume
-- ...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?)
2023-09-22 19:22 BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?) PG Bug reporting form <noreply@postgresql.org>
@ 2023-09-22 21:32 ` Christian Stork <cstork+postgresql@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Christian Stork @ 2023-09-22 21:32 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
I forgot to mention that the problem disappears when
call callee('table_name');
is replaced by
table_oid = 'table_name'::regclass;
call callee(table_oid);
-Chris
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?)
2023-09-22 19:22 BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?) PG Bug reporting form <noreply@postgresql.org>
@ 2023-09-23 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2023-09-23 16:39 UTC (permalink / raw)
To: cstork+postgresql@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
PG Bug reporting form <noreply@postgresql.org> writes:
> The following condensed recipe reproduces the error:
> create procedure callee(t regclass)
> language plpgsql as
> $body$
> begin
> raise notice 'callee: oid = %', t::oid;
> execute 'table ' || t;
> end;
> $body$;
> create procedure caller()
> language plpgsql as
> $body$
> begin
> create table table_name ();
> raise notice 'caller: oid = %', 'table_name'::regclass::oid;
> call callee('table_name');
> drop table table_name;
> end;
> $body$;
> call caller(); -- OK
> call caller(); -- ERROR: callee executed with OID of previous invocation!
Hmm. It's not just regclass: we're failing to track *any* dependencies
of the compiled CallStmt. So you can also break it by, say, dropping
and recreating the "callee" procedure.
The attached quick hack fixes the reported symptoms, but I wonder
if there is a better place to do it.
regards, tom lane
Attachments:
[text/x-diff] v1-track-dependencies-of-CallStmt.patch (1.1K, ../../1332569.1695487144@sss.pgh.pa.us/2-v1-track-dependencies-of-CallStmt.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 5700bfb5cd..9b54ed5c70 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -3564,9 +3564,22 @@ extract_query_dependencies_walker(Node *node, PlannerInfo *context)
if (query->commandType == CMD_UTILITY)
{
+ /* CALL requires bespoke processing */
+ if (IsA(query->utilityStmt, CallStmt))
+ {
+ CallStmt *callstmt = (CallStmt *) query->utilityStmt;
+
+ /* We need not examine funccall, just the transformed exprs */
+ (void) extract_query_dependencies_walker((Node *) callstmt->funcexpr,
+ context);
+ (void) extract_query_dependencies_walker((Node *) callstmt->outargs,
+ context);
+ return false;
+ }
+
/*
- * Ignore utility statements, except those (such as EXPLAIN) that
- * contain a parsed-but-not-planned query.
+ * Ignore other utility statements, except those (such as EXPLAIN)
+ * that contain a parsed-but-not-planned query.
*/
query = UtilityContainsQuery(query->utilityStmt);
if (query == NULL)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-09-23 16:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-22 19:22 BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?) PG Bug reporting form <noreply@postgresql.org>
2023-09-22 21:32 ` Christian Stork <cstork+postgresql@gmail.com>
2023-09-23 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
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