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

Search Discussions

  • Tom Lane at Aug 17, 2008 at 1:35 am

    "Dan Eloff" <dan.eloff@gmail.com> writes:
    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?
    Yes. No. Don't hold your breath on this being considered a bug.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedAug 16, '08 at 11:35p
activeAug 17, '08 at 1:35a
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Tom Lane: 1 post Dan Eloff: 1 post

People

Translate

site design / logo © 2022 Grokbase