PostgreSQL-> CTE +更新+删除->出乎意料的结果，为什么？ [英] PostgreSql -> CTE + UPDATE + DELETE -> not expected result, why?

问题描述

只是感兴趣，为什么下面的（简化的）示例以这种方式工作。

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);

结果：

删除0

问题：

为什么DELETE找不到要删除的行？

PostgreSQL版本9.2.1

事务隔离=已提交读

PostgreSql version 9.2.1
Transaction isolation = read commited

谢谢！

推荐答案

我怀疑它与文档-

主要查询和WITH查询是所有（名义上）同时执行
。这意味着从查询的其他部分（除读取
的其他
之外），无法读取WITH中数据修改
语句的效果，而只能读取其RETURNING输出。如果两个这样的数据修改
语句试图修改同一行，则结果是
未指定。

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.

虽然我认为ID可用，因为它在WITH子查询中没有更改，但行可见性可能会发生一些变化。 未指定一词含糊其词，这可能确实是postgres列表中的一个问题，以便其中一位专家可以对其进行破解...

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 ：为了提供更多信息，我还尝试将 DELETE 替换为 SELECT * ，这返回了预期的行。我的直接反应是，如果可以找到要返回的行，则应该能够找到要删除的行。但是，如果我考虑得更多，该测试将支持该报价，因为一个数据修改语句与一个非数据修改语句配对会产生预期的结果，而两个数据修改语句会产生意外的结果。

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.

这篇关于PostgreSQL-> CTE +更新+删除->出乎意料的结果，为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！