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

Re: Foreign key order evaluation

From: Randy Yates <yates(at)ieee(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Foreign key order evaluation
Date: 2004-09-28 13:30:08
Message-ID: pt46h673.fsf@ieee.org (view raw or flat)
Thread:
Lists: pgsql-general
Randy Yates <yates(at)ieee(dot)org> writes:

> Mike,
>
> I'm confused. Where is the lock? Is it on the 1 record in the model table?
> If so, why is that record locked? Is it possible in Postgresql to update
> the primary key of a record? 

Let me also ask why this is a problem. It may be a lock situation but
it isn't a DEADlock situation. I.e., the second transaction will just
have to wait until the first completes, and the first should complete
in milliseconds on a reasonable computer. Right?

Or am I completely missing the boat?

--Randy


>
> --RY
>
> mike(at)fuhr(dot)org (Michael Fuhr) writes:
>
>> On Mon, Sep 27, 2004 at 03:19:47PM -0400, Shawn Chisholm wrote:
>>> 
>>> Hi, I am trying to deal with a deadlock situation caused by foreign
>>> key references on insert and I was wondering if anyone knows what
>>> order the foreign keys are locked (or evaluated) in for a particular
>>> table?  Deferring the locks is unfortunately not a good option for me...
>>
>> What do you mean by "what order the foreign keys are locked"?  Can
>> you give us an example of what you're doing and what problem you're
>> trying to solve?  As I mentioned in reply to your earlier message,
>> foreign key locking and the potential for deadlock were recently
>> brought up in pgsql-general:
>>
>> http://archives.postgresql.org/pgsql-general/2004-09/msg00405.php
>> http://archives.postgresql.org/pgsql-general/2004-09/msg00442.php
>>
>> My followup to that thread (the second link above) mentions somebody
>> else's suggestion for a shared lock on the foreign key, but as far
>> as I can tell, no such solution has been implemented as of 8.0.0beta3.
>>
>> -- 
>> Michael Fuhr
>> http://www.fuhr.org/~mfuhr/
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: Have you searched our list archives?
>>
>>                http://archives.postgresql.org
>>
>
> -- 
> %  Randy Yates                  % "And all that I can do
> %% Fuquay-Varina, NC            %  is say I'm sorry, 
> %%% 919-577-9882                %  that's the way it goes..."
> %%%% <yates(at)ieee(dot)org>           % Getting To The Point', *Balance of Power*, ELO
> http://home.earthlink.net/~yatescr

-- 
%  Randy Yates                  % "Rollin' and riding and slippin' and
%% Fuquay-Varina, NC            %  sliding, it's magic."
%%% 919-577-9882                %  
%%%% <yates(at)ieee(dot)org>           % 'Living' Thing', *A New World Record*, ELO
http://home.earthlink.net/~yatescr

In response to

Responses

pgsql-general by date

Next:From: David ParkerDate: 2004-09-28 14:02:03
Subject: To OID or not to OID
Previous:From: Randy YatesDate: 2004-09-28 13:26:41
Subject: Re: Foreign key order evaluation

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