pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated
2+ messages / 2 participants
[nested] [flat]
* BUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated
@ 2019-07-16 17:49 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2019-07-16 17:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: zeroimpl@gmail.com
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated
@ 2019-08-15 19:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2019-08-15 19:23 UTC (permalink / raw)
To: zeroimpl@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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=6070ccdd179f34efecc92d6679a141093...
Thanks for the report!
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-08-15 19:23 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-16 17:49 BUG #15913: Could not open relation with oid on PL/pgSQL method referencing temporary table that got recreated PG Bug reporting form <noreply@postgresql.org>
2019-08-15 19:23 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox