Thread: create/drop table bug

create/drop table bug

From
"Libsis"
Date:
hello people, my name is Poyo, I'm from a little town near from Buenos Aire=
s -Argentina-  and
i'm having problems with the creation/drop of tables from inside of functio=
n's.=20
in other words: if you execute the below points in order (1,2,3,4,5) you 'l=
l see this:

ERROR:  relation with OID 17687 does not exist
CONTEXT:  PL/pgSQL function "myfunc" line 5 at for over select rows

the oid 17687 is corresponding with the table created inside the function (=
SELECT INTO newtable) (see point 3) but later, in the same function, this t=
able is dropped.
when the function (point 5) is executed again, this search for the oid's ta=
ble but never found it.

note: after the error, if you REPLACE the function and execute it again, th=
ere are no problem... and of corse, if you exectute it again without drop t=
he function, the error message will appear

i've tried this with posgresql 8.0 beta3 and beta4 running on linux (debian=
 6.2.6 and debian 6.2.9) and postgresql 8.0 beta 5 and RC1 running on windo=
ws 2000 Professional with the same results.

pd: i tried a lot of way's to do this whitout other results: temporary tabl=
es and not, creating tables (with CREATE TABLE) with and with out "ON COMMI=
T DROP" explicit.

if you wan't more information about this, don't doubt and mail me.

thank you.

1)

CREATE TABLE test
(a int4, b int4);

2)

insert into test values (1,1);
insert into test values (2,2);

3)

CREATE OR REPLACE FUNCTION myfunc()
  RETURNS SETOF test AS
$BODY$
DECLARE result test;
BEGIN
 SELECT * INTO newtable FROM test;

 FOR result in
  SELECT * FROM newtable
  loop
  RETURN NEXT result;
 END loop;

 DROP TABLE newtable;

 RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

4)
select * from myfunc();

5)
select * from myfunc();=

Re: create/drop table bug

From
Michael Fuhr
Date:
On Fri, Dec 10, 2004 at 05:30:14PM -0300, Libsis wrote:

> ERROR:  relation with OID 17687 does not exist
> CONTEXT:  PL/pgSQL function "myfunc" line 5 at for over select rows
>
> the oid 17687 is corresponding with the table created inside the
> function (SELECT INTO newtable) (see point 3) but later, in the same
> function, this table is dropped.  when the function (point 5) is
> executed again, this search for the oid's table but never found it.

Cached plans are causing the problem.  See the PL/pgSQL chapter in
the documentation -- the Overview section describes what's happening
and how to work around it.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/