BUG #1546: Temp table isn't deleted at the end of a transaction / ON COMMIT DROP has no effect
The following bug has been logged online:
Bug reference: 1546
Logged by: Oliver Siegmar
Email address: o.siegmar@vitrado.de
PostgreSQL version: 7.4.7 / 8.0.1
Operating system: Linux x86
Description: Temp table isn't deleted at the end of a transaction /
ON COMMIT DROP has no effect
Details:
Hello,
I've probably found a temp table bug in PostgreSQL (tested with 7.4.7 and
8.0.1 on Linux x86).
Here's a demonstration of the bug:
CREATE FUNCTION testfunction()
RETURNS void
AS '
BEGIN
CREATE TEMP TABLE testtable (field int4) ON COMMIT DROP;
INSERT INTO testtable (field) VALUES (1);
-- DROP TABLE testtable;
RETURN;
END;
' LANGUAGE 'plpgsql';
database=# SELECT testfunction();
testfunction
--------------
(1 row)
database=# SELECT testfunction();
ERROR: relation with OID 29308882 does not exist
CONTEXT: SQL statement "INSERT INTO testtable (field) VALUES (1)"
PL/pgSQL function "testfunction" line 4 at SQL statement
No transaction has been started manually.
If I drop the temporary testtable manually within the PL/pgSQL function,
everything runs fine. Bug or feature? ;-)
Cheers,
Oliver
On Tue, 15 Mar 2005, Oliver Siegmar wrote:
Here's a demonstration of the bug:
CREATE FUNCTION testfunction()
RETURNS void
AS '
BEGIN
CREATE TEMP TABLE testtable (field int4) ON COMMIT DROP;INSERT INTO testtable (field) VALUES (1);
-- DROP TABLE testtable;
RETURN;
END;
' LANGUAGE 'plpgsql';database=# SELECT testfunction();
testfunction
--------------(1 row)
database=# SELECT testfunction();
ERROR: relation with OID 29308882 does not exist
CONTEXT: SQL statement "INSERT INTO testtable (field) VALUES (1)"
PL/pgSQL function "testfunction" line 4 at SQL statement
Given the error message, this seems to be the whole plpgsql caches query
plans but we don't invalidate those plans when there are schema changes.
In all currently released versions you pretty much need to use EXECUTE on
any queries where the table may go away, for example, any use of temp
tables.
On Friday 18 March 2005 14:29, Stephan Szabo wrote:
Given the error message, this seems to be the whole plpgsql caches query
plans but we don't invalidate those plans when there are schema changes.
I already tried to execute the 'CREATE TEMP TABLE' statement using EXECUTE to
avoid cache problems - same problem!
On Fri, 18 Mar 2005, Oliver Siegmar wrote:
On Friday 18 March 2005 14:29, Stephan Szabo wrote:
Given the error message, this seems to be the whole plpgsql caches query
plans but we don't invalidate those plans when there are schema changes.I already tried to execute the 'CREATE TEMP TABLE' statement using EXECUTE to
avoid cache problems - same problem!
You have to EXECUTE the insert as well.
Given the error message, this seems to be the whole plpgsql caches
query plans but we don't invalidate those plans when there are schema
changes. In all currently released versions you pretty much need to
use EXECUTE on any queries where the table may go away, for example,
any use of temp tables.
FWIW, this is documented behaviour (section 35.1 of the manual).
-roy
Import Notes
Resolved by subject fallback