PLPGSQL回填函数中临时表行锁定失效致数据不一致问题

你遇到的核心问题是并发事务的时序冲突和对FOR UPDATE锁行为的误解，具体原因如下：

• 锁与数据读取的间隙：如果回填函数先查询获取status=TRUE的行（未加锁），之后才加锁，那么在查询和加锁的间隙，其他事务可以修改行的status为FALSE并提交。此时你的函数仍会使用之前查询到的旧状态（TRUE）插入table B，和触发器的删除操作冲突。
• FOR UPDATE的真实行为：SELECT ... FOR UPDATE仅会锁定扫描时符合status=TRUE的行，但如果你的逻辑是先把行数据存入临时表，之后没有再次验证status就插入，哪怕后续行被修改，还是会执行插入操作。
• 隔离级别与快照问题：如果回填事务用了REPEATABLE READ隔离级别，会读取事务启动时的快照，看不到其他事务提交的修改，导致基于旧状态插入数据。

1. 原子化锁、验证与插入操作 #

把数据读取、锁获取、状态验证、插入放在同一个原子步骤里，避免间隙：

CREATE OR REPLACE FUNCTION backfill_single_batch()
RETURNS void AS $$
DECLARE
    rec record;
BEGIN
    -- 遍历锁定当前status仍为TRUE的行
    FOR rec IN SELECT id, col1, col2 FROM table_a WHERE status = TRUE FOR UPDATE LOOP
        -- 显式验证当前行的status，确保未被修改
        PERFORM 1 FROM table_a WHERE id = rec.id AND status = TRUE FOR UPDATE;
        IF FOUND THEN
            -- 仅当状态确实为TRUE时插入，避免重复
            INSERT INTO table_b (id, col1, col2)
            VALUES (rec.id, rec.col1, rec.col2)
            ON CONFLICT (id) DO NOTHING;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

2. 调整触发器为BEFORE UPDATE #

将触发器时机从AFTER UPDATE改为BEFORE UPDATE，在修改table A的status前就处理table B的行，减少并发冲突：

CREATE OR REPLACE FUNCTION on_status_update()
RETURNS trigger AS $$
BEGIN
    IF OLD.status = TRUE AND NEW.status = FALSE THEN
        -- 即将改为FALSE，提前删除table B对应行
        DELETE FROM table_b WHERE id = NEW.id;
    ELSIF OLD.status = FALSE AND NEW.status = TRUE THEN
        -- 即将改为TRUE，提前插入table B对应行
        INSERT INTO table_b (id, col1, col2)
        VALUES (NEW.id, NEW.col1, NEW.col2)
        ON CONFLICT (id) DO NOTHING;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_table_a_status_update
BEFORE UPDATE OF status ON table_a
FOR EACH ROW EXECUTE FUNCTION on_status_update();

3. 使用默认隔离级别 #

保持回填事务使用PostgreSQL默认的READ COMMITTED隔离级别，此时SELECT ... FOR UPDATE会自动重新检查行的当前状态，过滤掉已经变为FALSE的行。

4. 避免批量缓存后延迟插入 #

不要把需要回填的行先存入临时表再批量插入——临时表中的数据是快照数据，不会反映后续的行修改。直接在循环中锁定行、验证状态、插入，保持操作的原子性。

执行回填函数时，任何尝试修改已锁定行status的事务都会被阻塞，直到回填事务完成。回填事务在插入前会验证行的当前status，确保只有status=TRUE的行才会被插入到table B，彻底避免触发器删除后又被回填插入的情况。

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