In the previous article, I explained the problem with foreign key checks checks obtaining too strong of a lock, and promised that we would be attempting to fix it.
Here is my proposal:
That's the gist of it. The end effect is that you are allowed to UPDATE a tuple that's being used in a foreign key check, as long as you don't change any indexed columns.
This idea was suggested by Simon Riggs in the pgsql-hackers thread referenced in my previous article, and further debugged and improved by the developers in the ensuing discussion to a reasonably workable level — though it remains ticklish.
The interesting implementation details are:
If you're curious about also carrying forward COMBO_CID: at first I thought this wasn't necessary, because the only transaction that might care about those bits is the one creating the tuple, thus no transaction can do the necessary UPDATE. However, if a transaction creates the tuple, then modifies it in a subtransaction, then aborts the subtransaction, then key-locks the tuple, and finally updates it again, the last version of the tuple needs to have the correct CommandId information. This is fairly corner case and I would be surprised to see it happen in reality. But this is no excuse for not supporting the case.
(Offhand, I don't see any other fields that need to be carried forward, but I'm open to the possibility that I'm missing some.)
Note that the lock would be even more granular if instead of checking for an attribute of any index, we were to check for the particular UNIQUE index that implements the foreign key being verified. We choose not to do that for now, because it brings excessive modularisation breakage and possibly extra locking considerations.