Re: Delete / F/K error

On Fri, 30 Dec 2005, Michael Fuhr wrote:

> On Fri, Dec 30, 2005 at 02:38:48PM -0800, CSN wrote:
> > I'm still confused what the problem was.
>
> I think the problem is related to having multiple foreign key
> constraints with ON DELETE SET NULL referencing the same target.
> The triggers that enforce those constraints are fired one at a time
> with a query like "UPDATE ONLY tbl SET col = NULL WHERE col = val".
> Each update changes only one column; the other columns still have
> their old values, so when the update checks those columns' foreign
> key constraints you get an error because the referenced key has
> already been deleted.  Interestingly, this only appears to be a
> problem if the delete takes place in the same (sub)transaction that
> inserted the referencing row.

Hmm, yes, IIRC there's code that overrides the key check for things done
in this transaction right now because there's a potential failure case the
other way (where a row can get in that violates the constraint under
certain sequences).  I think the trigger timing changes exposed some holes
in that.  It's possible that the experimental fk timing patches I'd sent
to -patches a while back might make these cases work.