Re: Foreign key order evaluation

On Tue, 28 Sep 2004, Randy Yates wrote:

> Michael Fuhr <mike@fuhr.org> writes:
>
> > On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote:
> >> Randy Yates <yates@ieee.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.