From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Michael Fuhr <mike(at)fuhr(dot)org> |
Cc: | CSN <cool_screen_name90001(at)yahoo(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Delete / F/K error |
Date: | 2005-12-31 00:51:14 |
Message-ID: | 20051230164242.T2454@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Reid Thompson | 2005-12-31 04:16:16 | Re: FW: FW: Re[2]: new beginner to postgresql. Looking |
Previous Message | Michael Fuhr | 2005-12-31 00:26:37 | Re: Delete / F/K error |