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

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

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: andrew_kazachkov(at)mail(dot)ru
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2124: Error "relation with OID ... does not exist" when
Date: 2005-12-27 15:48:54
Message-ID: 200512271548.jBRFmsQ18899@candle.pha.pa.us (view raw or flat)
Thread:
Lists: 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(at)mail(dot)ru wrote:
> 
> 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(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

pgsql-bugs by date

Next:From: Michael FuhrDate: 2005-12-27 17:28:06
Subject: Re: BUG #2131: SQL Query Bug ?
Previous:From: Tom LaneDate: 2005-12-27 15:29:55
Subject: Re: BUG #2125: SELECT problem with strings containing \

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