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


Mon, 05 May 2003 15:30:23 +1000
Philip Warner <pjw@rhyme.com.au>
Why are triggers semi-deferred?
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.


Mon, 5 May 2003 08:20:23 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Why are triggers semi-deferred?
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   |/


Tue, 06 May 2003 01:31:58 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Why are triggers semi-deferred?
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   |/


Tue, 06 May 2003 01:35:56 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Why are triggers semi-deferred?
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.


Mon, 5 May 2003 09:07:18 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Why are triggers semi-deferred?
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


Mon, 05 May 2003 12:07:48 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why are triggers semi-deferred? 
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


Mon, 05 May 2003 12:09:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why are triggers semi-deferred? 
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   |/


Tue, 06 May 2003 02:14:52 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Why are triggers semi-deferred? 
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   |/


Tue, 06 May 2003 02:23:54 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Why are triggers semi-deferred? 
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


Mon, 05 May 2003 12:55:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why are triggers semi-deferred? 
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


Mon, 05 May 2003 12:58:04 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why are triggers semi-deferred? 

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


Mon, 5 May 2003 10:58:42 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Why are triggers semi-deferred? 

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, Pennsylvania 19073

Sun, 1 Jun 2003 23:51:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why are triggers semi-deferred?
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   |/


Sun, 13 Jul 2003 00:48:41 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Why are triggers semi-deferred?
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   |/


Sun, 13 Jul 2003 12:02:58 +1000
Philip Warner <pjw@rhyme.com.au>
Re: Why are triggers semi-deferred?

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, Pennsylvania 19073

Mon, 21 Jul 2003 15:04:15 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Why are triggers semi-deferred?