Обсуждение: BUG #6632: "before delete" triggers that delete rows from the same table can invalidate constraints
The following bug has been logged on the website: Bug reference: 6632 Logged by: Ignas Mikalajunas Email address: ignas@pow.lt PostgreSQL version: 9.1.3 Operating system: Ubuntu 11.11 Description:=20=20=20=20=20=20=20=20 The snippet that reproduces the bug: drop schema public cascade; create schema public; -- Setup BEGIN; CREATE TABLE apps ( id bigserial NOT NULL, "type" varchar(32), primary key (id) ); CREATE TABLE content_items ( id bigserial NOT NULL, "type" varchar(32), app_id integer, wall_post_id integer, foreign key (app_id) references apps on delete cascade, foreign key (wall_post_id) references content_items on delete set null, primary key (id) ); CREATE OR REPLACE FUNCTION content_item_deleted_trigger() RETURNS trigger AS $$ BEGIN DELETE FROM content_items WHERE content_items.id =3D OLD.wall_post_id; RETURN OLD; END $$ LANGUAGE plpgsql; -- the problem is in this trigger, if I make it an "AFTER" it works as it should CREATE TRIGGER content_item_deleted_trigger BEFORE DELETE ON content_items FOR EACH ROW WHEN (OLD.wall_post_id is not null) EXECUTE PROCEDURE content_item_deleted_trigger(); COMMIT; -- End of schema setup BEGIN; INSERT INTO apps (type) VALUES ('basecamp'); INSERT INTO content_items (type, app_id, wall_post_id) VALUES ('wall_post', NULL, NULL); INSERT INTO content_items (type, app_id, wall_post_id) VALUES ('basecamp_post', 1, 1); COMMIT; -- End of setup BEGIN; DELETE FROM apps WHERE apps.id =3D 1; COMMIT; -- This select still sees 1 item that refers to an app that does not exist anymore BEGIN; SELECT count(*) from content_items; SELECT count(*) from apps; ROLLBACK; I think being able to generate rows that do not pass constraints on a table is a bug.
ignas@pow.lt writes: > [ you can sabotage foreign key constraints with triggers ] This is not a bug, it's a "don't do that" issue. The only way to prevent it would be to not fire triggers during FK operations, or to somehow restrict what triggers are allowed to do, and either of those cures would be worse than the disease. In general, it's bad design to use a BEFORE trigger to propagate changes to other rows; you should do that in AFTER triggers. See the documentation. regards, tom lane
Re: BUG #6632: "before delete" triggers that delete rows from the same table can invalidate constraints
От
Ignas Mikalajunas
Дата:
On Mon, May 7, 2012 at 6:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > ignas@pow.lt writes: >> [ you can sabotage foreign key constraints with triggers ] > > This is not a bug, it's a "don't do that" issue. =A0The only way to > prevent it would be to not fire triggers during FK operations, or > to somehow restrict what triggers are allowed to do, and either of > those cures would be worse than the disease. > > In general, it's bad design to use a BEFORE trigger to propagate changes > to other rows; you should do that in AFTER triggers. =A0See the > documentation. > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane I see, thank you very much for the explanation. Ignas