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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: James Robinson <jlrobins(at)socialserve(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
Date: 2006-10-06 16:25:23
Message-ID: 21634.1160151923@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message James Robinson 2006-10-06 16:30:08 Re: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
Previous Message James Robinson 2006-10-06 15:31:47 SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join