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

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
>