The following bug has been logged online:
Bug reference: 3450
Logged by: Chris Bowlby
Email address: excalibur@accesswave.ca
PostgreSQL version: 8.0.11
Operating system: SUSE Linux Enterprise Server 9 SP2
Description: Multiple Stored procedure calls cause issue with temp
tables...
Details:
Using a temporary table of the same name in repeated calls to a stored
procedure are causing OID failure issues, it can be re-created using the
following useless example:
----------------------------------------
CREATE TABLE test1
( id SERIAL,
name TEXT,
PRIMARY KEY(id));
INSERT INTO test1(name) VALUES('Marc');
INSERT INTO test1(name) VALUES('Chris');
INSERT INTO test1(name) VALUES('Bob');
INSERT INTO test1(name) VALUES('Robert');
INSERT INTO test1(name) VALUES('Sally');
INSERT INTO test1(name) VALUES('Paul');
CREATE OR REPLACE FUNCTION get_name_id(text) RETURNS INTEGER AS '
DECLARE
search_name ALIAS FOR $1;
return_id RECORD;
tbl_oid RECORD;
BEGIN
CREATE TEMPORARY TABLE t_name AS SELECT id, name FROM test1 WHERE name =
search_name;
SELECT INTO tbl_oid OID FROM pg_class WHERE relname = ''t_name'';
CREATE TEMPORARY TABLE t_id AS SELECT id FROM t_name;
SELECT INTO return_id id FROM t_id;
DROP TABLE t_id;
DROP TABLE t_name;
return return_id.id;
END;
' LANGUAGE plpgsql;
select get_name_id('Marc');
select get_name_id('Marc');
-----------------------------------------
The error is as follows:
test=# select get_name_id('Marc');
get_name_id
-------------
1
(1 row)
test=# select get_name_id('Marc');
ERROR: relation with OID 74623 does not exist
CONTEXT: SQL statement "CREATE TEMPORARY TABLE t_id AS SELECT id FROM
t_name"
PL/pgSQL function "get_name_id" line 10 at SQL statement
