PostgreSQL 16逻辑复制AFTER INSERT触发器无法读取同事务数据

问题原因 #

这是逻辑复制的特性导致的：逻辑复制默认按表分组回放WAL操作，而非原子性地完整回放整个原事务。

主库上的触发器在原事务内部执行，事务快照包含了该事务内所有未提交的修改，因此能正常关联同事务插入的customer_address行；但副本上，当回放customer表的INSERT操作时，同事务中customer_address的INSERT可能还未被执行，此时触发器查询customer_address会找不到对应行，导致city字段为null。

解决方案 #

可以通过在两个关联表上分别创建触发器，结合UPSERT（插入或更新）逻辑，确保最终customer_inserts表能得到完整数据。

步骤1：创建customer表的触发器（副本） #

该触发器负责插入customer的基础数据，若已有对应ID的行则更新邮箱：

CREATE OR REPLACE FUNCTION customer_insert_trg_fn()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO customer_inserts (id, email, city)
  VALUES (NEW.id, NEW.email, NULL)
  ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER customer_trg
AFTER INSERT ON customer
FOR EACH ROW
ENABLE REPLICA TRIGGER
EXECUTE FUNCTION customer_insert_trg_fn();

步骤2：创建customer_address表的触发器（副本） #

该触发器负责补全或更新customer_inserts中的城市信息，若没有对应ID的行则先插入：

CREATE OR REPLACE FUNCTION customer_address_insert_trg_fn()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO customer_inserts (id, city, email)
  VALUES (NEW.id, NEW.city, NULL)
  ON CONFLICT (id) DO UPDATE SET city = EXCLUDED.city;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER customer_address_trg
AFTER INSERT ON customer_address
FOR EACH ROW
ENABLE REPLICA TRIGGER
EXECUTE FUNCTION customer_address_insert_trg_fn();

原理说明 #

无论副本先回放customer还是customer_address的操作，通过UPSERT逻辑都能确保customer_inserts表最终整合两张表的数据：

• 若先回放customer：插入的行city为null，后续回放customer_address时会更新该字段
• 若先回放customer_address：插入的行email为null，后续回放customer时会更新该字段

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