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

Started by PG Bug reporting formover 2 years ago3 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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

#2Christian Stork
cstork+postgresql@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?)

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: PG Bug reporting form (#1)
Re: BUG #18131: PL/pgSQL: regclass procedure parameter wrongly memoized(?)

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:

v1-track-dependencies-of-CallStmt.patchtext/x-diff; charset=us-ascii; name=v1-track-dependencies-of-CallStmt.patchDownload+15-2