Re: improving foreign key locks

On Dec1, 2010, at 17:17 , Tom Lane wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> The validity wouldn't change, only the kind of lock taken. If all columns to be locked are part of some unique index, we'd record that fact in the locked tuple's infomask, and thus know that only a certain subset of columns are to be prevented from being updated.
>
> There's not enough space in the infomask to record which columns (or
> which unique index) are involved. And if you're talking about data that
> could remain on disk long after the unique index is gone, that's not
> going to be good enough.

We'd distinguish two cases
A) The set of locked columns is a subset of the set of columns mentioned in
*any* unique index. (In other words, for every locked column there is a
unique index which includes that column, though not necessarily one index
which includes them all)
B) The set of locked columns does not satisfy (A)

We'd mark case (A) by a bit in the infomask (say, HEAP_XMAX_SHARED_LOCK_KEYONLY) when SHARE locking the row. An UPDATE on such a SHARE locked row would be allowed despite the lock if it only changed columns not mentioned by any unique index.

Creating indices shouldn't pose a problem, since it would only enlarge the set of locked columns for rows with HEAP_XMAX_SHARED_LOCK_KEY set. Dropping an index requires some care, since it retroactively reduces the set of locked columns for pre-existing HEAP_XMAX_SHARED_LOCK_KEY locks. A possible solution for that might be to disregard HEAP_XMAX_SHARED_LOCK_KEYONLY, thus treating the row as fully share-locked, if a unique index was recently dropped. Assuming one can find a suitable definition of "recently" in this context, of course. Alvaro's initial proposal suffers from essentially the same problem I believe. It is somewhat mitigated though by the fact that he expects KEY locks to only be used by FOREIGN KEYS. Thus, if a unique index is dropped it either wasn't used by any FK constraint, in which case the retroactive reduction of lock strength doesn't matter, or the FK constraint must haven been dropped first, in which case the importance of enforcing the FK is somewhat diminished.

best regards,
Florian Pflug