Re: BUG #2124: Error "relation with OID ... does not exist" when - Mailing list pgsql-bugs

From Bruce Momjian
Subject Re: BUG #2124: Error "relation with OID ... does not exist" when
Date
Msg-id 200512271548.jBRFmsQ18899@candle.pha.pa.us
Whole thread Raw
In response to BUG #2124: Error "relation with OID ... does not exist" when using temporary table in function.  ("" <andrew_kazachkov@mail.ru>)
List pgsql-bugs
See our FAQ about temporary tables:

        <H3 id="item4.19">4.19) Why do I get "relation with OID #####
    does not exist" errors when accessing temporary tables in PL/PgSQL
    functions?</H3>


---------------------------------------------------------------------------

andrew_kazachkov@mail.ru wrote:
>
> The following bug has been logged online:
>
> Bug reference:      2124
> Logged by:
> Email address:      andrew_kazachkov@mail.ru
> PostgreSQL version: 8.1.1-1
> Operating system:   Windows
> Description:        Error "relation with OID ... does not exist" when using
> temporary table in function.
> Details:
>
> After running function proc_3 (described below) more than once error
> "relation with OID ... does not exist" occures.
>
> First run of function proc_3() is OK but the second run always fails until
> we recreate function proc_2().
>
> Script to reproduce.
>
> --------------------------------------------------------
>
> --DROP FUNCTION proc_1();
>
> CREATE OR REPLACE FUNCTION proc_1() RETURNS int AS
> $BODY$
> DECLARE
>     __nCount int;
> BEGIN
>     SELECT INTO __nCount COUNT(*) FROM __tmp_xx;
>     return __nCount;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> --DROP FUNCTION proc_2();
>
> CREATE OR REPLACE FUNCTION proc_2() RETURNS int AS
> $BODY$
> DECLARE
>     __nCount int;
> BEGIN
>     CREATE TEMPORARY TABLE __tmp_xx(
>         nId int PRIMARY KEY,
>         wstrName varchar(256) NOT NULL
>     );
>     INSERT INTO __tmp_xx (nId, wstrName) VALUES (1, 'xx');
>     __nCount := proc_1();
>     DROP TABLE __tmp_xx;
>     return __nCount;
> END
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> --DROP FUNCTION proc_3();
>
> CREATE OR REPLACE FUNCTION proc_3() RETURNS int AS
> $BODY$
> DECLARE
>     __nCount int;
> BEGIN
>     __nCount = proc_2();
>     --DELETE FROM t_res;
>     --INSERT INTO t_res(nId, nValue) VALUES(1, __nCount);
>     return __nCount;
> END
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;
>
>
> SELECT * FROM proc_3();
>
> SELECT * FROM proc_3();
>
> --------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #2125: SELECT problem with strings containing \
Next
From: Michael Fuhr
Date:
Subject: Re: BUG #2131: SQL Query Bug ?