Re: BUG #13465: multi update query use CTE, result & plan not equal, BUG?

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

before analyze t1,t2,t3:
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)

The same SQL:
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)

在 2015-06-24 22:45:29，"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> 写道：

On Wed, Jun 24, 2015 at 1:58 AM, <digoal(at)126(dot)com> wrote:

The following bug has been logged on the website:

Not a bug...

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?

The 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.

t1(1,'abc')

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

David J.