Thread: Why are triggers semi-deferred?
At least in 7.3, triggered actions specified as AFTER seem to be deferred
to the completion of the outer-most SQL statement. So, if two triggering
statements are executed as part of a PLPGSQL procedure, they will not be
executed until the outermost statement finishes.
As far as I can tell, this is not the way the spec says it should work:
The <triggered SQL statement> of a triggered action is effectively executed either immediately before or
immediately after the trigger event, as determined by the specified trigger action time.
In the case of statements executed sequentially inside a PLPGSQL procedure,
I would have expected that the trigger would fire after the first
triggering statement.
Have I missed a discussion of this (I have looked), or is this just a known
problem?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
On Mon, 5 May 2003, Philip Warner wrote: > At least in 7.3, triggered actions specified as AFTER seem to be deferred > to the completion of the outer-most SQL statement. So, if two triggering > statements are executed as part of a PLPGSQL procedure, they will not be > executed until the outermost statement finishes. > > As far as I can tell, this is not the way the spec says it should work: > > The <triggered SQL statement> of a triggered action is > effectively executed either immediately before or immediately > after the trigger event, as determined by the specified > trigger action time. > > In the case of statements executed sequentially inside a PLPGSQL procedure, > I would have expected that the trigger would fire after the first > triggering statement. Actually, I think from sql99's description, for after row triggers it should happen after the row is modified not after the statement as a whole (so given two 2 row updates in a function you'd getupdate1,row1 afterrow1-1 update1,row2 afterrow1-2,afterstatement1update2,row1afterrow2-1 update2,row2 afterrow2-2,afterstatement2 ) I'd been thinking (this came up recently is a bug/query about the foreign keys that Tom sent me) that it might be better to make times that the triggers can run be choosable (with the spec behavior becoming default eventually) because we've got backward compatibility issues and we've kind overloaded the trigger system to do the foreign keys which have their own timing issues.
At 08:20 AM 5/05/2003 -0700, Stephan Szabo wrote:
>Actually, I think from sql99's description, for after row triggers it
>should happen after the row is modified not after the statement as a
>whole (so given two 2 row updates in a function you'd get
> update1,row1 afterrow1-1 update1,row2 afterrow1-2,afterstatement1
> update2,row1 afterrow2-1 update2,row2 afterrow2-2,afterstatement2
>)
Totally agree (I think) -- I am not sure how to interpret your example. To
(I hope) clarify: if a function has two update statements (A & B), each of
which update two rows (1 & 2), I would expect the triggers to fire as:
Procedure Starts Statement A executes: Before Row 1 After Row 1 Before Row 2 After Row 2
Statement Trigger for A Statement B executes: Before Row 1 After Row 1 Before Row 2
After Row 2 Statement Trigger for B
Procedure Ends
At the current time in 7.3, we have:
Procedure Starts Statement A executes: Before Row 1 Before Row 2 Statement B executes:
BeforeRow 1 Before Row 2
Procedure End
After Row 1
After Row 2
After Row 1
After Row 2
...which seems weird to me. Is this something that needs fixing in 7.3.3?
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \| | --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
At 08:20 AM 5/05/2003 -0700, Stephan Szabo wrote: >it might be better to make times that the >triggers can run be choosable (with the spec behavior becoming default >eventually) because we've got backward compatibility issues and we've kind >overloaded the trigger system to do the foreign keys which have their own >timing issues. I think you are right here too; we need some way to make the triggers function according to the spec, as well as to preserve compatibility for constraint settings -- at least constrint triggers should fire when the constraints expect it, and normal triggers should fire when the spec says they should fire. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
On Tue, 6 May 2003, Philip Warner wrote: > At 08:20 AM 5/05/2003 -0700, Stephan Szabo wrote: > >it might be better to make times that the > >triggers can run be choosable (with the spec behavior becoming default > >eventually) because we've got backward compatibility issues and we've kind > >overloaded the trigger system to do the foreign keys which have their own > >timing issues. > > I think you are right here too; we need some way to make the triggers > function according to the spec, as well as to preserve compatibility for > constraint settings -- at least constrint triggers should fire when the > constraints expect it, and normal triggers should fire when the spec says > they should fire. Actually, we'll probably want to allow it for normal triggers as well. I think it's likely to break current triggers that people are using or at least change semantics. For example, triggers that were made by users that are doing some checks that currently assume that the full set of actions have already been done, or one that does stuff outside the database that now has to worry about stuff that used to be before it erroring after it (sure it was unsafe before but now it's more unsafe). Some of these will be rewritable (esp if we get statement triggers that can see new/old rowsets), but I don't know if we want to force that. [from other message] >...which seems weird to me. Is this something that needs fixing in 7.3.3? I think it's likely to be too large to be safe to put into 7.3.x. Just changing the times isn't too hard I think (rather than unconditionally adding to the queue, determine if its immediate and run it and put the deferred ones into the queue I think) but that doesn't deal with the other timing issues which should be part of that.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Actually, I think from sql99's description, for after row triggers it
> should happen after the row is modified not after the statement as a
> whole (so given two 2 row updates in a function you'd get
> update1,row1 afterrow1-1 update1,row2 afterrow1-2,afterstatement1
> update2,row1 afterrow2-1 update2,row2 afterrow2-2,afterstatement2
> )
[ scratches head ... ] That seems a useless definition. What is the
purpose of firing immediately after, rather than immediately before,
a row update? Wouldn't you want to wait till end of statement so you
know that the whole statement is in fact going to complete (and not
die at some later row)? What do you have immediately after the update
that you didn't have just before it?
regards, tom lane
Philip Warner <pjw@rhyme.com.au> writes:
> ...which seems weird to me. Is this something that needs fixing in 7.3.3?
It would be a really bad idea to make such a significant change in
trigger behavior in a dot-release ... think of it as a 7.4 proposal,
instead. (Or maybe 7.5, considering how close the 7.4 feature freeze
date is.)
regards, tom lane
At 12:09 PM 5/05/2003 -0400, Tom Lane wrote: >think of it as a 7.4 proposal, >instead. (Or maybe 7.5, considering how close the 7.4 feature freeze >date is.) Good point. How close is it? It would be good to see this fixed in 7.4. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 12:07 PM 5/05/2003 -0400, Tom Lane wrote: >Wouldn't you want to wait till end of statement so you >know that the whole statement is in fact going to complete (and not >die at some later row)? So you are suggesting: Procedure Starts Statement A executes: Before Row 1 Update Row 1 Before Row 2 UpdateRow 2 After Row 1 After Row 2 Statement Trigger for A Statement B executes: Before Row1 Update Row 1 Before Row 2 Update Row 2 After Row 1 After Row 2 StatementTrigger for B Procedure Ends This seems like a nice optimization, but probably disagrees with the spec since we would be deferring the triggerred action (slightly). From a users point of view, I would be happy with it, and even prefer it to my interpretation of the spec. But in the case of multi-row updates, won't it be expensive to keep all the context? ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Philip Warner <pjw@rhyme.com.au> writes:
> But in the case of multi-row updates, won't it be expensive to keep all the
> context?
We do that already for AFTER triggers. The question is just when the
trigger ought to be fired.
regards, tom lane
Philip Warner <pjw@rhyme.com.au> writes:
>> instead. (Or maybe 7.5, considering how close the 7.4 feature freeze
>> date is.)
> Good point. How close is it? It would be good to see this fixed in 7.4.
The target is to go beta June 1, so we need to freeze features probably
a week or two before that ... in other words, Real Soon Now.
regards, tom lane
On Mon, 5 May 2003, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > Actually, I think from sql99's description, for after row triggers it > > should happen after the row is modified not after the statement as a > > whole (so given two 2 row updates in a function you'd get > > update1,row1 afterrow1-1 update1,row2 afterrow1-2,afterstatement1 > > update2,row1 afterrow2-1 update2,row2 afterrow2-2,afterstatement2 > > ) > > [ scratches head ... ] That seems a useless definition. What is the > purpose of firing immediately after, rather than immediately before, > a row update? Wouldn't you want to wait till end of statement so you > know that the whole statement is in fact going to complete (and not > die at some later row)? What do you have immediately after the update > that you didn't have just before it? You're right, I'd misread "the trigger event" as being a row change for a row trigger (go figure). However, looking at it, then I'm not sure our before row trigger timing is correct then. It seems from 14.14 for a delete example that the timing is supposed to be something like: before trigger 1before trigger 2delete 1delete 2after trigger 1after trigger 2 rather than:before trigger 1delete 1before trigger 2delete 2after trigger 1after trigger 2
I am trying to figure out whether there is a TODO item in this thread.
The basis of the discussion appeared to be whether we are honoring the
spec by executing before/after statement/row/constraint triggers
properly, and if we are not, is it desirable/significant if we break the
spec.
Which need changes?
Before | After
Statement |
Row |
Constraint
Does anyone have answers for these? I read the thread and don't 100%
understand it all.
---------------------------------------------------------------------------
Stephan Szabo wrote:
>
> On Mon, 5 May 2003, Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > > Actually, I think from sql99's description, for after row triggers it
> > > should happen after the row is modified not after the statement as a
> > > whole (so given two 2 row updates in a function you'd get
> > > update1,row1 afterrow1-1 update1,row2 afterrow1-2,afterstatement1
> > > update2,row1 afterrow2-1 update2,row2 afterrow2-2,afterstatement2
> > > )
> >
> > [ scratches head ... ] That seems a useless definition. What is the
> > purpose of firing immediately after, rather than immediately before,
> > a row update? Wouldn't you want to wait till end of statement so you
> > know that the whole statement is in fact going to complete (and not
> > die at some later row)? What do you have immediately after the update
> > that you didn't have just before it?
>
> You're right, I'd misread "the trigger event" as being a row change for a
> row trigger (go figure). However, looking at it, then I'm not sure our
> before row trigger timing is correct then. It seems from 14.14 for a
> delete example that the timing is supposed to be something like:
>
> before trigger 1
> before trigger 2
> delete 1
> delete 2
> after trigger 1
> after trigger 2
>
> rather than:
> before trigger 1
> delete 1
> before trigger 2
> delete 2
> after trigger 1
> after trigger 2
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
At 11:51 PM 1/06/2003 -0400, Bruce Momjian wrote: >Does anyone have answers for these? I read the thread and don't 100% >understand it all. My belief is that at least ROW triggers need fixing (7.3 doesn't have statement, not sure about 7.4). Currently, if you write a plpgsql procedure which calls more than one insert/update/delete statements, the AFTER triggers for all of these statements will not fire until after the procedure exits. They should fire either just after each row is updated, or just after the most immediately enclosing statement executes. I think the thread wanted the latter. So, if we have a table with two rows, and a BEFORE and AFTER trigger, and a plpgsql procedure that updates all rows twice, then we should have: procedure called procedure executes first update before trigger fires(row 1) before trigger fires(row 2) row1 updated row 2 updated after trigger fires(row 1) after trigger fires(row 2) procedure executes second update before trigger fires(row 1) before trigger fires(row 2) row 1 updated row 2 updated after triggerfires(row 1) after trigger fires(row 2) procedure exits What we have in 7.3 is: procedure called procedure executes first update before trigger fires(row 1) before trigger fires(row 2) row1 updated row 2 updated procedure executes second update before trigger fires(row 1) before trigger fires(row2) row 1 updated row 2 updated procedure exits after trigger fires(row 1) after trigger fires(row 2) after trigger fires(row 1) after trigger fires(row 2) IIRC, the thread did not really discuss whether do intersperse the BEFORE executions with the updates, but doing them all before seems consistent. Apologies is this has been covered elsewhere... ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
At 10:38 AM 12/07/2003 -0700, Stephan Szabo wrote: > deferred after trigger row 1 > deferred after trigger #2 row a > deferred after trigger row 2 > deferred after trigger #2 row b I'd vote for this; ie. make them execute in the same order they would execute if they were not deferred. Otherwise you open up all sorts of weird application errors if a trigger is deferred/not-deferred. ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 03 5330 3172 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/
Added to TODO: * Have AFTER triggers execute after the appropriate SQL statement in a function, not at the end of the function --------------------------------------------------------------------------- Philip Warner wrote: > At 11:51 PM 1/06/2003 -0400, Bruce Momjian wrote: > >Does anyone have answers for these? I read the thread and don't 100% > >understand it all. > > My belief is that at least ROW triggers need fixing (7.3 doesn't have > statement, not sure about 7.4). > > Currently, if you write a plpgsql procedure which calls more than one > insert/update/delete statements, the AFTER triggers for all of these > statements will not fire until after the procedure exits. They should fire > either just after each row is updated, or just after the most immediately > enclosing statement executes. I think the thread wanted the latter. > > So, if we have a table with two rows, and a BEFORE and AFTER trigger, and a > plpgsql procedure that updates all rows twice, then we should have: > > procedure called > procedure executes first update > before trigger fires(row 1) > before trigger fires(row 2) > row 1 updated > row 2 updated > after trigger fires(row 1) > after trigger fires(row 2) > procedure executes second update > before trigger fires(row 1) > before trigger fires(row 2) > row 1 updated > row 2 updated > after trigger fires(row 1) > after trigger fires(row 2) > procedure exits > > What we have in 7.3 is: > > procedure called > procedure executes first update > before trigger fires(row 1) > before trigger fires(row 2) > row 1 updated > row 2 updated > procedure executes second update > before trigger fires(row 1) > before trigger fires(row 2) > row 1 updated > row 2 updated > procedure exits > after trigger fires(row 1) > after trigger fires(row 2) > after trigger fires(row 1) > after trigger fires(row 2) > > IIRC, the thread did not really discuss whether do intersperse the BEFORE > executions with the updates, but doing them all before seems consistent. > > Apologies is this has been covered elsewhere... > > > > > > > > > ---------------------------------------------------------------- > Philip Warner | __---_____ > Albatross Consulting Pty. Ltd. |----/ - \ > (A.B.N. 75 008 659 498) | /(@) ______---_ > Tel: (+61) 0500 83 82 81 | _________ \ > Fax: (+61) 03 5330 3172 | ___________ | > Http://www.rhyme.com.au | / \| > | --________-- > PGP key available upon request, | / > and from pgp5.ai.mit.edu:11371 |/ > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073