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  (Tom Lane <tgl@sss.pgh.pa.us>)
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








pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: EXISTS
Next
From: "Christopher Maier"
Date:
Subject: Re: Problem with delete trigger: how to allow only triggers to delete a row?