Re: Referential Integrity and SHARE locks

On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
> >> , and it doesn't scale to
> >> more than two holders, and I don't think it works for combinations of
> >> share and exclusive lock. Also, what happened to the third type of lock?
>
> > Well, we just need to record the maximum two lock holders (given the
> > semantics described). The third lock type is both locks at once.
>
> You're not going to support shared locks? That will be a big step
> backwards ...

I did say that Shared locks were supported also. The lack of ordering of
multitransactions is a hole in my suggestion, so I need to reconsider.

> > Anyway, implementation aside, I wanted to agree the overall TODO, so we
> > can think through the best way over a long period, if you agree in
> > general.
>
> 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.

For me, PKs are intimately tied to the rows they uniquely identify; we
should be mentally linking them and seeing them as a replacement for
Oids, which we still see as intimately linked at low level. We'll be
missing many optimizations if we don't, we've discussed others this week
already.

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.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com