Re: Foreign key quandries

On 1 Mar 2003, Rod Taylor wrote:

> On Sat, 2003-03-01 at 02:38, Stephan Szabo wrote:
> > On 1 Mar 2003, Rod Taylor wrote:
> >
> > > Gah, hit wrong key combination and the email sent early.
> > >
> > > Anyway, after that 'sleep' mess at the bottom is:
> > > T1 or T2: Sleeping too long -- lets run deadlock detection code
> > > T1 or T2: Kill off random participant of deadlock.
> > >
> > > The other participant is then allowed to continue their work.
> > >
> > > > Isn't the differentiation going to happen automatically?
> >
> > The problem is that in case 2, both tuples 2 and 3 are already removed
> > before either foreign key check runs, so when T1 adds the value 3
> > row and checks the pk table it will find that its pk row has been
> > modified. If the ordering went, delete 2 - check 2, delete 3 - check
> > 3, this wouldn't be a problem, but then that'd fail in a
> > spec-non-compliant way if row 2 refered to row 3.
>
> The foreign key cascade is explicitly deferred to the end of the
> statement via the trigger queue, but there is no reason that the foreign
> key code can't run immediately for each tuple removed.

The problem happens when you have two rows in a table that refer to each
other with a foreign key to the same table. If both are deleted, it must
succeed, but it won't if you do the check in between the deletes unless
I'm missing something. It's effectively the same problem as we currently
have with the unique constraint (premature checking of the constraint).
AFAICS, you need to defer to end of statement to get the correct semantics
out of the checks, or you need to have a state where the rows are sort of
pseudo-deleted/updated which could be better.