PostgreSQL中set_config在CTE/INSERT触发器中的生效性及竞态问题

我需要在与INSERT语句相同的事务/作用域内调用set_config设置配置参数，同时有一个AFTER INSERT触发器会读取这个参数，且必须用单条语句实现，因此尝试了CTE方案：

第一次尝试（触发器无法读取参数） #

WITH 
  idx AS (SELECT set_config('audit.trace_id', '123', true)), 
  rows AS (INSERT INTO "person" ("name") values ('Mary') RETURNING *) 
SELECT * FROM rows

触发器函数无法读取audit.trace_id。

第二次尝试（仍无法读取） #

调整CTE结构试图建立依赖：

WITH 
  idx AS (SELECT set_config('audit.trace_id', '123', true)) 
INSERT INTO "person" ("name") values ('Mary') RETURNING *

依旧无法读取参数，推测是PostgreSQL跳过了没有后续依赖的SELECT语句。

第三次尝试（触发器可成功读取） #

同时查询两个CTE的结果后，触发器能正常读取参数：

WITH 
  idx AS (SELECT set_config('audit.trace_id', '123', true) as __trace_id),
  row as (INSERT INTO "person" ("name") values ('Mary') RETURNING *)
SELECT * FROM idx, row

当person表的触发器执行时，能否保证audit.trace_id已经被设置？是否存在竞态条件？

以下写法测试正常：

INSERT INTO "person" ("name") values ('Mary') RETURNING *, set_config('audit.trace_id', '123', true)

答案 #

1. 参数设置的确定性：在第三种CTE写法以及补充的RETURNING写法中，触发器执行时完全可以保证audit.trace_id已被设置，不存在竞态条件。
   • 对于CTE写法：PostgreSQL的CTE执行规则是，只有被主查询引用的CTE才会被执行，且执行顺序严格遵循依赖关系。第三种写法中idx CTE被主查询通过SELECT * FROM idx, row引用，因此set_config会在INSERT语句执行前完成调用，AFTER INSERT触发器触发时，参数已经存在于当前事务的作用域中。
   • 对于RETURNING写法：PostgreSQL中RETURNING子句的执行时机是在行插入完成后、AFTER INSERT触发器触发前。因此set_config会在触发器执行前完成参数设置，触发器可以正常读取。
2. 前两次尝试失败的原因：前两种写法里，idx CTE没有被主查询引用，PostgreSQL的查询优化器会直接跳过这个无依赖的CTE，导致set_config根本没有被执行，自然触发器读不到参数。

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