Re: BUG #3450: Multiple Stored procedure calls cause issue with temp tables...

The following bug has been logged online:

Bug reference: 3450
Logged by: Chris Bowlby
Email address: excalibur(at)accesswave(dot)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

Chris Bowlby wrote:
> 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:

This is an unfortunate side-effect of caching access plans in PL/PgSQL
functions, see FAQ:

http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

The upcoming 8.3 release will include plan invalidation, which will fix
this issue.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Tue, 2007-17-07 at 00:51 +0000, Chris Bowlby wrote:
> Using a temporary table of the same name in repeated calls to a stored
> procedure are causing OID failure issues

This is a (well) known bug. The problem arises because plpgsql caches
the query plan used to access the temporary table, which includes the
temp table's OID; when a new temp is created, it is assigned a new OID,
but the cached plan is not discarded.

The bug will be fixed in 8.3 (cached plans are now invalidated when
dependent DB objects change). In the mean time, you can workaround the
problem by always accessing the temporary table with EXECUTE -- see the
list archives for prior discussion.

-Neil

hello,

it's not bug. Please look on
http://www.pgsql.cz/index.php/Automatic_execution_plan_caching_in_PL/pgSQL

Regards
Pavel Stehule

2007/7/17, Chris Bowlby <excalibur(at)accesswave(dot)ca>:
>
> The following bug has been logged online:
>
> Bug reference: 3450
> Logged by: Chris Bowlby
> Email address: excalibur(at)accesswave(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>