Thread

1. Work-in-progress referential action trigger timing patch

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2005-08-24T03:47:55Z

   Here's my current work in progress for 8.1 devel related to fixing the
   timing issues with referential actions having their checks run on
   intermediate states.  I've only put in a simple test that failed against
   8.0 in the regression patch and regression still passes for me.  There's
   still an outstanding question of whether looping gives the correct result
   in the presence of explicit inserts and set constraints immediate in
   before triggers.

2. Re: Work-in-progress referential action trigger timing patch

   Allan Wang <allanvv@gmail.com> — 2005-08-29T03:13:59Z

   On Tue, 2005-08-23 at 20:47 -0700, Stephan Szabo wrote:
   > Here's my current work in progress for 8.1 devel related to fixing the
   > timing issues with referential actions having their checks run on
   > intermediate states.  I've only put in a simple test that failed against
   > 8.0 in the regression patch and regression still passes for me.  There's
   > still an outstanding question of whether looping gives the correct result
   > in the presence of explicit inserts and set constraints immediate in
   > before triggers.
   
   Thanks, I'll try this patch and get back to you on the results.
   
   Allan Wang
   
   
   

3. Re: Work-in-progress referential action trigger timing patch

   Allan Wang <allanvv@gmail.com> — 2005-08-30T02:35:18Z

   On Tue, 2005-08-23 at 20:47 -0700, Stephan Szabo wrote:
   > Here's my current work in progress for 8.1 devel related to fixing the
   > timing issues with referential actions having their checks run on
   > intermediate states.  I've only put in a simple test that failed against
   > 8.0 in the regression patch and regression still passes for me.  There's
   > still an outstanding question of whether looping gives the correct result
   > in the presence of explicit inserts and set constraints immediate in
   > before triggers.
   
   Yup, this patch fixed my issues.
   
   Allan Wang
   
   
   

4. Re: Work-in-progress referential action trigger timing

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2005-09-01T22:59:15Z

   On Tue, 23 Aug 2005, Stephan Szabo wrote:
   
   > Here's my current work in progress for 8.1 devel related to fixing the
   > timing issues with referential actions having their checks run on
   > intermediate states.  I've only put in a simple test that failed against
   > 8.0 in the regression patch and regression still passes for me.  There's
   > still an outstanding question of whether looping gives the correct result
   > in the presence of explicit inserts and set constraints immediate in
   > before triggers.
   
   As Darcy noticed, the patch as given does definately still have problems
   with before triggers.  I was able to construct a case that violates the
   constraint with an update in a before delete trigger.  I think this might
   be why the spec has the wierd timing rules for before triggers on cascaded
   deletes such that the deletions happen before the before triggers.
   
   We have a similar problem for before triggers that update the rows that
   are being cascade updated.  The following seems to violate the constraint
   for me on 8.0.3:
   
   drop table pk cascade;
   drop table fk cascade;
   drop function fk_move();
   
   create table pk(a int primary key);
   create table fk(a int references pk on delete cascade on update cascade, b
   int);
   create function fk_move() returns trigger as '
    begin
     raise notice '' about to move for % '', old.b;
     update fk set b=b-1 where b > old.b;
     return new;
    end;' language 'plpgsql';
   create trigger fkmovetrig before update on fk for each row execute
   procedure fk_move();
   insert into pk values(1);
   insert into pk values(2);
   insert into fk values(1,1);
   insert into fk values(1,2);
   insert into fk values(2,3);
   select * from pk;
   select * from fk;
   update pk set a = 3 where a = 1;
   select * from pk;
   select * from fk;
   
   This gives me (3,1), (1,1) and (2,2) as the rows in fk where the (1,1) row
   is invalid.  This is obviously wrong, but the question is, what is the
   correct answer?  Should the update in the before trigger trying to change
   b on a row that no longer has a reference have errored?
   
   

5. Re: [PATCHES] Work-in-progress referential action trigger timing

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2005-09-03T05:16:07Z

   [Hackers now seems more appropriate]
   
   On Thu, 1 Sep 2005, Stephan Szabo wrote:
   
   >
   > On Tue, 23 Aug 2005, Stephan Szabo wrote:
   >
   > > Here's my current work in progress for 8.1 devel related to fixing the
   > > timing issues with referential actions having their checks run on
   > > intermediate states.  I've only put in a simple test that failed against
   > > 8.0 in the regression patch and regression still passes for me.  There's
   > > still an outstanding question of whether looping gives the correct result
   > > in the presence of explicit inserts and set constraints immediate in
   > > before triggers.
   >
   > As Darcy noticed, the patch as given does definately still have problems
   > with before triggers.  I was able to construct a case that violates the
   > constraint with an update in a before delete trigger.  I think this might
   > be why the spec has the wierd timing rules for before triggers on cascaded
   > deletes such that the deletions happen before the before triggers.
   >
   > We have a similar problem for before triggers that update the rows that
   > are being cascade updated.  The following seems to violate the constraint
   > for me on 8.0.3:
   >
   > drop table pk cascade;
   > drop table fk cascade;
   > drop function fk_move();
   >
   > create table pk(a int primary key);
   > create table fk(a int references pk on delete cascade on update cascade, b
   > int);
   > create function fk_move() returns trigger as '
   >  begin
   >   raise notice '' about to move for % '', old.b;
   >   update fk set b=b-1 where b > old.b;
   >   return new;
   >  end;' language 'plpgsql';
   > create trigger fkmovetrig before update on fk for each row execute
   > procedure fk_move();
   > insert into pk values(1);
   > insert into pk values(2);
   > insert into fk values(1,1);
   > insert into fk values(1,2);
   > insert into fk values(2,3);
   > select * from pk;
   > select * from fk;
   > update pk set a = 3 where a = 1;
   > select * from pk;
   > select * from fk;
   >
   > This gives me (3,1), (1,1) and (2,2) as the rows in fk where the (1,1) row
   > is invalid.  This is obviously wrong, but the question is, what is the
   > correct answer?  Should the update in the before trigger trying to change
   > b on a row that no longer has a reference have errored?
   
   Well, the spec seems to get out of this simply. I read SQL2003's trigger
   execution information (specifically 14.27 GR5g*) to say that before
   triggers that call data changing statements are invalid.
   
   We can't do that for compatibility reasons, but it would allow us to say
   that modifying a row in a before trigger that is also a row selected in
   the outer statement is an error for this update case.  It'd presumably be
   an error for a normal delete as well, although I think it might be
   relaxable for cascaded deletes because the spec seems to say that the
   before triggers for deletions caused by the cascade are actually run after
   the removals. I'm not sure whether we could easily differentiate this case
   from any other cases where the row was modified twice either yet.
   
   ---
   * "If TR is a BEFORE trigger and if, before the completion of the
   execution of an <SQL procedure statement> simply contained in TSS, an
   attempt is made to execute an SQL-data change statement or an SQL-invoked
   routine that possibly modifies SQL-data, then an exception condition is
   raised:  prohibited statement encountered during trigger execution."
   
   

6. Re: [PATCHES] Work-in-progress referential action trigger

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2005-09-09T14:36:15Z

   On Fri, 2 Sep 2005, Stephan Szabo wrote:
   
   > [Hackers now seems more appropriate]
   >
   > On Thu, 1 Sep 2005, Stephan Szabo wrote:
   >
   > >
   > > On Tue, 23 Aug 2005, Stephan Szabo wrote:
   > >
   > > > Here's my current work in progress for 8.1 devel related to fixing the
   > > > timing issues with referential actions having their checks run on
   > > > intermediate states.  I've only put in a simple test that failed against
   > > > 8.0 in the regression patch and regression still passes for me.  There's
   > > > still an outstanding question of whether looping gives the correct result
   > > > in the presence of explicit inserts and set constraints immediate in
   > > > before triggers.
   > >
   > > As Darcy noticed, the patch as given does definately still have problems
   > > with before triggers.  I was able to construct a case that violates the
   > > constraint with an update in a before delete trigger.  I think this might
   > > be why the spec has the wierd timing rules for before triggers on cascaded
   > > deletes such that the deletions happen before the before triggers.
   > >
   > > We have a similar problem for before triggers that update the rows that
   > > are being cascade updated.  The following seems to violate the constraint
   > > for me on 8.0.3:
   > >
   > > drop table pk cascade;
   > > drop table fk cascade;
   > > drop function fk_move();
   > >
   > > create table pk(a int primary key);
   > > create table fk(a int references pk on delete cascade on update cascade, b
   > > int);
   > > create function fk_move() returns trigger as '
   > >  begin
   > >   raise notice '' about to move for % '', old.b;
   > >   update fk set b=b-1 where b > old.b;
   > >   return new;
   > >  end;' language 'plpgsql';
   > > create trigger fkmovetrig before update on fk for each row execute
   > > procedure fk_move();
   > > insert into pk values(1);
   > > insert into pk values(2);
   > > insert into fk values(1,1);
   > > insert into fk values(1,2);
   > > insert into fk values(2,3);
   > > select * from pk;
   > > select * from fk;
   > > update pk set a = 3 where a = 1;
   > > select * from pk;
   > > select * from fk;
   > >
   > > This gives me (3,1), (1,1) and (2,2) as the rows in fk where the (1,1) row
   > > is invalid.  This is obviously wrong, but the question is, what is the
   > > correct answer?  Should the update in the before trigger trying to change
   > > b on a row that no longer has a reference have errored?
   >
   > We can't do that for compatibility reasons, but it would allow us to say
   > that modifying a row in a before trigger that is also a row selected in
   > the outer statement is an error for this update case.  It'd presumably be
   > an error for a normal delete as well, although I think it might be
   > relaxable for cascaded deletes because the spec seems to say that the
   > before triggers for deletions caused by the cascade are actually run after
   > the removals. I'm not sure whether we could easily differentiate this case
   > from any other cases where the row was modified twice either yet.
   
   Is there a case other than a before trigger updating a row we will want to
   act upon later in the statement where we'll get a row with xmax of our
   transaction and cmax greater than the current command?
   
   

7. Re: [PATCHES] Work-in-progress referential action trigger timing

   Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-09T14:50:27Z

   Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
   > Is there a case other than a before trigger updating a row we will want to
   > act upon later in the statement where we'll get a row with xmax of our
   > transaction and cmax greater than the current command?
   
   The greater-cmax case could occur via any kind of function, not only a
   trigger, ie
   
   	update tab set x = foo(x) where ...
   
   where foo() is a volatile function that internally updates the tab
   table.
   
   I suppose you could say that this is horrible programming practice and
   anyone who tries it deserves whatever weird behavior ensues ... but
   it's not the case that every such situation involves a trigger.
   
   			regards, tom lane
   
   

8. Re: [PATCHES] Work-in-progress referential action trigger

   Stephan Szabo <sszabo@megazone.bigpanda.com> — 2005-09-09T15:46:03Z

   On Fri, 9 Sep 2005, Tom Lane wrote:
   
   > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
   > > Is there a case other than a before trigger updating a row we will want to
   > > act upon later in the statement where we'll get a row with xmax of our
   > > transaction and cmax greater than the current command?
   >
   > The greater-cmax case could occur via any kind of function, not only a
   > trigger, ie
   >
   > 	update tab set x = foo(x) where ...
   >
   > where foo() is a volatile function that internally updates the tab
   > table.
   
   I *thought* I was missing a case, I just couldn't figure out what.
   
   > I suppose you could say that this is horrible programming practice and
   > anyone who tries it deserves whatever weird behavior ensues ... but
   > it's not the case that every such situation involves a trigger.
   
   Well, the change I was thinking of would have made it an error if foo(x)
   updated a row that was then later selected by the update rather than the
   current behavior which I think would have ignored the already updated row,
   so that's probably not going to work.
   
   

9. Re: [PATCHES] Work-in-progress referential action trigger

   Darcy Buskermolen <darcy@wavefire.com> — 2005-10-13T17:23:19Z

   On Friday 09 September 2005 08:46, Stephan Szabo wrote:
   > On Fri, 9 Sep 2005, Tom Lane wrote:
   > > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
   > > > Is there a case other than a before trigger updating a row we will want
   > > > to act upon later in the statement where we'll get a row with xmax of
   > > > our transaction and cmax greater than the current command?
   > >
   > > The greater-cmax case could occur via any kind of function, not only a
   > > trigger, ie
   > >
   > > 	update tab set x = foo(x) where ...
   > >
   > > where foo() is a volatile function that internally updates the tab
   > > table.
   >
   > I *thought* I was missing a case, I just couldn't figure out what.
   >
   > > I suppose you could say that this is horrible programming practice and
   > > anyone who tries it deserves whatever weird behavior ensues ... but
   > > it's not the case that every such situation involves a trigger.
   >
   > Well, the change I was thinking of would have made it an error if foo(x)
   > updated a row that was then later selected by the update rather than the
   > current behavior which I think would have ignored the already updated row,
   > so that's probably not going to work.
   
   I see that this still is not addressed fulling in beta 3.  Can anybody give a 
   quick overview of where this is sitting, and if it's likely to make it's way 
   into 8.1 gold ?
   
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 3: Have you checked our extensive FAQ?
   >
   >                http://www.postgresql.org/docs/faq
   
   -- 
   Darcy Buskermolen
   Wavefire Technologies Corp.
   
   http://www.wavefire.com
   ph: 250.717.0200
   fx: 250.763.1759
   
   

10. Foreign key trigger timing bug?

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-12-07T17:46:22Z

    I think this is the foreign key trigger timing issue.
    
    ---------------------------------------------------------------------------
    
    Darcy Buskermolen wrote:
    > On Friday 09 September 2005 08:46, Stephan Szabo wrote:
    > > On Fri, 9 Sep 2005, Tom Lane wrote:
    > > > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
    > > > > Is there a case other than a before trigger updating a row we will want
    > > > > to act upon later in the statement where we'll get a row with xmax of
    > > > > our transaction and cmax greater than the current command?
    > > >
    > > > The greater-cmax case could occur via any kind of function, not only a
    > > > trigger, ie
    > > >
    > > > 	update tab set x = foo(x) where ...
    > > >
    > > > where foo() is a volatile function that internally updates the tab
    > > > table.
    > >
    > > I *thought* I was missing a case, I just couldn't figure out what.
    > >
    > > > I suppose you could say that this is horrible programming practice and
    > > > anyone who tries it deserves whatever weird behavior ensues ... but
    > > > it's not the case that every such situation involves a trigger.
    > >
    > > Well, the change I was thinking of would have made it an error if foo(x)
    > > updated a row that was then later selected by the update rather than the
    > > current behavior which I think would have ignored the already updated row,
    > > so that's probably not going to work.
    > 
    > I see that this still is not addressed fulling in beta 3.  Can anybody give a 
    > quick overview of where this is sitting, and if it's likely to make it's way 
    > into 8.1 gold ?
    > 
    > >
    > > ---------------------------(end of broadcast)---------------------------
    > > TIP 3: Have you checked our extensive FAQ?
    > >
    > >                http://www.postgresql.org/docs/faq
    > 
    > -- 
    > Darcy Buskermolen
    > Wavefire Technologies Corp.
    > 
    > http://www.wavefire.com
    > ph: 250.717.0200
    > fx: 250.763.1759
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 2: Don't 'kill -9' the postmaster
    > 
    
    -- 
      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
    
    

11. Re: [PATCHES] Work-in-progress referential action trigger

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-14T18:36:38Z

    Added to TODO:
    
            o Fix problem when cascading referential triggers make changes on
              cascaded tables, seeing the tables in an intermediate state
    
             http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php
             http://archives.postgresql.org/pgsql-hackers/2005-09/msg00174.php
    
    
    ---------------------------------------------------------------------------
    
    Stephan Szabo wrote:
    > [Hackers now seems more appropriate]
    > 
    > On Thu, 1 Sep 2005, Stephan Szabo wrote:
    > 
    > >
    > > On Tue, 23 Aug 2005, Stephan Szabo wrote:
    > >
    > > > Here's my current work in progress for 8.1 devel related to fixing the
    > > > timing issues with referential actions having their checks run on
    > > > intermediate states.  I've only put in a simple test that failed against
    > > > 8.0 in the regression patch and regression still passes for me.  There's
    > > > still an outstanding question of whether looping gives the correct result
    > > > in the presence of explicit inserts and set constraints immediate in
    > > > before triggers.
    > >
    > > As Darcy noticed, the patch as given does definately still have problems
    > > with before triggers.  I was able to construct a case that violates the
    > > constraint with an update in a before delete trigger.  I think this might
    > > be why the spec has the wierd timing rules for before triggers on cascaded
    > > deletes such that the deletions happen before the before triggers.
    > >
    > > We have a similar problem for before triggers that update the rows that
    > > are being cascade updated.  The following seems to violate the constraint
    > > for me on 8.0.3:
    > >
    > > drop table pk cascade;
    > > drop table fk cascade;
    > > drop function fk_move();
    > >
    > > create table pk(a int primary key);
    > > create table fk(a int references pk on delete cascade on update cascade, b
    > > int);
    > > create function fk_move() returns trigger as '
    > >  begin
    > >   raise notice '' about to move for % '', old.b;
    > >   update fk set b=b-1 where b > old.b;
    > >   return new;
    > >  end;' language 'plpgsql';
    > > create trigger fkmovetrig before update on fk for each row execute
    > > procedure fk_move();
    > > insert into pk values(1);
    > > insert into pk values(2);
    > > insert into fk values(1,1);
    > > insert into fk values(1,2);
    > > insert into fk values(2,3);
    > > select * from pk;
    > > select * from fk;
    > > update pk set a = 3 where a = 1;
    > > select * from pk;
    > > select * from fk;
    > >
    > > This gives me (3,1), (1,1) and (2,2) as the rows in fk where the (1,1) row
    > > is invalid.  This is obviously wrong, but the question is, what is the
    > > correct answer?  Should the update in the before trigger trying to change
    > > b on a row that no longer has a reference have errored?
    > 
    > Well, the spec seems to get out of this simply. I read SQL2003's trigger
    > execution information (specifically 14.27 GR5g*) to say that before
    > triggers that call data changing statements are invalid.
    > 
    > We can't do that for compatibility reasons, but it would allow us to say
    > that modifying a row in a before trigger that is also a row selected in
    > the outer statement is an error for this update case.  It'd presumably be
    > an error for a normal delete as well, although I think it might be
    > relaxable for cascaded deletes because the spec seems to say that the
    > before triggers for deletions caused by the cascade are actually run after
    > the removals. I'm not sure whether we could easily differentiate this case
    > from any other cases where the row was modified twice either yet.
    > 
    > ---
    > * "If TR is a BEFORE trigger and if, before the completion of the
    > execution of an <SQL procedure statement> simply contained in TSS, an
    > attempt is made to execute an SQL-data change statement or an SQL-invoked
    > routine that possibly modifies SQL-data, then an exception condition is
    > raised:  prohibited statement encountered during trigger execution."
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 4: Have you searched our list archives?
    > 
    >                http://archives.postgresql.org
    > 
    
    -- 
      Bruce Momjian   http://candle.pha.pa.us
      EnterpriseDB    http://www.enterprisedb.com
    
      + If your life is a hard drive, Christ can be your backup. +