What Is The Firing Order?
Greetings!
The document says: "Also, if more than one trigger is
defined for the same event on the same relation, the
order of trigger firing is unpredictable. This may be
changed in the future."
My another question is: When both UPDATE trigger (and
its trigger function) and UPDATE CASCADE constraint
are both defined, is the custom UPDATE trigger or the
UPDATE CASCADE constraint fired first?
I wish the constraint will be fired first. Otherwise,
I should not define the constraint (which saves a lot
of work) and code the complex UPDATE CASCADE actions
in the trigger to replace the powerful constraint
functionality during table creation, in order to see
data changed in the expected order. Am I correct?
Thanks!
CN
--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com
<cnliou@eurosport.com> writes:
The document says: "Also, if more than one trigger is
defined for the same event on the same relation, the
order of trigger firing is unpredictable.
Yup, and it means what it says.
My another question is: When both UPDATE trigger (and
its trigger function) and UPDATE CASCADE constraint
are both defined, is the custom UPDATE trigger or the
UPDATE CASCADE constraint fired first?
I wish the constraint will be fired first.
That seems fairly arbitrary; someone else might wish the opposite,
depending on the details of what they want to do.
If we were to modify the code to make the firing order predictable,
I'd want it to be user-controllable. A simple hack that comes to
mind is to fire triggers in alphabetical order by name --- then you
can easily arrange for custom triggers to fall either before or after
system-generated ones. But perhaps someone else has a better idea.
regards, tom lane
I wish the constraint will be fired first.
That seems fairly arbitrary; someone else might wish the opposite,
depending on the details of what they want to do.If we were to modify the code to make the firing order predictable,
I'd want it to be user-controllable. A simple hack that comes to
mind is to fire triggers in alphabetical order by name --- then you
can easily arrange for custom triggers to fall either before or after
system-generated ones. But perhaps someone else has a better idea.
This is not the first time someone has asked about firing order. We
either need a solution or I will add it to the TODO list.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Fri, 7 Sep 2001, Tom Lane wrote:
<cnliou@eurosport.com> writes:
The document says: "Also, if more than one trigger is
defined for the same event on the same relation, the
order of trigger firing is unpredictable.Yup, and it means what it says.
My another question is: When both UPDATE trigger (and
its trigger function) and UPDATE CASCADE constraint
are both defined, is the custom UPDATE trigger or the
UPDATE CASCADE constraint fired first?I wish the constraint will be fired first.
That seems fairly arbitrary; someone else might wish the opposite,
depending on the details of what they want to do.If we were to modify the code to make the firing order predictable,
I'd want it to be user-controllable. A simple hack that comes to
mind is to fire triggers in alphabetical order by name --- then you
can easily arrange for custom triggers to fall either before or after
system-generated ones. But perhaps someone else has a better idea.
I think that'd probably work, although I think that you probably
want to ensure that deferred constraint triggers run after normal
triggers and immediate constraints when you're running statements
in their own implicit transactions, since that would model the
behavior (check on commit) better.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
If we were to modify the code to make the firing order predictable,
I'd want it to be user-controllable. A simple hack that comes to
mind is to fire triggers in alphabetical order by name --- then you
can easily arrange for custom triggers to fall either before or after
system-generated ones. But perhaps someone else has a better idea.
I think that'd probably work, although I think that you probably
want to ensure that deferred constraint triggers run after normal
triggers and immediate constraints when you're running statements
in their own implicit transactions, since that would model the
behavior (check on commit) better.
Yes, the semantics of immediate and deferred triggers wouldn't change.
I'm just suggesting that when the system has a choice of legal firing
orders, it adopt an "alphabetical order" rule. AFAICS, all it would
take to implement this is for RelationBuildTriggers to sort the list
of triggers just after it's read them from pg_trigger and before it
inserts them into the TriggerDesc structure (ie, about line 638 of
trigger.c in current sources). The latter insertion is where they
are divided into categories, so the sorting would end up only affecting
the ordering within categories.
The interesting question is not that, really, but whether an
alphabetical-ordering rule will be useful and convenient. I don't
recall exactly how the system chooses names for triggers that it creates
--- if the user can't control those at all then this idea may not be
helpful.regards, tom lane
I think that'd probably work, although I think that you probably
want to ensure that deferred constraint triggers run after normal
triggers and immediate constraints when you're running statements
in their own implicit transactions, since that would model the
behavior (check on commit) better.Yes, the semantics of immediate and deferred triggers wouldn't change.
I'm just suggesting that when the system has a choice of legal firing
orders, it adopt an "alphabetical order" rule. AFAICS, all it would
take to implement this is for RelationBuildTriggers to sort the list
of triggers just after it's read them from pg_trigger and before it
inserts them into the TriggerDesc structure (ie, about line 638 of
trigger.c in current sources). The latter insertion is where they
are divided into categories, so the sorting would end up only affecting
the ordering within categories.The interesting question is not that, really, but whether an alphabetical-ordering rule will be useful and convenient. I don't recall exactly how the system chooses names for triggers that it creates --- if the user can't control those at all then this idea may not be helpful.
Should we get a system where the user can control the firing, perhaps
using oid order?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026On Fri, 7 Sep 2001, Tom Lane wrote:
Yes, the semantics of immediate and deferred triggers wouldn't change.
I'm just suggesting that when the system has a choice of legal firing
orders, it adopt an "alphabetical order" rule. AFAICS, all it would
take to implement this is for RelationBuildTriggers to sort the list
of triggers just after it's read them from pg_trigger and before it
inserts them into the TriggerDesc structure (ie, about line 638 of
trigger.c in current sources). The latter insertion is where they
are divided into categories, so the sorting would end up only affecting
the ordering within categories.The interesting question is not that, really, but whether an alphabetical-ordering rule will be useful and convenient. I don't recall exactly how the system chooses names for triggers that it creates --- if the user can't control those at all then this idea may not be helpful.
What other than foreign key constraints creates behind the scenes
triggers? The fk trigger names are currently RI_ConstraintTrigger_###.
I don't think there's any way right now to set their names (although
we could change the naming to <constraint name>_### or something like
that).
On Fri, 7 Sep 2001, Bruce Momjian wrote:
I think that'd probably work, although I think that you probably
want to ensure that deferred constraint triggers run after normal
triggers and immediate constraints when you're running statements
in their own implicit transactions, since that would model the
behavior (check on commit) better.Yes, the semantics of immediate and deferred triggers wouldn't change.
I'm just suggesting that when the system has a choice of legal firing
orders, it adopt an "alphabetical order" rule. AFAICS, all it would
take to implement this is for RelationBuildTriggers to sort the list
of triggers just after it's read them from pg_trigger and before it
inserts them into the TriggerDesc structure (ie, about line 638 of
trigger.c in current sources). The latter insertion is where they
are divided into categories, so the sorting would end up only affecting
the ordering within categories.The interesting question is not that, really, but whether an alphabetical-ordering rule will be useful and convenient. I don't recall exactly how the system chooses names for triggers that it creates --- if the user can't control those at all then this idea may not be helpful.Should we get a system where the user can control the firing, perhaps
using oid order?
I don't think oid order would help, because what happens if you've say got
a trigger and then want to add another before it? I'd guess the most
general way would be to give triggers some kind of numeric ordering not
associated with anything else, but then you need ways to set it on create
trigger and probably an alter trigger way to change it. :( I'd guess that
there'd be a default value if you didn't set it, and that triggers of the
same value would run in indeterminate order like before.
Should we get a system where the user can control the firing, perhaps
using oid order?I don't think oid order would help, because what happens if you've say got
a trigger and then want to add another before it? I'd guess the most
general way would be to give triggers some kind of numeric ordering not
associated with anything else, but then you need ways to set it on create
trigger and probably an alter trigger way to change it. :( I'd guess that
there'd be a default value if you didn't set it, and that triggers of the
same value would run in indeterminate order like before.
Yes, the problem with alphabetical order is that mere creation of a
trigger would change the firing order. I was thinking oid order so
there is some stability to the system for people who don't care about
the order, and a way to control it for people who need to (create
triggers in desired order).
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026On Fri, 7 Sep 2001, Bruce Momjian wrote:
Should we get a system where the user can control the firing, perhaps
using oid order?I don't think oid order would help, because what happens if you've say got
a trigger and then want to add another before it? I'd guess the most
general way would be to give triggers some kind of numeric ordering not
associated with anything else, but then you need ways to set it on create
trigger and probably an alter trigger way to change it. :( I'd guess that
there'd be a default value if you didn't set it, and that triggers of the
same value would run in indeterminate order like before.Yes, the problem with alphabetical order is that mere creation of a
trigger would change the firing order. I was thinking oid order so
there is some stability to the system for people who don't care about
the order, and a way to control it for people who need to (create
triggers in desired order).
The alphabetical ordering would change the order, but only with respect to
the new constraint and existing ones not between the existing ones. The
advantage here is that making a trigger fire before an existing one is
easy, since you can name before it.
The oid ordering has the property that the triggers would by default run
in creation order. Creating a new trigger would always put it at the end
unless you've wrapped oids which is nicer than having to worry about
interactions between the new trigger and existing ones based on where it
is (although you have to do that now), however putting a trigger before
an existing one means dropping and recreating one or more of your existing
triggers... all of the ones from where you want to put it in the order to
the last one.
The alphabetical ordering would change the order, but only with respect to
the new constraint and existing ones not between the existing ones. The
advantage here is that making a trigger fire before an existing one is
easy, since you can name before it.The oid ordering has the property that the triggers would by default run
in creation order. Creating a new trigger would always put it at the end
unless you've wrapped oids which is nicer than having to worry about
interactions between the new trigger and existing ones based on where it
is (although you have to do that now), however putting a trigger before
an existing one means dropping and recreating one or more of your existing
triggers... all of the ones from where you want to put it in the order to
the last one.
Good points. It is a tradeoff between making things stable for people
who don't notice the order vs. making it easy to define the ordering.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Added to TODO:
* Allow user to control trigger firing order
On Fri, 7 Sep 2001, Bruce Momjian wrote:
I think that'd probably work, although I think that you probably
want to ensure that deferred constraint triggers run after normal
triggers and immediate constraints when you're running statements
in their own implicit transactions, since that would model the
behavior (check on commit) better.Yes, the semantics of immediate and deferred triggers wouldn't change.
I'm just suggesting that when the system has a choice of legal firing
orders, it adopt an "alphabetical order" rule. AFAICS, all it would
take to implement this is for RelationBuildTriggers to sort the list
of triggers just after it's read them from pg_trigger and before it
inserts them into the TriggerDesc structure (ie, about line 638 of
trigger.c in current sources). The latter insertion is where they
are divided into categories, so the sorting would end up only affecting
the ordering within categories.The interesting question is not that, really, but whether an alphabetical-ordering rule will be useful and convenient. I don't recall exactly how the system chooses names for triggers that it creates --- if the user can't control those at all then this idea may not be helpful.Should we get a system where the user can control the firing, perhaps
using oid order?I don't think oid order would help, because what happens if you've say got
a trigger and then want to add another before it? I'd guess the most
general way would be to give triggers some kind of numeric ordering not
associated with anything else, but then you need ways to set it on create
trigger and probably an alter trigger way to change it. :( I'd guess that
there'd be a default value if you didn't set it, and that triggers of the
same value would run in indeterminate order like before.---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Tom Lane writes:
My another question is: When both UPDATE trigger (and
its trigger function) and UPDATE CASCADE constraint
are both defined, is the custom UPDATE trigger or the
UPDATE CASCADE constraint fired first?I wish the constraint will be fired first.
That seems fairly arbitrary; someone else might wish the opposite,
depending on the details of what they want to do.
We should probably check first whether the SQL standard has anything to
say about the relative ordering of foreign key cascade actions versus
triggers. (I would tend to think that triggers come after FK actions.
However, things might get tricky when cascade actions fire triggers of
their own.)
The order of execution of "pure" triggers meanwhile is defined thus:
The order of execution of a set of triggers is ascending by value
of their timestamp of creation in their descriptors, such that the
oldest trigger executes first. If one or more triggers have the
same timestamp value, then their relative order of execution is
implementation-defined. [4.35]
This is probably what happens in practice anyway, so it might make sense
to follow this rule.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
That seems fairly arbitrary; someone else might wish the opposite,
depending on the details of what they want to do.We should probably check first whether the SQL standard has anything to
say about the relative ordering of foreign key cascade actions versus
triggers. (I would tend to think that triggers come after FK actions.
However, things might get tricky when cascade actions fire triggers of
their own.)The order of execution of "pure" triggers meanwhile is defined thus:
The order of execution of a set of triggers is ascending by value
of their timestamp of creation in their descriptors, such that the
oldest trigger executes first. If one or more triggers have the
same timestamp value, then their relative order of execution is
implementation-defined. [4.35]This is probably what happens in practice anyway, so it might make sense
to follow this rule.
Yep, that would be a pretty strong vote for OID order.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Bruce Momjian <pgman@candle.pha.pa.us> writes:
The order of execution of a set of triggers is ascending by value
of their timestamp of creation in their descriptors, such that the
oldest trigger executes first.
Yep, that would be a pretty strong vote for OID order.
Au contraire: OID wraparound would cause us to violate the spec.
If we want to follow the spec here, then I think we'd need to add
a creation-timestamp column to pg_trigger, and sort on that.
regards, tom lane
On Fri, 7 Sep 2001, Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
The order of execution of a set of triggers is ascending by value
of their timestamp of creation in their descriptors, such that the
oldest trigger executes first.Yep, that would be a pretty strong vote for OID order.
Au contraire: OID wraparound would cause us to violate the spec.
If we want to follow the spec here, then I think we'd need to add
a creation-timestamp column to pg_trigger, and sort on that.
If you are going to create a separate column for sorting, why not just
use a numeric (int8?) value, so one can change that as sees fit? Maybe
associated with a database-wide sequence...
--
Alvaro Herrera (<alvherre[@]atentus.com>)
I don't think oid order would help, because what
happens if you've say got a trigger and then want
to add another before it? I'd guess the most
general way would be to give triggers some kind of
numeric ordering not associated with anything else,
but then you need ways to set it on create
trigger and probably an alter trigger way to
change it. :( I'd guess that there'd be a
default value if you didn't set it, and that
triggers of the same value would run in
indeterminate order like before.
Being an ignorant end user, I would like to add some
humble and stupid opinions.
I too feel using creation timestamps of FK constraint
and custom trigger to determine the firing order
being inconvient for me due to 2 problems:
Problem 1:
I do a lot of table creations like this:
CREATE TABLE table1 (
CONSTRAINT fk1 FOREIGN KEY (field1) REFERENCES table2
(field1) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (field1,field2),
field1 TEXT,
field2 TEXT,
field3 TEXT
);
Then, I create the trigger for table1 UPDATE event.
If the execution order is determined by timestamp or
oid, then I really am happy _now_. However, as
pointed by you experts, problem happens when I want
to change my mind and want my trigger be fired before
fk1. Since trigger can only be created after table is
created, there is no way for me to make the trigger
fired before FK. Am I correct?
Problem 2:
Suppose I dump the database and drop it and then
restore it from the dump file, and my machine runs
"too fast", can it happen that postgresql creates the
same timestamps for the FK and trigger?
Best Regards,
CN
--------------------------------------------------------
You too can have your own email address from Eurosport.
http://www.eurosport.com
Import Notes
Resolved by subject fallback
On Fri, 7 Sep 2001 21:20:16 +0000 (UTC), alvherre@atentus.com (Alvaro
Herrera) wrote:
[snip]
Au contraire: OID wraparound would cause us to violate the spec.
If we want to follow the spec here, then I think we'd need to add
a creation-timestamp column to pg_trigger, and sort on that.If you are going to create a separate column for sorting, why not just
use a numeric (int8?) value, so one can change that as sees fit?
[snip]
Seconded..
But then again, whats to stop DBA just changing the value in the date
column?