Patrick Earl <patearl@patearl.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