BUG #13465: multi update query use CTE, result & plan not equal, BUG? - Mailing list pgsql-bugs

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)