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

create/drop table bug

From: "Libsis" <libsis(at)arnet(dot)com(dot)ar>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: create/drop table bug
Date: 2004-12-10 20:30:14
Message-ID: 000801c4def7$12a04c00$0401a8c0@etarra (view raw or flat)
Thread:
Lists: pgsql-bugs
hello people, my name is Poyo, I'm from a little town near from Buenos Aires -Argentina-  and
i'm having problems with the creation/drop of tables from inside of function's. 
in other words: if you execute the below points in order (1,2,3,4,5) you 'll 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 table is dropped.
when the function (point 5) is executed again, this search for the oid's table but never found it.

note: after the error, if you REPLACE the function and execute it again, there are no problem... and of corse, if you exectute it again without drop the 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 windows 2000 Professional with the same results.

pd: i tried a lot of way's to do this whitout other results: temporary tables and not, creating tables (with CREATE TABLE) with and with out "ON COMMIT 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();

Responses

pgsql-bugs by date

Next:From: PostgreSQL Bugs ListDate: 2004-12-10 21:41:10
Subject: BUG #1345: pgsql character comparison bug
Previous:From: Steve McWilliamsDate: 2004-12-10 15:00:08
Subject: pg_autovacuum fails to run as a windows service (beta4)

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