PostgreSQL 16.9中延迟约束触发器为何校验记录中间状态而非最终状态？

我太懂你这种憋屈感了——明明存储过程跑完后，查出来的记录完全符合约束要求，可延迟约束触发器偏偏揪着某个中间状态报错，连CTID都对不上，这简直是“我明明改好了，你却抓着我之前的错不放”！

咱们先拆解一下问题根源：你可能没注意到，PostgreSQL的约束触发器是绑定到每一次具体的DML操作上的。比如你在存储过程里先执行INSERT（此时成员的继承字段还是null），之后又用UPDATE把字段改成合法值。虽然最终行的状态没问题，但INSERT操作对应的约束检查会在事务结束时依然生效——它盯着的是INSERT那一刻的行版本（也就是字段为null的状态），而不是UPDATE后的最终版本。这就是为啥异常里的CTID和最终行的CTID不匹配，因为异常对应的是INSERT时的行版本，而最终行是UPDATE后的，CTID早就变了。

那怎么解决这个问题呢？核心思路就是让触发器别盯着操作时的临时状态，而是直接检查事务结束时表中的最终行状态。具体可以这么改：

修改触发器函数，查询最新行状态校验 #

把你原来触发器里依赖NEW值的逻辑，改成直接从member表中查询当前行的最新值来做检查。比如：

CREATE OR REPLACE FUNCTION required_fields() RETURNS TRIGGER AS $$
DECLARE
    app_source_id integer = (select source_id from source where name = 'APP');
    proc_source_id integer = (select source_id from source where name = 'PROC');
    current_first integer;
    current_second integer;
BEGIN
    IF new.source_id = app_source_id THEN
        RETURN NEW; -- 保留APP的原有校验逻辑
    ELSEIF new.source_id = proc_source_id THEN
        -- 直接查询表中该行的最新状态，而非依赖操作时的NEW值
        SELECT first_inheritable_field_id, second_inheritable_field_id
        INTO current_first, current_second
        FROM member
        WHERE member_id = NEW.member_id;

        IF current_first IS NOT NULL AND current_second IS NOT NULL THEN
            RETURN NEW;
        ELSE
            RAISE EXCEPTION 'Required fields are null. Member_Id: %, MEMBER NAME: %, first_inheritable_trait_id: %, second_inheritable_trait_id: %',
                NEW.member_id, NEW.name, current_first, current_second;
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

这样一来，不管你在事务里对这行做了多少次INSERT/UPDATE，触发器在事务结束时都会去查最新的行数据，只要最终状态合法就不会报错。

额外确认两个关键点 #

1. 确保你的约束触发器确实是DEFERRABLE INITIALLY DEFERRED的，创建触发器的语句大概是这样：

CREATE CONSTRAINT TRIGGER member_required_fields_trigger
AFTER INSERT OR UPDATE ON member
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION required_fields();

2. 存储过程开头的SET CONSTRAINTS ALL DEFERRED;一定要保留，确保所有延迟约束都在事务结束时才检查。

另外，如果你能调整存储过程的逻辑，尽量一次性INSERT出符合约束的行（比如先确定parent_id对应的继承值，再插入），那就能从根源上避免中间状态的问题，但如果业务逻辑必须分步骤操作，上面的触发器修改方案应该能解决你的困扰。

内容来源于stack exchange