Re: Does a 'stable' deferred trigger execution order exist? -> answer: yes

> Mmm, yes, but are all the deferred triggers on the same event 'grouped'?
> What I'm thinking about is something like:
> BEGIN;
> update foo1; => triggers deferred after insert trigger 'Z'
> select bar;
> update foo2; => triggers deferred after insert triggers 'B' and 'A'
> COMMIT;
>
> What will the resulting trigger execution order be?

In case someone is interested, a little bit of fiddling around with the script
below seems to indicate that for different events, deferred triggers are
always executed in the order they fired. For one and the same event, they
will execute in the order in which they were initially defined.

I'm happy with the outcome, but still would like to find out though whether
this execution order is regarded as 'stable', i.e. is it part of any spec, is
it likely to be changed between versions, etc.

Best,

Frank.

***************************************************************************************
drop table f cascade;
drop table f_update cascade;
drop function tr_f() cascade;
drop function tr_f_update_a_def() cascade;
drop function tr_f_update_b_def() cascade;
drop function tr_f_update_z_def() cascade;

create table f (id int);
create table f_update (id int);

create function tr_f() RETURNS trigger LANGUAGE 'plpgsql' STABLE STRICT
SECURITY INVOKER AS '
DECLARE
BEGIN
RAISE NOTICE ''tr_f() triggered'';
INSERT INTO f_update VALUES(1);

RETURN NULL;
END;';

CREATE FUNCTION tr_f_update_a_def() RETURNS trigger LANGUAGE 'plpgsql' STABLE
STRICT SECURITY INVOKER AS '
DECLARE
BEGIN
RAISE NOTICE ''tr_f_update_a_def() triggered'';

RETURN NULL;
END;';

CREATE FUNCTION tr_f_update_b_def() RETURNS trigger LANGUAGE 'plpgsql' STABLE
STRICT SECURITY INVOKER AS '
DECLARE
BEGIN
RAISE NOTICE ''tr_f_update_b_def() triggered'';

RETURN NULL;
END;';

CREATE FUNCTION tr_f_update_z_def() RETURNS trigger LANGUAGE 'plpgsql' STABLE
STRICT SECURITY INVOKER AS '
DECLARE
BEGIN
RAISE NOTICE ''tr_f_update_z_def() triggered'';

RETURN NULL;
END;';

CREATE TRIGGER f_iud AFTER INSERT OR UPDATE OR DELETE ON f FOR EACH ROW
EXECUTE PROCEDURE tr_f();
CREATE CONSTRAINT TRIGGER f_b_def AFTER INSERT ON f_update DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_f_update_b_def();
CREATE CONSTRAINT TRIGGER f_a_def AFTER INSERT ON f_update DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_f_update_a_def();
CREATE CONSTRAINT TRIGGER f_z_def AFTER INSERT ON f_update DEFERRABLE
INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE tr_f_update_z_def();

BEGIN;
INSERT INTO f_update VALUES(1);
INSERT INTO f VALUES(1);
COMMIT;
***************************************************************************************