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

Mon, 13 Oct 2008 11:16:28 -0700

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







--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql






















					Reply via email to