Re: improving foreign key locks

On Dec1, 2010, at 17:17 , Tom Lane wrote:
> Florian Pflug <fgp@phlo.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
beprevented 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
necessarilyone 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
onsuch 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_KEYset. Dropping an index requires some care, since it retroactively reduces the set of locked
columnsfor 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.
Assumingone can find a suitable definition of "recently" in this context, of course. Alvaro's initial proposal suffers
fromessentially the same problem I believe. It is somewhat mitigated though by the fact that he expects KEY locks to
onlybe used by FOREIGN KEYS. Thus, if a unique index is dropped it either wasn't used by any FK constraint, in which
casethe retroactive reduction of lock strength doesn't matter, or the FK constraint must haven been dropped first, in
whichcase the importance of enforcing the FK is somewhat diminished. 

best regards,
Florian Pflug