From: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Confusing results with lateral references |
Date: | 2015-12-03 12:26:02 |
Message-ID: | CAFjFpRe9cok4rnxuOUzpfUfaAqBZ_5U0BZ5RXSKPdXOp+F5ELg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
There's another seemingly wrong result, not with lateral, but with FOR
UPDATE.
postgres=# select * from t1;
val | val2
-----+------
1 | 1
(1 row)
postgres=# select * from t2;
val | val2
-----+------
1 | 1
2 | 2
1 | 1
(3 rows)
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?
On Thu, Dec 3, 2015 at 3:49 PM, Ashutosh Bapat <
ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> Hi,
>
> I am seeing different results with two queries which AFAIU have same
> semantics and hence are expected to give same results.
>
> postgres=# \d t1
> Table "public.t1"
> Column | Type | Modifiers
> --------+---------+-----------
> val | integer |
> val2 | integer |
>
> postgres=# \d t2
> Table "public.t2"
> Column | Type | Modifiers
> --------+---------+-----------
> val | integer |
> val2 | integer |
>
> There's no data in the table to start with.
>
> postgres=# insert into t1 values (1, 1);
> postgres=# insert into t2 values (1, 1), (2, 2);
>
> Session 1
> postgres=# begin;
> BEGIN
> postgres=# update t1 set val = 2 where val2 = 1;
> UPDATE 1
>
> Session 2
> postgres=# select * from t1, (select distinct val, val2 from t2) t2 where
> t1.val = t2.val for update of t1;
>
> query waits here because of FOR UPDATE clause
>
> Session 1
> postgres=# commit;
> COMMIT
>
> Session 2 gives no rows
> postgres=# select * from t1, (select distinct val, val2 from t2) t2 where
> t1.val = t2.val for update of t1;
> val | val2 | val | val2
> -----+------+-----+------
> (0 rows)
>
>
> Reset values of t1
> postgres=# update t1 set val = 1 where val2 = 1;
> UPDATE 1
>
> Session 1
> postgres=# begin;
> BEGIN
> postgres=# update t1 set val = 2 where val2 = 1;
> UPDATE 1
>
> Session 2
> postgres=# select * from t1, lateral (select distinct val, val2 from t2
> where t2.val = t1.val) t2 for update of t1;
>
> query waits here
>
> Session 1
> postgres=# commit;
> COMMIT
>
> Session 2 gives results of the query
> postgres=# select * from t1, lateral (select distinct val, val2 from t2
> where t2.val = t1.val) t2 for update of t1;
> val | val2 | val | val2
> -----+------+-----+------
> 2 | 1 | 1 | 1
> (1 row)
>
> AFAIU, both the queries
>
> select * from t1, (select distinct val, val2 from t2) t2 where t1.val =
> t2.val for update of t1;
>
> AND
>
> select * from t1, lateral (select distinct val, val2 from t2 where t2.val
> = t1.val) t2 for update of t1;
>
> have same semantic and should give same results.
>
> Is seeing different results expected behaviour?
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2015-12-03 12:39:30 | Re: Logical replication and multimaster |
Previous Message | Simon Riggs | 2015-12-03 12:22:40 | Re: Logical replication and multimaster |