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

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. +