From: Tom Lane <tgl@sss.pgh.pa.us>
To: James Robinson <jlrobins@socialserve.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
Date: Fri, 06 Oct 2006 12:25:23 -0400
Message-ID: <21634.1160151923@sss.pgh.pa.us> (raw)
In-Reply-To: <B15DF97D-188F-4FD1-99B6-BCEB7E0C3E99@socialserve.com>
References: <B15DF97D-188F-4FD1-99B6-BCEB7E0C3E99@socialserve.com>
James Robinson <jlrobins@socialserve.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, jlrobins@socialserve.com
Subject: Re: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
In-Reply-To: <21634.1160151923@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox