Lists: | pgsql-bugs |
---|
From: | "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #1295: Problem on trigger |
Date: | 2004-10-26 01:56:11 |
Message-ID: | 20041026015611.C1FE75A1016@www.postgresql.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 1295
Logged by: shancheng
Email address: sd_shancheng(at)hotmail(dot)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!
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "shancheng" <sd_shancheng(at)hotmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #1295: Problem on trigger |
Date: | 2004-10-26 02:06:27 |
Message-ID: | 3410.1098756387@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)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