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.