Thread: BUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated

The following bug has been logged on the website:

Bug reference:      15913
Logged by:          Daniel Fiori
Email address:      zeroimpl@gmail.com
PostgreSQL version: 11.4
Operating system:   Debian 11.4-1.pgdg90+1
Description:

I have some SQL which works fine in 9.x and 10.x, but fails in 11.x and 12.x
(I tested on various Docker images).

One of the functions declares a variable whose type matches a temporary
table. If I drop and recreate that temporary table twice in the same
session, I get an error like: "ERROR:  could not open relation with OID
xxx". This occurs on the second call to the DoSomething() function after the
temporary table has been recreated. 

---

BEGIN;

CREATE OR REPLACE FUNCTION BeginTest( arg TEXT ) RETURNS VOID AS $$
BEGIN
    CREATE TEMPORARY TABLE TestVal AS SELECT arg;
END
$$ LANGUAGE PLPGSQL;

SELECT BeginTest( NULL );

CREATE OR REPLACE FUNCTION EndTest() RETURNS VOID AS $$
BEGIN
    DROP TABLE TestVal;
END
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION DoSomething() RETURNS VOID AS $$
DECLARE
    varname TestVal;
BEGIN
    SELECT * INTO varname FROM TestVal;
END
$$ LANGUAGE PLPGSQL;

SELECT EndTest();

COMMIT;

---

Then in a different session run:
---

BEGIN;

SELECT BeginTest( 'abc' );
SELECT DoSomething();
SELECT EndTest();

SELECT BeginTest( 'def' );
SELECT DoSomething();
SELECT EndTest();

COMMIT;

---

Note if the above SQL is all run in the same session, a slightly different
error is reported: "ERROR:  type with OID xxx does not exist"

Based on the PG 11 release notes, it sounds like it's related to this
change:

> Allow PL/pgSQL to handle changes to composite types (e.g. record, row)
that happen between the first and later function executions in the same
session (Tom Lane). Previously, such circumstances generated errors.

PG Bug reporting form <noreply@postgresql.org> writes:
> I have some SQL which works fine in 9.x and 10.x, but fails in 11.x and 12.x
> (I tested on various Docker images).
> One of the functions declares a variable whose type matches a temporary
> table. If I drop and recreate that temporary table twice in the same
> session, I get an error like: "ERROR:  could not open relation with OID
> xxx". This occurs on the second call to the DoSomething() function after the
> temporary table has been recreated.

I've pushed a fix for this -

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=6070ccdd179f34efecc92d6679a141093df0f879

Thanks for the report!

            regards, tom lane