Re: improving foreign key locks

On Nov29, 2010, at 22:33 , Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Excerpts from Alvaro Herrera's message of lun nov 29 18:00:55 -0300 2010:
>>> Additionally, we'd have to expend some more cycles at the parse analysis
>>> phase (of the "FOR SHARE OF x.col1, x.col2" query) to verify that those
>>> columns belong into some non-partial unique index.
>
>> Hmm, actually there's already a relcache lookup when we execute whatever
>> action the FK needs to execute, so maybe we don't need to do any of
>> this.
>
> Checking for existence of a unique index at parse analysis time is quite
> horrid anyway, because it means the validity of the query can change
> from parse time to execution time. We got stuck with some of that in
> relation to GROUP BY dependencies, but I don't want to buy into it
> anywhere that we're not forced to by the letter of the SQL spec.

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.

So I figured we could do the check while executing the query, probably when we're about to lock the first tuple. The result could be cached then, so we'd only incur the overhead once. I'd also expect the overhead to be pretty small compared to the subsequent update of the tuple's xmax and the IO that this entails.

best regards,
Florian Pflug