Re: Foreign key order evaluation

Michael Fuhr wrote:
> 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.

There are some proposal to have another kind of lock in order to avoid the
above. I hope soon.

Regards
Gaetano Mendola