On Fri, Dec 4, 2015 at 10:23 AM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Ashutosh Bapat <ashutosh.ba...@enterprisedb.com> writes: >> I am seeing different results with two queries which AFAIU have same >> semantics and hence are expected to give same results. > >> postgres=# select * from t1, (select distinct val, val2 from t2) t2ss where >> t1.val = t2ss.val for update of t1; > >> postgres=# select * from t1, lateral (select distinct val, val2 from t2 >> where t2.val = t1.val) t2ss for update of t1; > > (I renamed your inline sub-selects to avoid confusion between them and the > table t2.) > > I'm skeptical that those should be claimed to have identical semantics. > > In the first example, after we've found the join row (1,1,1,1), we block > to see if the pending update on t1 will commit. After it does, we recheck > the join condition using the updated row from t1 (and the original row > from t2ss). The condition fails, so the updated row is not output.
Check. > The same thing happens in the second example, ie, we consider the updated > row from t1 and the non-updated row from t2ss (NOT t2). There are no join > conditions to recheck (in the outer query level), so the row passes, and > we output it. What's surprising is that t2.val = t1.val isn't rechecked here. I think that's not really possible, because of the DISTINCT operation, which prevents us from identifying a single row from t2 that accounts for the subquery's output row. Not sure whether it would work without the DISTINCT. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
