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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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