Обсуждение: BUG #5987: Rows created by WITH (INSERT ... RETURNING) are not visible to the rest of the query
BUG #5987: Rows created by WITH (INSERT ... RETURNING) are not visible to the rest of the query
От
"Daniel Grace"
Дата:
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