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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-hackers by date

  From Date Subject
Next Message Greg Smith 2009-10-28 16:10:04 Re: Parsing config files in a directory
Previous Message Kevin Grittner 2009-10-28 14:56:00 Re: Parsing config files in a directory