PostgreSQL 16：CTE是否会在INSERT...ON CONFLICT前执行？

架构老林

2026-6-13

问题

需要通过主键对行执行upsert操作并返回更新前的旧值，希望用单条查询实现（可接受中间数据变动，无需完全隔离），使用PostgreSQL 16，表结构如下：

CREATE TABLE test_table (
    user_id INT PRIMARY KEY,
    session_id INT
);

编写的查询如下：

WITH old_data AS (
  SELECT session_id FROM test_table WHERE user_id = :user_id
)
INSERT INTO test_table (user_id, session_id) VALUES (:user_id, :session_id)
ON CONFLICT (user_id) DO UPDATE SET session_id = :session_id
RETURNING (SELECT session_id FROM old_data);

该查询目前能正常返回预期的旧session_id，但不确定PostgreSQL是否保证old_data中的SELECT一定会在INSERT/UPDATE修改行之前执行，是否存在CTE在行更新后才执行导致返回新值的可能？从执行计划来看SELECT先运行：

Insert on test_table  (cost=8.17..8.18 rows=1 width=8) (actual time=0.110..0.111 rows=1 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: test_table_pkey
  Tuples Inserted: 0
  Conflicting Tuples: 1
  InitPlan 1 (returns $0)
    ->  Index Scan using test_table_pkey on test_table test_table_1  (cost=0.15..8.17 rows=1 width=4) (actual time=0.028..0.028 rows=1 loops=1)
          Index Cond: (user_id = 1)
  ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1)
Planning Time: 0.086 ms
Execution Time: 0.143 ms

但PostgreSQL文档未明确保证CTE中的SELECT与主INSERT ... ON CONFLICT的执行顺序，想明确：old_data是否一定会在实际INSERT/UPDATE操作前执行？还是此行为只是偶然，未来可能改变？

回答

你的当前写法在PostgreSQL 16及现有稳定版本中是可靠的，old_data中的SELECT一定会在INSERT/UPDATE的修改操作之前执行。从执行计划能看到，CTE的查询被作为InitPlan执行，这类计划节点会在主INSERT逻辑启动前完成计算。

虽然PostgreSQL官方文档没有明确单独声明这个执行顺序，但从查询执行的逻辑模型来看：普通非递归CTE会被规划器展开为子查询，而你的RETURNING子句依赖CTE的结果来返回旧值，规划器必然会优先执行CTE的查询——否则无法获取到更新前的旧数据。这种行为是由查询的逻辑需求驱动的，未来版本几乎不可能随意改变，因为这会破坏大量依赖该逻辑的现有应用。

另外，还有一种更简洁且官方逻辑更清晰的写法可以替代，无需依赖CTE：

INSERT INTO test_table (user_id, session_id) VALUES (:user_id, :session_id)
ON CONFLICT (user_id) DO UPDATE SET session_id = :session_id
RETURNING test_table.session_id;

在DO UPDATE分支中，RETURNING里的test_table.session_id就是更新前的旧值；如果是INSERT分支（无冲突），则返回的是新插入的值，这符合“无旧值则返回新值”的逻辑。若需要区分是插入还是更新操作，可以额外返回xmax字段：

INSERT INTO test_table (user_id, session_id) VALUES (:user_id, :session_id)
ON CONFLICT (user_id) DO UPDATE SET session_id = :session_id
RETURNING test_table.session_id, (xmax = 0) AS is_inserted;

当is_inserted为true时表示是新插入的行，否则是更新的行。

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