Re: [BUGS] BUG #2124: Error "relation with OID ... does not exist" when

#1

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 (AT) mail (DOT) ru wrote:

Quote:

The following bug has been logged online:

Bug reference: 2124
Logged by:
Email address: andrew_kazachkov (AT) mail (DOT) 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 (AT) candle (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #2124: Error "relation with OID ... does not exist" when

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #2124: Error "relation with OID ... does not exist" when - 12-27-2005 , 09:49 AM