PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15913
PG Version11.4
OSDebian 11.4-1.pgdg90+1
Opened2019-07-16 17:49:14+00
Reported byDaniel Fiori
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15913
Logged by:          Daniel Fiori
Email address:      (redacted)
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.

Messages

DateAuthorSubject
2019-07-16 17:49:14+00PG Bug reporting formBUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated
2019-08-15 19:23:31+00Tom LaneRe: BUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated