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

Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)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