On Thu, 11 Aug 2005, Stephan Szabo wrote: > On Thu, 11 Aug 2005, Allan Wang wrote: > > > I'm running a fairly recent CVS head server, but I think this bug > > applies in all versions. > > It doesn't happen for me in 7.4.2 with the example below, although my > couple month old CVS server and an 8.0.x server do error. My first guess > is that there's some side effect of one of the trigger timing changes > that's causing this, but I haven't looked yet.
I think I have a theory for why the timing change would have affected this. The check in trigger.c to decide if the key has changed only abort the check if the key has changed and the row was not made by this transaction. In the deferred case, you could have an insert / update combination where the insert trigger sees the row it's checking is no longer live and doesn't do any checks, and then if the update only checked changed keys some might be missed entirely. I think for the case given in the example if the constraint were deferred it would work because the second update would have made the first update's check no longer live either, and so only the final state is checked. In the immediate case where the checks were deferred to end of outer statement (7.4 and below), I believe the same applies. By the time the first check caused by the first update is run, the second update has happened, so the first check doesn't actually do anything. In the immediate case where the checks run directly on the update run by the constraint (8.0 and above), the check happens before the second update so the first check (with the half changed key) runs on both keys which fails. I don't think we can simply change the immediate case behavior to unconditionally check that the key has changed because that might break for an update inside an after insert trigger that updates the same row (if the update happened before the insert's check. Is there a way to detect this case that wouldn't also catch two updates caused by separate on updates for an action? ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
