From: Tom Lane <tgl@sss.pgh.pa.us>
To: cstork+postgresql@gmail.com
cc: pgsql-bugs@lists.postgresql.org,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly
 memoized(?)
In-reply-to: <18131-576854e79c5cd264@postgresql.org>
References: <18131-576854e79c5cd264@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 22 Sep 2023 19:22:34 -0000"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1332509.1695487092.0@sss.pgh.pa.us>
Date: Sat, 23 Sep 2023 12:39:04 -0400
Message-ID: <1332569.1695487144@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1332569.1695487144%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1332509.1695487092.1@sss.pgh.pa.us>

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


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="v1-track-dependencies-of-CallStmt.patch";
	charset="us-ascii"
Content-ID: <1332509.1695487092.2@sss.pgh.pa.us>
Content-Description: v1-track-dependencies-of-CallStmt.patch
Content-Transfer-Encoding: quoted-printable

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, Plann=
erInfo *context)
 =

 		if (query->commandType =3D=3D CMD_UTILITY)
 		{
+			/* CALL requires bespoke processing */
+			if (IsA(query->utilityStmt, CallStmt))
+			{
+				CallStmt   *callstmt =3D (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 =3D UtilityContainsQuery(query->utilityStmt);
 			if (query =3D=3D NULL)

------- =_aaaaaaaaaa0--