Re: outer joins and for update

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> Do we really need to prevent inserts from happening under a SELECT FOR
> UPDATE? ISTM that's trying to apply serializable concurrency to SELECT
> FOR UPDATE even if it's running in a read committed transaction. In the
> single table case we don't prevent someone from inserting a value...

You're missing the point entirely, Jim. In the first place, SELECT FOR
UPDATE has little or nothing to do with serializable mode: it's
guaranteed to lock and return the latest committed version of the row.
In the second case, inserting additional tuples does not invalidate your
lock on the tuples you selected to begin with. SELECT FOR UPDATE
doesn't try to guarantee that if you were to select again with the same
WHERE condition, there might not be more rows matching the same
condition. It does try to guarantee that the rows you selected before
are still there and unchanged.

In the case being discussed here, you're trying to lock rows of an
outer-join. IMHO, if that means anything at all, it means that if
you read those rows again they will still look the same. Having the
righthand side go from NULL to not-NULL does not qualify as "looking the
same" in my book.

Perhaps this could be clarified if someone has an actual use case of
wanting to SELECT FOR UPDATE from an outer join, and can explain what
semantics they think they need for that.

regards, tom lane