Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly

From: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly
Date: 2008-10-13 18:15:49
Message-ID: 7C39BB4E-3E07-4CE6-83E7-77E8D6B4F90A@med.unc.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I've got a series of triggers that are responsible for maintaining
deduced data in a table (background for this can be found in an
earlier mailing list posting: http://archives.postgresql.org/pgsql-sql/2008-10/msg00041.php)
.

This problem concerns the order in which my triggers are executed.
After each row in the table is deleted, I have a trigger set up to
then delete any rows that can be logically deduced from the row that
was just deleted. I expect that when deleting a row, my before row
triggers would fire in alphabetical order, the row would be deleted,
and then my after row triggers would fire in alphabetical order. If I
delete rows one at a time (e.g. "DELETE FROM foo WHERE ID = 1"), this
is what happens. However, when I do a bulk delete (e.g., "DELETE FROM
foo"), this is not what happens. Based on debugging messages I added
to the triggers, it looks like the before row triggers fire for ALL
the rows first, THEN all the after row triggers fire. Here's some
sample code that illustrates this:

CREATE TABLE foo (
id serial primary key,
message text
);

insert into foo(message) values
('Hello'),
('Hi'),
('Howdy')
;

CREATE OR REPLACE FUNCTION debug_insert_or_update_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Trigger % on table % is firing % % for record %',
TG_NAME, TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, TG_WHEN, TG_OP,
NEW.id;
RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION debug_delete_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Trigger % on table % is firing % % for record %',
TG_NAME, TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME, TG_WHEN, TG_OP,
OLD.id;
RETURN OLD;
END;
$$;

CREATE TRIGGER a_debug_before_insert_or_update
BEFORE UPDATE OR INSERT
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_insert_or_update_trigger();
;

CREATE TRIGGER a_debug_after_insert_or_update
AFTER UPDATE OR INSERT
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_insert_or_update_trigger();
;

CREATE TRIGGER a_debug_before_delete
BEFORE DELETE
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_delete_trigger();
;

CREATE TRIGGER a_debug_after_delete
AFTER DELETE
ON foo
FOR EACH ROW
EXECUTE PROCEDURE debug_delete_trigger();
;

SELECT * FROM FOO;
id | message
----+---------
1 | Hello
2 | Hi
3 | Howdy
(3 rows)

SAVEPOINT S1;

DELETE FROM FOO WHERE id = 1;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 1
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 1
DELETE 1

DELETE FROM FOO WHERE id = 2;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 2
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 2
DELETE 1

DELETE FROM FOO WHERE id = 3;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 3
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 3
DELETE 1

ROLLBACK TO SAVEPOINT S1;

SELECT * FROM FOO;
id | message
----+---------
1 | Hello
2 | Hi
3 | Howdy

DELETE FROM FOO;
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 1
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 2
NOTICE: Trigger a_debug_before_delete on table public.foo is firing
BEFORE DELETE for record 3
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 1
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 2
NOTICE: Trigger a_debug_after_delete on table public.foo is firing
AFTER DELETE for record 3
DELETE 3

As you can see, doing a row-by-row delete works fine, but when doing a
bulk delete, all the before triggers are grouped together, as are all
the after triggers.

Why are these calls not interleaved (e.g. BEFORE DELETE for record 1,
AFTER DELETE for record 1, BEFORE DELETE for record 2, AFTER DELETE
for record 2, etc.). Is there any way I can get them to be
interleaved? This causes problems for my application because if
dependent rows are not deleted immediately after the row that supports
it, then I can get "orphaned" rows left in the table that have no
supporting rows.

Thanks,
Chris

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Christopher Maier 2008-10-13 18:21:15 Re: Problem with delete trigger: how to allow only triggers to delete a row?
Previous Message Tom Lane 2008-10-11 17:32:01 Re: EXISTS