Thread: BUG #2124: Error "relation with OID ... does not exist" when using temporary table in function.
BUG #2124: Error "relation with OID ... does not exist" when using temporary table in function.
From
""
Date:
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(); --------------------------------------------------------
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