Re: Referential Integrity and SHARE locks

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.