Re: Select For Update and Left Outer Join

Patrick Earl <patearl(at)patearl(dot)net> writes:
> On Sun, May 1, 2011 at 9:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Addition of new rows certainly isn't supposed to be prevented by a
>> SELECT FOR UPDATE, but that's not the problem here. What I *do* expect
>> a SELECT FOR UPDATE to promise is that the rows it did return can't
>> change or be deleted by someone else for the life of my transaction.

> While I don't have access to the SQL standard to check if the
> semantics are documented, the semantics don't seem clear cut.

The specific syntax of SELECT FOR UPDATE isn't in the standard at all
--- the standard considers FOR UPDATE to be a clause you can attach to
DECLARE CURSOR, not a bare SELECT, and also the items that can be
mentioned in FOR UPDATE OF are individual column names not table names.
But ignoring that little detail, as best I can tell the standard only
allows FOR UPDATE to be applied to columns for which the cursor output
is guaranteed to be one-to-one with elements of the underlying table,
ie you could say UPDATE tab SET col = <value> WHERE CURRENT OF <cursor>
and expect that a single well-defined table cell would get updated.
This is certainly not the case for columns in the nullable side of an
outer join, where there might be no existing cell that could be updated.
The question of whether the cell is locked against concurrent updates
isn't something that the spec addresses, so far as I've ever seen; but
it is perfectly clear that there should be something there that could be
updated. So your proposal doesn't work from that standpoint either.

regards, tom lane