Postgresql – Workaround the snapshot isolation in a writable CTE

ctepostgresqlpostgresql-9.2

The postgres docs say:

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables. This … means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

I'm looking for a way to work around this effect in a situation like this:

Schema Setup:

create table t1(id serial);

create table t2(id integer);

create function f1() returns integer language sql as $$
  insert into t1 default values returning id; --
$$;

create function f2() returns integer language sql as $$
  with w as (insert into t1 default values returning id)
  insert into t2(id) select id from w join t1 using(id) returning id; --
$$;

Example:

select f2()
/*
|     F2 |
----------
| (null) |
*/

select * from t1
/*
| ID |
------
|  1 |
*/

select * from t2
/*
| ID |
------
*/

SQLFiddle

I'd like the insert in f2 to see the data in t1 and so end up with 1 row in t2.

Best Answer

If you return * from the CTE then you are effectively reading the row that you are currently trying to read from t1 -- that's how you mange to read the changes you're making to t1.

In your current code the join to t1 is redundant by the way, but I get the impression that you're using an example more simple than you really want.

Related Question