Thread: 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