Re: 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 get
update1,row1 afterrow1-1 update1,row2 afterrow1-2,afterstatement1
update2,row1 afterrow2-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:
Before Row 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(at)megazone23(dot)bigpanda(dot)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(at)rhyme(dot)com(dot)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
Update Row 2
After Row 1
After Row 2
Statement Trigger for A
Statement B executes:
Before Row 1
Update Row 1
Before Row 2
Update Row 2
After Row 1
After Row 2
Statement Trigger 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(at)rhyme(dot)com(dot)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(at)rhyme(dot)com(dot)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(at)megazone23(dot)bigpanda(dot)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

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(at)megazone23(dot)bigpanda(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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 |/

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073