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

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.