Обсуждение: BUG #1295: Problem on trigger

The following bug has been logged online:

Bug reference:      1295
Logged by:          shancheng

Email address:      sd_shancheng@hotmail.com

PostgreSQL version: 8.0 Beta

Operating system:   Fedora2

Description:        Problem on trigger

Details:

I have a table for test.
CREATE TABLE _test(id int);
Then i insert some records(at least 3 records for obvious result) into the
table:
INSERT INTO _test VALUES(1);
INSERT INTO _test VALUES(2);
INSERT INTO _test VALUES(3);

And below is my testing function and the point where the problem comes from:
CREATE OR REPLACE FUNCTION _test_delete_and_drop()
RETURNS void AS $$
BEGIN
    DELETE FROM _test;
    DROP TABLE _test;
    RETURN;
END;
$$ LANGUAGE plpgsql;

When i run the command:
   SELECT _test_delete_and_drop();
The computer responses:
   ERROR:  relation with OID 1354613 does not exist
   CONTEXT:  SQL query "DELETE FROM _test"
   PL/pgSQL function "_test_delete_and_drop" line 2 at SQL statement

The function is very easy. It just delete the contents of a table and then
drop it.
The reason that i don't drop the table directly is that i need some cleanup
operations. I define several triggers on the table. And when i delete
records, the trigger will be activeted. If i don't drop the records
beforehand, there will be much useless information left in the database. But
i met a very puzzling problem when i do that. So i write the above test case
to find out what the problem is. And to simplify the question, i don't
define triggers for the table.
Could anyone help me?

Thanks very much!

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> CREATE OR REPLACE FUNCTION _test_delete_and_drop()
> RETURNS void AS $$
> BEGIN
>     DELETE FROM _test;
>     DROP TABLE _test;
>     RETURN;
> END;
> $$ LANGUAGE plpgsql;

This will work fine the first time.  When you recreate the "_test" table
and try to use the function again, the DELETE will fail because it's
cached a plan referring to the prior incarnation of the table.  plpgsql
has always worked like that; it's not a new issue.

You can work around this by executing the DELETE with EXECUTE, viz
    EXECUTE 'DELETE FROM _test';
so that it gets re-planned each time.

There are plans to improve this situation, but it won't happen in the
near future (certainly not for 8.0).

            regards, tom lane