Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly - Mailing list pgsql-sql
From | Christopher Maier |
---|---|
Subject | Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly |
Date | |
Msg-id | 7C39BB4E-3E07-4CE6-83E7-77E8D6B4F90A@med.unc.edu Whole thread Raw |
Responses |
Re: Order of trigger execution: AFTER DELETE ON EACH ROW doesn't appear to be working properly
|
List | 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 PROCEDUREdebug_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