PostgreSQL中DEFERRABLE INITIALLY IMMEDIATE约束为何逐语句检查？

测试场景与问题 #

先看你执行的未提交事务SQL：

rollback;
begin;
create table testing_stuff (
    id serial,
    num integer NOT NULL unique deferrable initially immediate
);

insert into testing_stuff (num) values (2), (1);

-- 即使交换值，deferrable也无问题
update testing_stuff set num = id;

-- 未提交却执行失败
update testing_stuff set num = 2;

-- 执行此语句可修复问题
update testing_stuff set num = id;

你的核心疑问：

1. INITIALLY IMMEDIATE是否适用于事务内所有后续语句？
2. 为什么第一次UPDATE能避免约束错误，而第二次UPDATE在未提交时就触发失败？

问题解答 #

1. INITIALLY IMMEDIATE的作用范围 #

INITIALLY IMMEDIATE是约束的初始检查时机配置，它的核心逻辑是：

• 事务启动时，该约束默认采用「立即检查」模式；
• 这个模式并非固定不可改——你可以在事务内通过SET CONSTRAINTS <约束名> DEFERRED/IMMEDIATE命令随时切换，后续语句会遵循修改后的检查规则。
• 简言之：它是事务初始状态的默认值，而非强制绑定事务内所有语句的固定规则。

2. 第一次UPDATE成功的原因 #

当约束为DEFERRABLE INITIALLY IMMEDIATE时，PostgreSQL对单条语句的约束检查逻辑是：

先完成语句内所有行的修改操作，再一次性检查约束是否违反

对比移除DEFERRABLE INITIALLY IMMEDIATE的情况（此时约束为NOT DEFERRABLE）：

• NOT DEFERRABLE会逐行修改后立即检查约束：第一行（id=1）把num从2改成1时，表中第二行的num还是1，此时就触发唯一约束错误；
• 而DEFERRABLE INITIALLY IMMEDIATE下，第一次UPDATE完成后，两行的num分别为1和2，无重复值，所以语句执行完毕后的约束检查顺利通过，不会报错。

3. 第二次UPDATE未提交就失败的原因 #

第二条UPDATE testing_stuff SET num = 2;会把所有行的num改成2，单条语句执行完成后，所有行的num值完全重复，直接违反唯一约束。

• 对于INITIALLY IMMEDIATE模式，单条语句执行完毕后会立即触发约束检查，所以此时直接报错，不需要等到事务提交；
• 而如果使用INITIALLY DEFERRED，约束检查会延迟到事务提交时，这条语句执行时不会报错，直到你执行COMMIT才会触发约束错误。

关键结论 #

• DEFERRABLE INITIALLY IMMEDIATE：单条语句执行后立即检查约束，允许事务内跨语句的中间状态违反约束（只要最终提交时符合要求）；
• NOT DEFERRABLE：逐行修改后立即检查，不允许任何中间状态违反约束；
• INITIALLY DEFERRED：仅在事务提交时检查约束，事务全程的中间状态都不会触发检查。

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