I came across this bug tonight:
-- Function: pre_delete_main()
CREATE FUNCTION pre_delete_main() RETURNS TRIGGER AS
$BODY$BEGIN
DROP TABLE bug_referring_table;
RETURN OLD;
END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;
-- Table: bug_referenced_table
CREATE TABLE bug_referenced_table
( id integer NOT NULL, CONSTRAINT bug_referenced_table_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
-- Trigger: on_pre_delete_main on bug_referenced_table
CREATE TRIGGER on_pre_delete_main BEFORE DELETE ON bug_referenced_table FOR EACH ROW EXECUTE PROCEDURE
pre_delete_main();
INSERT INTO bug_referenced_table VALUES (1);
-- Table: bug_referring_table
CREATE TABLE bug_referring_table
( main_id integer NOT NULL, CONSTRAINT bug_referring_table_main_id_fkey FOREIGN KEY (main_id) REFERENCES
bug_referenced_table(id) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (OIDS=FALSE);
-- ERROR: cache lookup failed for constraint 19411
DELETE FROM bug_referenced_table WHERE id = 1;
It seems the trigger list is calculated once, and does not reflect
changes made by triggers in that list. Are there good reasons for
doing that? Should that behavior be changed?
The reason I wanted to do this was partitioning. By dropping the
partitions in a trigger, I can make deleting look the same to the
programmer with or without partitions. Because of this bug, the
programmer will first need to determine if the delete will cascade
normally (it does depending on which partition the record falls into)
or if he needs to drop partition tables first.
-Dan
