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?
