Skip site navigation (1) Skip section navigation (2)

FOR UPDATE versus outer joins

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: FOR UPDATE versus outer joins
Date: 2009-10-28 15:22:36
Message-ID: 19574.1256743356@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
While I'm fooling with the FOR UPDATE code ...

Currently, you can't apply FOR UPDATE to a relation that's on the inner
side of an outer join, eg

regression=# select * from a left join b using(aa) for update;
ERROR:  SELECT FOR UPDATE/SHARE cannot be applied to the nullable side of an outer join

It would be a trivial code change to make this work by just not locking
any row in b when a null-extended join row is formed.  The argument
against that was that FOR UPDATE is supposed to guarantee that the same
rows can be fetched again, and it's not clear that the same
null-extended row would be formed if the join were repeated.  In
particular, if the a row's join key has been changed by a concurrent
update, we would still return the null-extended row, but there might now
be rows in b that it can join to; which we won't find since we aren't
repeating the whole join but only rechecking particular join pairs.

Do people still find that argument convincing, or would it be better to
remove the restriction and let the code do the best it can?  It seems to
me that allowing FOR UPDATE with outer joins may be more useful than not
allowing it, even given the caveat.

			regards, tom lane

pgsql-hackers by date

Next:From: Greg SmithDate: 2009-10-28 16:10:04
Subject: Re: Parsing config files in a directory
Previous:From: Kevin GrittnerDate: 2009-10-28 14:56:00
Subject: Re: Parsing config files in a directory

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group