Re: Partitioning/inherited tables vs FKs - Mailing list pgsql-hackers

From Marko Tiikkaja
Subject Re: Partitioning/inherited tables vs FKs
Date
Msg-id 4BE9578E.4060801@cs.helsinki.fi
Whole thread Raw
In response to Re: Partitioning/inherited tables vs FKs  (Nicolas Barbier <nicolas.barbier@gmail.com>)
Responses Re: Partitioning/inherited tables vs FKs
List pgsql-hackers
On 5/11/10 4:07 PM +0300, Nicolas Barbier wrote:
> 2010/5/11 Marko Tiikkaja<marko.tiikkaja@cs.helsinki.fi>:
>
>> This is getting way off topic, but:
>>
>> On 5/11/10 3:55 PM +0300, Nicolas Barbier wrote:
>>>
>>> T2>    SELECT i FROM a WHERE i = 1 FOR SHARE; -- Lock a with i = 1 FOR
>>> SHARE.
>>>   i
>>> ---
>>>   1
>>> (1 Zeile)
>>>
>>> T2>    SELECT a_id FROM b WHERE a_id = 1; -- Check whether it's got
>>> anything pointing to it.
>>>   a_id
>>> ------
>>> (0 Zeilen)
>>>
>>> T2>    DELETE FROM a WHERE i = 1; -- Nope, so delete a with i = 1 (this
>>> blocks, because T1 is still holding the lock).
>>
>> Obviously you wouldn't delete anything with a SHARE lock.
>
> So where would you put a SELECT ... FOR SHARE to fix the problem? (Per
> "Will SELECT ... FOR SHARE not help?".) I agree that my second FOR
> SHARE doesn't really make a lot of sense, but that doesn't disprove
> the fact that the first FOR SHARE fails to ensure consistency.

I took the "SELECT ... FOR SHARE" suggestion in a more general way, 
suggesting the use of row-level locks.  T2 should be holding an 
exclusive row-level lock (SELECT ... FOR UPDATE) when checking for 
references.


Regards,
Marko Tiikkaja


pgsql-hackers by date:

Previous
From: Nicolas Barbier
Date:
Subject: Re: Partitioning/inherited tables vs FKs
Next
From: Marko Tiikkaja
Date:
Subject: Re: Partitioning/inherited tables vs FKs