Thread: BUG #5987: Rows created by WITH (INSERT ... RETURNING) are not visible to the rest of the query

The following bug has been logged online:

Bug reference:      5987
Logged by:          Daniel Grace
Email address:      dgrace@wingsnw.com
PostgreSQL version: 9.1-alpha5
Operating system:   Win7 x64
Description:        Rows created by WITH (INSERT ... RETURNING) are not
visible to the rest of the query
Details:

This may not be a bug, but I'll describe it anyways.  Apologies if this is a
duplicate -- "WITH" is apparently a stopword and searching the archives
using it produces zero results.

9.1 adds the ability to use WITH in INSERT/UPDATE/DELETE in both
directions.

However, the actual effects of the query in the WITH clause are not visible
to the outer query.

-----
DROP TABLE IF EXISTS a;
CREATE TABLE a ( t TEXT );

INSERT INTO a VALUES ('test1') RETURNING *;    -- Works as expected

WITH b AS (INSERT INTO a VALUES ('test2') RETURNING *) SELECT * FROM b;    --
Works as expected

WITH b AS (INSERT INTO a VALUES ('test3') RETURNING *) SELECT a.* FROM a
INNER JOIN b USING(t);    -- Does not see the newly created row.

SELECT * FROM a WHERE t='test3';    -- But it was created.

WITH b AS (INSERT INTO a VALUES ('test4') RETURNING *) -- Does not see the
newly created row, thus the update does not happen.
UPDATE a SET t='test5'
FROM b
WHERE a.t=b.t;

SELECT * FROM a;
-----
This is also true if the WITH query is a stored procedure that modifies the
database and returns results, i.e. WITH b AS (SELECT * FROM
create_row('test6'))...

Presumably it affects UPDATE and DELETE as well, but I didn't test those
cases.

My actual use case is: I'm calling a function to duplicate+modify some rows.
 (Essentially, it does INSERT ... SELECT from the same table, but forcing
the primary key to be reassigned via being a serial column and some other
changes).  This function returns the new rows as results (doing RETURN QUERY
INSERT ... SELECT ... RETURNING *).  In some situations, I want to further
update the freshly created rows, so the goal was to do this:

WITH newrows AS (SELECT * FROM function_that_creates_rows(...))
UPDATE basetable SET foo=overrides.bar
FROM newrows
LEFT JOIN (VALUES (...)) AS overrides(...)
WHERE ...

"Daniel Grace" <dgrace@wingsnw.com> writes:
> Description:        Rows created by WITH (INSERT ... RETURNING) are not
> visible to the rest of the query

This is not a bug but the designed behavior.  See
http://developer.postgresql.org/pgdocs/postgres/queries-with.html

particularly this bit:

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 alleviates the effects of the unpredictability of
the actual order of row updates, and means that RETURNING data is the
only way to communicate changes between different WITH sub-statements
and the main query.

            regards, tom lane