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(at)pow(dot)lt
PostgreSQL version: 9.1.3
Operating system: Ubuntu 11.11
Description:

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 = 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 = 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(at)pow(dot)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

On Mon, May 7, 2012 at 6:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> ignas(at)pow(dot)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

I see, thank you very much for the explanation.

Ignas