调用VOLATILE函数破坏SELECT原子性的PostgreSQL技术问询

测试准备 #

首先在PostgreSQL 15.3环境创建测试表：

create table test (
    test_id int2,
    test_val int4,
    primary key (test_id)
);

基础并发测试 #

在Read Committed隔离级别下，并行执行以下两个事务：

事务1 #

insert into test (select 1, 1 from pg_sleep(2));

事务2 #

select coalesce(
    (select test_val from test where test_id = 1),
    (select null::int4 from pg_sleep(5)),
    (select test_val from test where test_id = 1)
);

事务2作为单条语句执行，全程复用同一个数据快照，因此即使事务1在中间完成插入，最终返回null，符合预期。

引入VOLATILE函数后的异常结果 #

先创建一个VOLATILE类型的SQL函数，再执行新的查询：

create function select_testval
    (id int2) returns int4
    language sql strict volatile -- 改为stable函数会保持原单语句快照特性
return (select test_val from test where test_id = id);

-- 事务2.1
select coalesce(
    (select test_val from test where test_id = 1),
    (select null::int4 from pg_sleep(5)),
    select_testval(1::int2)
);

此时查询返回1，说明函数调用部分使用了新的数据快照，读取到了事务1提交的插入数据。

技术问题解答 #

1. 函数调用引入新快照是否合规？相关规则是什么？ #

这是完全合规的行为，PostgreSQL根据函数的稳定性级别定义了快照使用规则：

• VOLATILE函数：被标记为每次调用可能返回不同结果，甚至修改数据库状态。在Read Committed隔离级别下，VOLATILE函数的每次调用都会获取当前最新的数据库快照，因此能看到主语句执行过程中其他事务提交的变更。
• STABLE/IMMUTABLE函数：在单条SQL语句执行期间会复用同一个快照。STABLE保证事务内多次调用结果一致，IMMUTABLE则完全不依赖数据库状态。

2. VOLATILE函数是否可能被内联？如何控制？ #

VOLATILE函数在特定场景下会被优化器内联，比如函数逻辑简单、无明显副作用时，优化器会将函数逻辑直接融入主语句，此时函数会复用主语句的快照，改变原本的并发语义。

控制内联的方法：

• 创建函数时显式添加NOINLINE选项，强制优化器不内联该函数：

  create function select_testval(id int2) returns int4
      language sql strict volatile NOINLINE
  return (select test_val from test where test_id = id);
  

• 临时禁用会话内的函数内联：设置set local plan_cache_mode = 'force_custom_plan';，但该参数会影响整个会话的优化器策略，仅适合调试场景。

注：该问题源于寻找纯SQL方案解决并发插入时的唯一约束冲突场景。

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