Re: Lock changes with 8.1 - what's the right lock?

> Unless I'm misunderstanding you or a bug was fixed between 7.4.5
> and 7.4.13 (the version I'm running), I'm not convinced that last
> statement is true. EXCLUSIVE conflicts with all lock types except
> ACCESS SHARE; foreign key references prior to 8.1 use SELECT FOR
> UPDATE and in 8.1 they use SELECT FOR SHARE, but in both cases they
> acquire ROW SHARE on the referenced table, which conflicts with
> EXCLUSIVE.

My apologies for being so unclear. I had intended to just indicate that the
problem occurred when we upgraded from 7.4.5 to 8.1, as opposed to this
being fixed specifically in 8.1. I didn't realize this was fixed in a 7.4
release - I thought it was 8.x. The last time I'd checked, it hadn't been
fixed in 7.x.

>> What is now the appropriate lock? It needs to:
>>
>> 1. Prevent others from updating the table
>> 2. Block other jobs that are requesting the same lock (if job 2 does a
>> SELECT and finds nothing, it will try to create the record that job 1 may
>> already have created in its transaction).
>> 3. Not conflict with foreign key reference locks
>
> SHARE ROW EXCLUSIVE is the weakest lock that meets these requirements.
> It conflicts with itself (#2) and with ROW EXCLUSIVE, which UPDATE,
> DELETE, and INSERT acquire (#1), but doesn't conflict with ROW SHARE,
> which is what SELECT FOR UPDATE/SHARE acquire (#3).

Thanks for the confirmation. Is there any stronger lock that would not
block SELECT foreign key references? I didn't find any documentation on
what type of lock is grabbed by a when a foreign key is referenced during
SELECT (or other).

Wes