Re: Select For Update and Left Outer Join

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