PostgreSQL 10逻辑复制触发器函数未执行问题排查

首先直接解答你的核心疑问：为什么DB2的触发器在复制插入时没触发？

PostgreSQL逻辑复制的订阅端默认会跳过触发器、规则以及大部分约束检查（仅保留主键/唯一约束这类保证数据一致性的规则），这个行为由订阅的skip_triggers参数控制（默认值为true）。设计这个机制是为了避免源库已经执行过的业务逻辑（比如你的BEFORE INSERT触发器生成ID）在目标库重复执行，防止数据异常。所以当DB1的记录复制到DB2时，DB2上的update_ogc_fid_address触发器完全不会被触发，序列自然停留在原有位置，导致本地插入时仍使用旧值。

接下来针对你的主主架构需求，提供一套完整的解决方案，确保两边序列能自动同步到正确位置，且不会生成重复ID：

步骤1：修改触发器函数，区分本地插入与复制插入 #

我们需要让触发器只在本地插入时生成ID，复制过来的插入操作直接保留原ID。可以用pg_replication_origin_session_is_setup()函数判断当前操作是否来自逻辑复制：

CREATE OR REPLACE FUNCTION update_ogc_fid() 
RETURNS trigger 
LANGUAGE 'plpgsql' 
COST 100 VOLATILE NOT LEAKPROOF 
AS $BODY$ 
Declare 
    iInt integer; 
BEGIN 
    -- 如果是逻辑复制过来的操作，直接返回原记录，不生成新ID
    IF pg_replication_origin_session_is_setup() THEN
        RETURN NEW;
    END IF;
    
    -- 本地插入时，生成对应奇偶的ID
    SELECT nextval('address_ogc_fid_seq') INTO iInt; 
    NEW.ogc_fid = iInt; 
    RETURN NEW; 
END; 
$BODY$;

步骤2：创建序列同步的AFTER INSERT触发器 #

不管是本地插入还是复制插入，我们都需要确保序列的当前值不会小于已插入的最大ID，避免后续生成重复值。创建一个自动同步序列的触发器函数：

CREATE OR REPLACE FUNCTION sync_address_seq_after_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
DECLARE
    seq_start integer;
    seq_inc integer;
    desired_next_val integer;
BEGIN
    -- 获取当前序列的起始值和步长
    SELECT start_value, increment 
    INTO seq_start, seq_inc
    FROM pg_sequence
    WHERE seqrelid = 'address_ogc_fid_seq'::regclass;
    
    -- 计算下一个符合步长规则的、不小于当前插入ID的数值
    IF NEW.ogc_fid >= seq_start THEN
        desired_next_val = seq_start + ((NEW.ogc_fid - seq_start + seq_inc - 1) / seq_inc) * seq_inc;
    ELSE
        desired_next_val = seq_start;
    END IF;
    
    -- 只有当计算出的下一个值大于当前序列的nextval结果时，才更新序列
    IF desired_next_val > nextval('address_ogc_fid_seq') THEN
        -- 将序列当前值设置为 desired_next_val - 步长，确保nextval返回desired_next_val
        PERFORM setval('address_ogc_fid_seq', desired_next_val - seq_inc, false);
    END IF;
    
    RETURN NULL; -- AFTER触发器无需返回值
END;
$BODY$;

然后创建对应的AFTER触发器：

CREATE TRIGGER sync_address_seq_after_insert
AFTER INSERT ON address
FOR EACH ROW
EXECUTE PROCEDURE sync_address_seq_after_insert();

步骤3：修改订阅参数，允许触发器执行 #

因为我们需要让触发器在复制操作时也能执行（用来同步序列），所以要修改订阅的skip_triggers参数为false，两边的订阅都要修改：

在DB2上执行（对应DB1的订阅）：

ALTER SUBSCRIPTION your_db1_subscription SET (skip_triggers = false);

在DB1上执行（对应DB2的订阅）：

ALTER SUBSCRIPTION your_db2_subscription SET (skip_triggers = false);

步骤4：验证效果 #

• 在DB1插入一条记录，会生成奇数ID（比如3），这条记录复制到DB2后，DB2的序列会自动同步到下一个偶数（4），后续DB2本地插入会生成4、6...
• 在DB2插入一条记录，会生成偶数ID（比如2），复制到DB1后，DB1的序列会同步到下一个奇数（3），后续DB1本地插入会生成3、5...
• 故障恢复后，两边的序列都会基于已插入的最大ID继续生成正确的奇偶ID，不会重复。

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