PostgreSQL中CTE或临时表二次读取报错的问题排查与解决

CTE方案报错原因 #

你使用的CTE是语句级临时视图，作用域仅限定义后的第一条SQL语句，后续的SELECT无法访问该CTE，因此触发"relation does not exist"错误。CTE并非事务级的临时对象，不能跨语句复用。

临时表方案重复存在的原因 #

PostgreSQL中，默认创建的临时表是会话级的——事务提交后不会自动销毁，只要当前数据库会话未断开，临时表就会一直存在。这就是第二次执行代码时提示表已存在的原因。

最优解决方案：用UPDATE ... RETURNING直接实现 #

完全不需要CTE或临时表，PostgreSQL的UPDATE语句支持RETURNING子句，可直接返回被更新的行，完美满足你“筛选-更新-返回”的原子性需求：

BEGIN;
UPDATE my_table 
SET is_readed = true 
WHERE is_readed = false 
RETURNING *; -- 直接返回所有被更新的记录
COMMIT;

如果业务逻辑更复杂，必须用CTE串联操作，可以把更新和查询合并到同一个CTE链中：

BEGIN;
WITH unwatched_rows AS (
    SELECT * from my_table WHERE is_readed = false FOR UPDATE
), updated_result AS (
    UPDATE my_table 
    SET is_readed = true 
    WHERE id IN (SELECT id FROM unwatched_rows)
    RETURNING *
)
SELECT * FROM updated_result;
COMMIT;

临时表方案的修复 #

若坚持使用临时表，创建时添加ON COMMIT DROP选项，让临时表在事务提交后自动删除：

BEGIN;
CREATE TEMPORARY TABLE unwatched_rows ON COMMIT DROP AS (
    SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT; -- 事务提交后临时表立即销毁

内容的提问来源于stack exchange，提问作者SorryForAsking