pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
3+ messages / 2 participants
[nested] [flat]
* SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
@ 2006-10-06 15:31 James Robinson <jlrobins@socialserve.com>
0 siblings, 1 reply; 3+ messages in thread
From: James Robinson @ 2006-10-06 15:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
@ 2006-10-06 16:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: James Robinson <jlrobins@socialserve.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2006-10-06 16:25 UTC (permalink / raw)
To: James Robinson <jlrobins@socialserve.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join
@ 2006-10-06 16:30 James Robinson <jlrobins@socialserve.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: James Robinson @ 2006-10-06 16:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-10-06 16:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-06 15:31 SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join James Robinson <jlrobins@socialserve.com>
2006-10-06 16:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-06 16:30 ` James Robinson <jlrobins@socialserve.com>
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