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

Re: Foreign key order evaluation

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Randy Yates <yates(at)ieee(dot)org>
Cc: Michael Fuhr <mike(at)fuhr(dot)org>, pgsql-general(at)postgresql(dot)org
Subject: Re: Foreign key order evaluation
Date: 2004-09-28 20:02:58
Message-ID: 20040928130001.F26429@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
On Tue, 28 Sep 2004, Randy Yates wrote:

> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>
> > On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote:
> >> Randy Yates <yates(at)ieee(dot)org> writes:
> >> >
> >> > I'm confused. Where is the lock? Is it on the 1 record in the model table?
> >
> > Yes.
> >
> >> > If so, why is that record locked? Is it possible in Postgresql to update
> >> > the primary key of a record?
> >
> > When you insert a row that has a foreign key reference, PostgreSQL
> > does a SELECT FOR UPDATE on the referenced row in the foreign table;
> > the lock prevents other transactions from changing the referenced
> > row before this transaction completes.  Unfortunately it also
> > prevents other transactions from acquiring a lock on the same row,
> > so those transactions will block until the transaction holding the
> > lock completes.
>
> Well, yeah - sure it does. Given that the locking mechanism's
> granularity is record-level, it MUST if it is to guarantee referential
> integrity.

But it doesn't need to prevent other transactions that want to just see if
the row is there from continuing (as opposed to ones that want to actually
modify that row). We just simply don't have that lock currently.

In response to

Responses

pgsql-general by date

Next:From: Greg StarkDate: 2004-09-28 20:52:31
Subject: Re: Null comparisons (was Re: checksum)
Previous:From: Geisler, JimDate: 2004-09-28 19:47:22
Subject: PostgreSQL Installation Question

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