Thread: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?)

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
-- ...

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

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)