Skip site navigation (1) Skip section navigation (2)

Re: Foreign key quandries

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Foreign key quandries
Date: 2003-03-01 14:39:20
Message-ID: 1046529559.26763.124.camel@jester (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

For the defer check to commit case, have the code add the element to the
trigger queue by making a fake trigger wrapper -- that isn't really a
trigger (simplest method probably) and it should and will deadlock.

> > > In case 2:
> > >
> > > T1: create fk tuple (uncommitted) -> value 2
> *   T1: scan through pk table, no problems
> > > T2: delete pk tuple value 2
> *   T2: delete pk tuple value 3
> > > T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> > > T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> > > T2:	scan through fk table, find uncommitted tuple value 2 ... sleep
> > > T1: create fk tuple (uncommitted) -> value 3
> *   T1: scan through pk table, find modified tuple value 3 ...
-- 
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

In response to

Responses

pgsql-hackers by date

Next:From: Itai ZukermanDate: 2003-03-01 15:11:35
Subject: GiST: Bug In gistdoinsert() ?
Previous:From: Stephan SzaboDate: 2003-03-01 07:38:23
Subject: Re: Foreign key quandries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group