Re: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join

Given something like:

create table foo (id int primary key not null);
create table bar (id int primary key not null, a_id int references
foo(id));
select a.id, b.id from foo a left outer join bar b on (b.a_id =
a.id) for update;

PG 8.1.4 balks, saying:

SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an
outer join

Is this behavior spec-mandated, or could only the rows in B which are
not-null be locked?

----
James Robinson
Socialserve.com

James Robinson <jlrobins(at)socialserve(dot)com> writes:
> select a.id, b.id from foo a left outer join bar b on (b.a_id =
> a.id) for update;
> SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an
> outer join

> Is this behavior spec-mandated, or could only the rows in B which are
> not-null be locked?

I don't believe that the spec expects FOR UPDATE to work on outer joins.

The problem with it is what does it mean to lock the result of a select
from an outer join? To my mind, a lock on a select result means that
you've guaranteed that no one else can change the rows you selected.
In an outer join it's impossible to guarantee that --- someone could
insert a B row that matches a formerly unmatched A row. If you now
re-did the SELECT you would get a different result, ie, your
null-extended A row would be replaced by a normal row, even though you
had lock on that A row. (This does not speak to the question of new
rows showing up in the second SELECT --- that's always possible. The
point is that a row you got the first time is now different despite
being "locked".)

So I tend to feel that if you think you need this, you need to rethink
your data model.

Note that you can select "FOR UPDATE OF a" in this situation, it's just
the B side that is problematic.

regards, tom lane

Oh that's sweet and all I needed anyway thanks.

On Oct 6, 2006, at 12:25 PM, Tom Lane wrote:

> Note that you can select "FOR UPDATE OF a" in this situation, it's
> just
> the B side that is problematic.

----
James Robinson
Socialserve.com