Thread: 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 19026
On 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 19026
On 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 19026
Added 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 19026
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.
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
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?