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? ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match