Обсуждение: BUG #13465: multi update query use CTE, result & plan not equal, BUG?

The following bug has been logged on the website:

Bug reference:      13465
Logged by:          digoal
Email address:      digoal@126.com
PostgreSQL version: Unsupported/Unknown
Operating system:   CentOS 6.x x64
Description:

PostgreSQL 9.5
when i use CTE update t1 two times, on problem : there is diffient results.
another problem : CTE update one table two times, which query exec first,
and how to isolation MVCC? why these result not same?

postgres=# create table t1(id int,info text);
CREATE TABLE
postgres=# create table t2(id int,info text);
CREATE TABLE
postgres=# create table t3(id int,info text);
CREATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# insert into t2 values (2,'test'),(3,'abc');
INSERT 0 2
postgres=# insert into t3 values (1,'abc'),(2,'test');
INSERT 0 2
postgres=# update t1 set id=t2.id from t2 where t1.info=t2.info;
UPDATE 2
postgres=# select ctid,* from t1;
 ctid  | id | info
-------+----+------
 (0,3) |  3 | abc
 (0,4) |  2 | test
(2 rows)

postgres=# update t1 set info=t3.info from t3 where t1.id=t3.id;
UPDATE 1
postgres=# select ctid,* from t1;
 ctid  | id | info
-------+----+------
 (0,3) |  3 | abc
 (0,5) |  2 | test
(2 rows)

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
 id | info
----+------
  1 | abc
  2 | test
(2 rows)

UPDATE 2
postgres=# select ctid,* from t1;
 ctid  | id | info
-------+----+------
 (0,3) |  1 | abc
 (0,4) |  2 | test
(2 rows)

postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
 c1 | c2  | c3 | c4
----+-----+----+-----
  3 | abc |  1 | abc
(1 row)

postgres=# select ctid,* from t1;
 ctid  | id | info
-------+----+------
 (0,3) |  3 | abc
 (0,4) |  1 | abc
(2 rows)

postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
                                      QUERY PLAN

--------------------------------------------------------------------------------------
 Nested Loop  (cost=5471.31..374220205.11 rows=17045913600 width=72)
   CTE t
     ->  Update on t1  (cost=751.71..2735.66 rows=130560 width=48)
           ->  Merge Join  (cost=751.71..2735.66 rows=130560 width=48)
                 Merge Cond: (t1.info = t2_1.info)
                 ->  Sort  (cost=375.85..388.63 rows=5110 width=38)
                       Sort Key: t1.info
                       ->  Seq Scan on t1  (cost=0.00..61.10 rows=5110
width=38)
                 ->  Sort  (cost=375.85..388.63 rows=5110 width=42)
                       Sort Key: t2_1.info
                       ->  Seq Scan on t2 t2_1  (cost=0.00..61.10 rows=5110
width=42)
   CTE t2
     ->  Update on t1 t1_1  (cost=751.71..2735.66 rows=130560 width=48)
           ->  Merge Join  (cost=751.71..2735.66 rows=130560 width=48)
                 Merge Cond: (t1_1.id = t3.id)
                 ->  Sort  (cost=375.85..388.63 rows=5110 width=10)
                       Sort Key: t1_1.id
                       ->  Seq Scan on t1 t1_1  (cost=0.00..61.10 rows=5110
width=10)
                 ->  Sort  (cost=375.85..388.63 rows=5110 width=42)
                       Sort Key: t3.id
                       ->  Seq Scan on t3  (cost=0.00..61.10 rows=5110
width=42)
   ->  CTE Scan on t  (cost=0.00..2611.20 rows=130560 width=36)
   ->  CTE Scan on t2  (cost=0.00..2611.20 rows=130560 width=36)
(23 rows)

postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
                                      QUERY PLAN

--------------------------------------------------------------------------------------
 Update on t1  (cost=3487.36..5471.31 rows=130560 width=48)
   CTE t
     ->  Update on t1 t1_1  (cost=751.71..2735.66 rows=130560 width=48)
           ->  Merge Join  (cost=751.71..2735.66 rows=130560 width=48)
                 Merge Cond: (t1_1.info = t2.info)
                 ->  Sort  (cost=375.85..388.63 rows=5110 width=38)
                       Sort Key: t1_1.info
                       ->  Seq Scan on t1 t1_1  (cost=0.00..61.10 rows=5110
width=38)
                 ->  Sort  (cost=375.85..388.63 rows=5110 width=42)
                       Sort Key: t2.info
                       ->  Seq Scan on t2  (cost=0.00..61.10 rows=5110
width=42)
   ->  Merge Join  (cost=751.71..2735.66 rows=130560 width=48)
         Merge Cond: (t1.id = t3.id)
         ->  Sort  (cost=375.85..388.63 rows=5110 width=10)
               Sort Key: t1.id
               ->  Seq Scan on t1  (cost=0.00..61.10 rows=5110 width=10)
         ->  Sort  (cost=375.85..388.63 rows=5110 width=42)
               Sort Key: t3.id
               ->  Seq Scan on t3  (cost=0.00..61.10 rows=5110 width=42)
(19 rows)



postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2

postgres=# analyze t1;
ANALYZE
postgres=# analyze t2;
ANALYZE
postgres=# analyze t3;
ANALYZE

postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
 c1 |  c2  | c3 |  c4
----+------+----+------
  2 | test |  2 | test
(1 row)

postgres=# select ctid,* from t1;
 ctid  | id | info
-------+----+------
 (0,7) |  2 | test
 (0,8) |  2 | test
(2 rows)



postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info), t2(c3,c4) as (update t1 set
info=t3.info from t3 where t1.id=t3.id returning t1.id,t1.info) select *
from t,t2;
                                    QUERY PLAN

----------------------------------------------------------------------------------
 Nested Loop  (cost=4.21..4.35 rows=4 width=72)
   CTE t
     ->  Update on t1  (cost=0.00..2.10 rows=2 width=20)
           ->  Nested Loop  (cost=0.00..2.10 rows=2 width=20)
                 Join Filter: (t1.info = t2_1.info)
                 ->  Seq Scan on t1  (cost=0.00..1.02 rows=2 width=10)
                 ->  Materialize  (cost=0.00..1.03 rows=2 width=14)
                       ->  Seq Scan on t2 t2_1  (cost=0.00..1.02 rows=2
width=14)
   CTE t2
     ->  Update on t1 t1_1  (cost=0.00..2.10 rows=2 width=20)
           ->  Nested Loop  (cost=0.00..2.10 rows=2 width=20)
                 Join Filter: (t1_1.id = t3.id)
                 ->  Seq Scan on t1 t1_1  (cost=0.00..1.02 rows=2 width=10)
                 ->  Materialize  (cost=0.00..1.03 rows=2 width=14)
                       ->  Seq Scan on t3  (cost=0.00..1.02 rows=2
width=14)
   ->  CTE Scan on t  (cost=0.00..0.04 rows=2 width=36)
   ->  CTE Scan on t2  (cost=0.00..0.04 rows=2 width=36)
(17 rows)



postgres=# truncate t1;
TRUNCATE TABLE
postgres=# insert into t1 values (1,'test'),(2,'abc');
INSERT 0 2
postgres=# analyze t1;
ANALYZE
postgres=# explain with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
                                 QUERY PLAN

-----------------------------------------------------------------------------
 Update on t1  (cost=2.10..4.21 rows=2 width=20)
   CTE t
     ->  Update on t1 t1_1  (cost=0.00..2.10 rows=2 width=20)
           ->  Nested Loop  (cost=0.00..2.10 rows=2 width=20)
                 Join Filter: (t1_1.info = t2.info)
                 ->  Seq Scan on t1 t1_1  (cost=0.00..1.02 rows=2 width=10)
                 ->  Materialize  (cost=0.00..1.03 rows=2 width=14)
                       ->  Seq Scan on t2  (cost=0.00..1.02 rows=2
width=14)
   ->  Nested Loop  (cost=0.00..2.10 rows=2 width=20)
         Join Filter: (t1.id = t3.id)
         ->  Seq Scan on t1  (cost=0.00..1.02 rows=2 width=10)
         ->  Materialize  (cost=0.00..1.03 rows=2 width=14)
               ->  Seq Scan on t3  (cost=0.00..1.02 rows=2 width=14)
(13 rows)

postgres=# with t(c1,c2) as (update t1 set id=t2.id from t2 where
t1.info=t2.info returning t1.id,t1.info) update t1 set info=t3.info from t3
where t1.id=t3.id returning t1.id,t1.info;
 id | info
----+------
  1 | abc
  2 | test
(2 rows)

UPDATE 2
postgres=# select ctid,* from t1;
 ctid  | id | info
-------+----+------
 (0,3) |  1 | abc
 (0,4) |  2 | test
(2 rows)

On Wed, Jun 24, 2015 at 1:58 AM, <digoal@126.com> wrote:

> The following bug has been logged on the website:
>

=E2=80=8BNot a bug...=E2=80=8B


PostgreSQL 9.5
> when i use CTE update t1 two times, on problem : there is diffient result=
s.
> another problem : CTE update one table two times, which query exec first,
> and how to isolation MVCC? why these result not same?
>

=E2=80=8BThe update of t1 outside of the CTE cannot see any of the changes =
made
within the CTE - which is why a RETURNING clause is required to pass
changes.

The non-CTE action effectively takes precedence=E2=80=8B.

t1(1,'abc')

WITH up AS (
UPDATE t1 SET t1.info =3D 'xyz';
)
SELECT info FROM t1; -- returns abc, not xyz

David J.

On Wed, Jun 24, 2015 at 8:27 PM, =E5=BE=B7=E5=93=A5 <digoal@126.com> wrote:

> But Why, The same SQL has two diff result?
> It's not a BUG?
>

=E2=80=8BTBH, it might be but the pain I experience in trying to decipher y=
our
example is too great for me to know for sure.

If you cannot simplify it even further I would suggest some improvements.

1. choose better data values and less complexity on the where/joins
2. avoid having CTE names that match up with your table names
3. minimize the table count altogether (i.e, 1) and simply use constants in
your where/joins

Note that #2 above may be why you are confused....

with t(c1,c2) as (update t1 set id=3Dt2.id from t2 where t1.info=3Dt2.info
returning t1.id,t1.info), t2(c3,c4) as (update t1 set info=3Dt3.info from t=
3
where t1.id=3Dt3.id returning t1.id,t1.info) select * from t,t2;

You may also want to write more commentary, especially regarding what you
are expecting versus what you are seeing.

David J.

On 6/25/15 2:27 AM, µÂ¸ç wrote:
> But Why, The same SQL has two diff result?
> It's not a BUG?

This test case is still really difficult to follow, but I believe what
you're seeing is documented here:
http://www.postgresql.org/docs/current/static/queries-with.html

"Trying to update the same row twice in a single statement is not
supported. Only one of the modifications takes place, but it is not easy
(and sometimes not possible) to reliably predict which one. This also
applies to deleting a row that was already updated in the same
statement: only the update is performed. Therefore you should generally
avoid trying to modify a single row twice in a single statement. In
particular avoid writing WITH sub-statements that could affect the same
rows changed by the main statement or a sibling sub-statement. The
effects of such a statement will not be predictable."


.m