Re: Referential Integrity and SHARE locks

Simon Riggs wrote:
> On Sat, 2007-02-03 at 09:43 -0800, Stephan Szabo wrote:
>> On Sat, 3 Feb 2007, Simon Riggs wrote:
>>
>>> On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
>>>> No, I don't. I think knowledge of which columns are in a PK is quite a
>>>> few levels away from the semantics of row locking. To point out just
>>>> one problem, what happens when you add or drop a PK? Or drop and
>>>> replace with a different column set? Yes, I know dropping one requires
>>>> exclusive lock on the table, but the transaction doing it could hold row
>>>> locks within the table, and now it's very unclear what they mean.
>>> There are issues, yes. Dropping PKs is a very irregular occurrence nor
>>> is it likely to be part of a complex transaction. It wouldn't bother me
>>> to say that if a transaction already holds a RowExclusiveLock or a
>>> RowShareLock it cannot upgrade to an AccessExclusiveLock.
>> The lock check seems like a strange constraint, given that it's not
>> necessarily going to be anything that conflicts with the row locks. I'm
>> not sure there'd be a better idea given this sort of scheme, but it still
>> seems strange.
>>
>>> The TODO I was requesting you consider was this:
>>>
>>> "Develop non-conflicting locking scheme to allow RI checks to co-exist
>>> peacefully with non-PK UPDATEs on the referenced table".
>>>
>>> That is, IMHO, a general statement of an important unresolved issue with
>>> our Referential Integrity implementation. That is in no way intended as
>>> any form of negative commentary on the excellent detailed work that has
>>> got us so far already.
>> Well, if we really want to solve that completely then we really need
>> column locking, or at least locking at the level of arbitrary (possibly
>> overlapping) unique constraints, not just the PK because foreign keys
>> don't necessarily reference the primary key. But the PK case is certainly
>> the most common and it'd certainly be nice to cover that case.
>
> IMHO generic column level locking would hardly ever be used. Locking for
> RI seems to be 99% of the use case, which means we'd be OK if we found a
> way of only locking an arbitary number of unique col groups. By
> definition, each of these column groups is covered by a unique index.

Not saying this'll gain us anything but...

It has ocurred to me that the lock could be reduced in another way. If
we had an "immutable" constraint that could be applied to pkey-columns
then we wouldn't have to worry about updates at all. If a pkey value was
there before an update, it would be there after too. The only thing
you'd need to prevent would be deletes.

--
Richard Huxton
Archonet Ltd