Re: Referential Integrity and SHARE locks

On Sun, 2007-02-04 at 09:38 +0000, Simon Riggs wrote:
> > > 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.

...

> It occurs to me that if we had visibility in unique indexes, this would
> allow the index rows to be separately lockable to the main row. That's
> exactly what we need here.

I've implemented a work-around using this principle, utilising RULEs and
a duplicated PK column-only table. This still allows FK checks to work
correctly, yet doesn't require the backend hack Csaba mentioned.

My feeling is that more work in this area is required, even if we can't
yet agree a TODO item.

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