Skip site navigation (1) Skip section navigation (2)

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

From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Chris Bowlby" <excalibur(at)accesswave(dot)ca>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3450: Multiple Stored procedure calls cause issue with temp tables...
Date: 2007-07-17 09:33:08
Message-ID: 162867790707170233n2ce3e5f6m8f05d611d1f1c728@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>

In response to

pgsql-bugs by date

Next:From: YaYoDate: 2007-07-17 10:04:25
Subject: BUG #3455: order of rows should not significant
Previous:From: Hiroshi SaitoDate: 2007-07-17 07:45:03
Subject: Re: BUG #3453: Error on COPY TO/FROM 'non-ascii-path'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group