Re: foreign key locks, 2nd attempt

On Thu, Feb 23, 2012 at 02:08:28PM +0100, Jeroen Vermeulen wrote:
> On 2012-02-23 10:18, Simon Riggs wrote:
>
>> However, review of such a large patch should not be simply pass or
>> fail. We should be looking back at the original problem and ask
>> ourselves whether some subset of the patch could solve a useful subset
>> of the problem. For me, that seems quite likely and this is very
>> definitely an important patch.
>>
>> Even if we can't solve some part of the problem we can at least commit
>> some useful parts of infrastructure to allow later work to happen more
>> smoothly and quickly.
>>
>> So please let's not focus on the 100%, lets focus on 80/20.
>
> The suggested immutable-column constraint was meant as a potential
> "80/20 workaround." Definitely not a full solution, helpful to some,
> probably easier to do. I don't know if an immutable key would actually
> be enough to elide foreign-key locks though.

That alone would not simplify the patch much. INSERT/UPDATE/DELETE on the
foreign side would still need to take some kind of tuple lock on the primary
side to prevent primary-side DELETE. You then still face the complicated case
of a tuple that's both locked and updated (non-key/immutable columns only).
Updates that change keys are relatively straightforward, following what we
already do today. It's the non-key updates that complicate things.

If you had both an immutable column constraint and a never-deleted table
constraint, that combination would be sufficient to simplify the picture.
(Directly or indirectly, it would not actually be a never-deleted constraint
so much as a "you must take AccessExclusiveLock to DELETE" constraint.)
Foreign-side DML would then take an AccessShareLock on the parent table with
no tuple lock at all.

By then, though, that change would share little or no code with the current
patch. It may have its own value, but it's not a means for carving a subset
from the current patch.

Thanks,
nm