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

Re: Foreign key quandries

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

> > 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 ...


In response to

Responses

pgsql-hackers by date

Next:From: Rod TaylorDate: 2003-03-01 14:39:20
Subject: Re: Foreign key quandries
Previous:From: Rod TaylorDate: 2003-03-01 07:05:51
Subject: Re: Foreign key quandries

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