On Fri, Dec 4, 2015 at 10:58 AM, Amit Langote <langote_amit...@lab.ntt.co.jp > wrote:
> On 2015/12/03 21:26, Ashutosh Bapat wrote: > > Session 1 > > postgres=# begin; > > BEGIN > > postgres=# update t1 set val = 2 where val2 = 1; > > UPDATE 1 > > > > Session 2 > > postgres=# select * from t1 left join t2 on (t1.val = t2.val) for update > of > > t1; > > > > query waits > > > > Session 1 > > postgres=# commit; > > COMMIT > > > > > > Session 2 query returns two rows > > select * from t1 left join t2 on (t1.val = t2.val) for update of t1; > > val | val2 | val | val2 > > -----+------+-----+------ > > 2 | 1 | | > > 2 | 1 | | > > (2 rows) > > > > It's confusing to see two rows from left join result when the table > really > > has only a single row. Is this behaviour expected? > > Maybe it is. Because the other table still has two (1, 1) rows, LockRows's > subplan would still produce two rows in result, no? > > Documentation at http://www.postgresql.org/docs/9.4/static/queries-table-expressions.html says (T1) LEFT OUTER JOIN (T2) First, an inner join is performed. Then, *for each row in T1* that does not satisfy the join condition with any row in T2, *a joined row is added* with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1. So there should be only one row for each row of outer table that didn't join with the inner table. IOW a join with no joining rows should have same number of rows as outer table. > Thanks, > Amit > > > -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
