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.g.johnston@gmail.com> 写道：
On Wed, Jun 24, 2015 at 1:58 AM, <digoal@126.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.