Just interested, why below ( simplified ) example works this way.
CREATE TABLE test (id SERIAL, val INT NOT NULL, PRIMARY KEY(id));
INSERT INTO test (val) VALUES (1);
WITH t AS ( UPDATE test SET val = 1 RETURNING id )
DELETE FROM test WHERE id IN ( SELECT id FROM t);
Result:
DELETE 0
Question:
Why DELETE did not find any rows to delete?
PostgreSql version 9.2.1
Transaction isolation = read commited
Thanks!
I suspect it has something to do with this line in the docs -
The primary query and the WITH queries are all (notionally) executed at the same time. This implies that the effects of a data-modifying statement in WITH cannot be seen from other parts of the query, other than by reading its RETURNING output. If two such data-modifying statements attempt to modify the same row, the results are unspecified.
While I would think the ID would be available since it isn't changing in the WITH subquery, there could be something going on with row visibility. The term "unspecified" is pretty vague, this may really be a question for the postgres list so that one of the gurus can have a crack at it...
EDIT: To provide slightly more information, I also tried replacing DELETE with SELECT *, and this returned the expected rows. My immediate reaction was that if it can find the rows to return them, it should be able to find them to delete them. But if I think about it more, this test supports the quote, in that a data-modifying statement paired with a non-data-modifying statement produces the expected results, whereas two data-modifying statements produce unexpected results.
