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

BUG #2124: Error "relation with OID ... does not exist" when using temporary table in function.

From: "" <andrew_kazachkov(at)mail(dot)ru>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2124: Error "relation with OID ... does not exist" when using temporary table in function.
Date: 2005-12-23 12:53:00
Message-ID: 20051223125300.B2790F0AC7@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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();

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

Responses

pgsql-bugs by date

Next:From: bfraciDate: 2005-12-23 16:48:03
Subject: Re: BUG #2102: Backend reports wrong number of affected rows for a
Previous:From: Konstantin S. Zhinko [tIT]Date: 2005-12-23 08:56:48
Subject: BUG #2123: join between stored procedures

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