Horton

Thread

Commits

  1. Collect dependency information for parsed CallStmts.

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

    The Post Office <noreply@postgresql.org> — 2023-09-22T19:22:34Z

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

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

    Christian Stork <cstork+postgresql@gmail.com> — 2023-09-22T21:32:15Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-09-23T16:39:04Z

    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