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
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 |