Re: Select For Update and Left Outer Join

On Sun, May 1, 2011 at 4:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Patrick Earl <patearl(at)patearl(dot)net> writes:
>> The query to get all the pets is as follows:
>
>> select * from Pet
>> left join Dog on Dog.Id = Pet.Id
>> left join Cat on Cat.Id = Pet.Id
>
>> Now suppose you want to lock to ensure that your Cat is not updated
>> concurrently.  You add FOR UPDATE, but then PostgreSQL gets upset and
>> complains that locking on the nullable side of an outer join is not
>> allowed.
>
> Quite.  What would it mean to lock the absence of a row?
>
>> From our data model, we know that for every single Pet, there can
>> never be a Dog or Cat that spontaneously appears, so locking in this
>> case is totally safe.
>
> You might know that, but you didn't explain how you know that or how
> the database could be expected to know it.
>
>                        regards, tom lane
>

I would argue that SELECT FOR UPDATE never locks on the absence of a
row. For example, if I do:
SELECT * FROM Table WHERE Column = 10
The existing rows are locked, but somebody could come along and add
another unlocked row with Column = 10.

Whether I'm specifying a secondary set of records (via a criteria
involving a join) or a primary set of records (via a regular where
criteria), FOR UPDATE always allows new rows to appear.

The fact that new rows are not locked is common and expected. Whether
they appear in the result set due to the join or due to the where
clause seems unimportant to me.

Patrick Earl