By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,732 Members | 1,429 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,732 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Frank van Vugt <ft**********@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2

P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

P: n/a
> 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;
************************************************** *************************************
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #5

P: n/a
Frank van Vugt <ft**********@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #6

P: n/a
> > 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.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #7

P: n/a
Frank van Vugt <ft**********@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #8

P: n/a
> > 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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.