Thread: Does a 'stable' deferred trigger execution order exist? Is housekeeping for deferred trigger fire events done in one of the system catalogues?

Hi,


If during a transaction a number of deferred triggers are fired, what will be
their execution order upon the commit?

Will they be executed in order of firing or alfabetically or something
entirely different? The docs only mention regular triggers being executed
alfabetically.

And equally important in case it is in firing order, is this order regarded as
'stable' between releases?


Also, are the fire-events of deferred triggers kept in a system catalogue
somewhere, .i.e. would it be possible upon a first time execution of a
deferred trigger to avoid any additional executions of that same trigger that
got scheduled earlier in the transaction due to multiple fire events ?




--
Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> If during a transaction a number of deferred triggers are fired, what will be
> their execution order upon the commit?

Should be alphabetical within each triggering event, IIRC.

> Also, are the fire-events of deferred triggers kept in a system catalogue
> somewhere,

No.  They're in a list in memory.

            regards, tom lane

> > If during a transaction a number of deferred triggers are fired, what
> > will be their execution order upon the commit?

> Should be alphabetical within each triggering event, IIRC.

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;


Now, will I see an execution order of 'Z-A-B' (on alfabet per event per
statement) or 'A-B-Z' (on alfabet per event per transaction)??

For what I want to accomplish, I don't care about the order of A/B, but
together they depend on the work that is done by the earlier triggered Z.



--
Best,




Frank.

Frank van Vugt wrote:

>>>If during a transaction a number of deferred triggers are fired, what
>>>will be their execution order upon the commit?
>
>
>>Should be alphabetical within each triggering event, IIRC.
>
>
> 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;
>
>
> Now, will I see an execution order of 'Z-A-B' (on alfabet per event per
> statement) or 'A-B-Z' (on alfabet per event per transaction)??
>
> For what I want to accomplish, I don't care about the order of A/B, but
> together they depend on the work that is done by the earlier triggered Z.

The best way is to "raise notice" inside the trigger function and observe
the results


Regards
Gaetano Mendola

> 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;
***************************************************************************************

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> 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.

"Order in which they were defined"?  Hmm, I thought we had agreed long
since to trigger these things in alphabetical order.  Something is wrong
here.

            regards, tom lane

> > deferred triggers are always executed in the order they fired
> > for one and the same event, they will execute in order of definition

> "Order in which they were defined"?  Hmm, I thought we had agreed long
> since to trigger these things in alphabetical order.  Something is wrong
> here.

All 'plain' triggers on one and the same event are indeed triggered in
alphabetical order. It's the deferred triggers that aren't, not for the set
of triggers on one event and not for the complete collection of triggers for
that transaction.


Allow me to repeat and possibly clarify an earlier (personal) point of
interest:

Any execution order for regular triggers would be as good as any other, given
the fact that it is a single atomic event that triggered them, a design
should not try to make use of a specific order. The same goes for deferred
triggers that fired on the same event, the particular order of trigger
execution within this set should not be of any interest.

However, the accumulated collection of deferred triggers at the end of a
transaction possibly exists of many sets, each of which was fired on a
different event, on a different point in time. Therefore, IMHO, it is
possible for a dependancy between certain sets to be valid and handled
properly by the fact that at least the sets itself are executed in the same
order as the original events they fired upon.

So, the one thing that would make me unhappy here is when the complete
collection of triggers would simply be executed in alphabetical order. And
since I'd obviously like to make use of the earlier described dependancies
between trigger-sets, I'm a bit cautious regarding a possible 'silent change
in behaviour' between versions.




--
Best,




Frank.

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
>> "Order in which they were defined"?  Hmm, I thought we had agreed long
>> since to trigger these things in alphabetical order.  Something is wrong
>> here.

> Allow me to repeat and possibly clarify an earlier (personal) point of
> interest:

> Any execution order for regular triggers would be as good as any other, given
> the fact that it is a single atomic event that triggered them, a design
> should not try to make use of a specific order. The same goes for deferred
> triggers that fired on the same event, the particular order of trigger
> execution within this set should not be of any interest.

This is perhaps true for "cleanly designed" applications, but people
have requested that we nail down the execution order, and we have
responded by specifying that it's alphabetical within an event.
(Not order of creation --- alphabetical lets you insert later-created
triggers where you need to in the firing order, order-of-creation
does not.)  The intention was certainly to apply this to AFTER as well
as BEFORE triggers.  We'll need to look and see why it's not working.

> However, the accumulated collection of deferred triggers at the end of a
> transaction possibly exists of many sets, each of which was fired on a
> different event, on a different point in time. Therefore, IMHO, it is
> possible for a dependancy between certain sets to be valid and handled
> properly by the fact that at least the sets itself are executed in the same
> order as the original events they fired upon.

Right.  This is handled by appending new pending-trigger events to a
global list when they are detected.  Barring oddities such as different
deferral specifications, they should be executed in order of detection.
I would have expected triggers for the same event to be inserted in
alphabetical order ...

            regards, tom lane

> > Any execution order for regular triggers would be as good as any other
> This is perhaps true for "cleanly designed" applications, but people
> have requested that we nail down the execution order, and we have
> responded by specifying that it's alphabetical within an event.

I understand and actually meant to say that since nothing should 'depend' on
any particular order, executing these triggers alphabetically seems a logical
approach with the extra bonus you mention.

> The intention was certainly to apply this to AFTER as well
> as BEFORE triggers.  We'll need to look and see why it's not working.

Just to avoid any misunderstandings:

- regular triggers DO execute in alphabetical order

- it's the deferred triggers that execute per event in order of definition

> > at least the sets itself are executed in the same order as the original
> > events they fired upon.
> Right.  This is handled by appending new pending-trigger events to a
> global list when they are detected.  Barring oddities such as different
> deferral specifications, they should be executed in order of detection.

That's the big 'YES' I was looking for ;-) Thanks.

> I would have expected triggers for the same event to be inserted in
> alphabetical order ...

Yep, me too, but apart from the fact that I'm o.k. with the way it currently
works, I imagine this is not exactly a high-priority issue right now ;-)


Thanks again for the replies.



--
Best,




Frank.