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.
