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

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?