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@accesswave.ca>:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>