Collected from the PG bugs email list.
|Reported by||Daniel Fiori|
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.
|2019-07-16 17:49:14+00||PG Bug reporting form||BUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated|
|2019-08-15 19:23:31+00||Tom Lane||Re: BUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated|