PostgreSQL跨FDW执行upsert时两种ON CONFLICT写法差异问题

通过postgres-to-postgres外部数据包装器（FDW）执行upsert更新本地表时，两种ON CONFLICT写法表现存在明显差异：

• 第一种写法在ON CONFLICT子句中通过主键约束名触发冲突判断，更新十万级数据量的表时会出现10-20条记录丢失的问题
• 第二种写法直接指定主键列作为冲突判断依据，可稳定完成全量upsert操作

两种写法代码示例 #

写法1：指定主键约束作为冲突判断依据 #

INSERT INTO myschema.mytable AS t1 (
    SELECT 
        column1,
        column2
    FROM fdwschema.fdwtable 
)
ON CONFLICT ON CONSTRAINT mytable_pkey
    DO UPDATE SET...

写法2：指定主键列作为冲突判断依据 #

INSERT INTO myschema.mytable AS t1 (
    SELECT 
        column1,
        column2
    FROM fdwschema.fdwtable 
)
ON CONFLICT ON (pk_column)
    DO UPDATE SET...

场景前置操作流程 #

upsert执行前会完成预处理，执行结束后再重建索引和外键，完整流程共3步：

1. 若目标表不存在则创建空表；若表已存在则先删除原有主键约束再重新创建，同时移除所有现有索引、外键
2. 执行表数据upsert更新
3. 重新创建所有索引、外键约束
   完整操作示例SQL：

CREATE TABLE IF NOT EXISTS jupiter.code AS
     (
         SELECT 
             code,
             codetype,
             shorttext,
             longtext,
             sortno,
             insertdate,
             updatedate
         FROM geus_fdw.code
         LIMIT 0
     )
;

ALTER TABLE jupiter.code 
    DROP CONSTRAINT IF EXISTS code_pkey CASCADE
;

ALTER TABLE jupiter.code 
    ADD PRIMARY KEY (code, codetype)
;

ALTER TABLE jupiter.code 
    DROP CONSTRAINT IF EXISTS fk_codetype_code CASCADE
;

DROP INDEX IF EXISTS code_code_idx;
DROP INDEX IF EXISTS code_codetype_idx;

INSERT INTO jupiter.code AS t1
    (
        SELECT 
            code,
            codetype,
            shorttext,
            longtext,
            sortno,
            insertdate,
            updatedate
        FROM geus_fdw.code t2
    ) 
    ON CONFLICT (code, codetype) -- 替换为ON CONSTRAINT code_pkey时会出现丢数
        DO UPDATE SET 
            code = excluded.code,
            codetype = excluded.codetype,
            shorttext = excluded.shorttext,
            longtext = excluded.longtext,
            sortno = excluded.sortno,
            insertdate = excluded.insertdate,
            updatedate = excluded.updatedate
        WHERE COALESCE(t1.updatedate, t1.insertdate) != 
            COALESCE(excluded.updatedate, excluded.insertdate)
;

CREATE INDEX IF NOT EXISTS code_code_idx
    ON jupiter.code (code)
;

CREATE INDEX IF NOT EXISTS code_codetype_idx
    ON jupiter.code(codetype)
;

ALTER TABLE jupiter.code 
    ADD CONSTRAINT fk_codetype_code FOREIGN KEY (codetype) 
    REFERENCES jupiter.codetype (codetype)
;

常规本地表场景下两种写法语义完全等价，丢数问题是当前的表预处理流程+FDW执行机制共同导致的，核心差异有三点：

• 元数据依赖逻辑不同
  ON CONFLICT ON CONSTRAINT 约束名的逻辑是先通过约束名匹配对应的唯一索引，再用该索引做冲突判断。由于每次执行前都会删除旧主键、重建新主键，如果建主键和upsert操作在同一个事务内，或者Postgres系统目录缓存未及时刷新，FDW生成执行计划时可能拿到旧主键的元数据——比如旧主键的列顺序错误、对应索引的OID已失效，最终导致部分行的冲突判断逻辑错位：本该触发更新的行被判定为新行插入，触发主键冲突后被FDW的批量插入逻辑静默丢弃（postgres_fdw批量写入时默认不会抛出单行插入失败的错误，会直接跳过问题行）。
  而ON CONFLICT (列名)的写法不需要匹配约束名，执行时直接按指定列查找匹配的唯一索引，完全不依赖约束相关的元数据缓存，不会出现索引匹配错误的问题。
• 冲突检测的执行路径不同
  通过约束名触发冲突时，Postgres走严格的约束校验路径：要求插入值必须完全符合约束定义才会判定为冲突。如果远端FDW表的主键列和本地列的排序规则（collation）、字段类型存在隐式差异（比如char和varchar的尾部空格差异、不同编码下的字符相等判断规则差异），部分边界值会被判定为无冲突，插入时触发主键重复后被静默丢弃。
  通过列名触发冲突时，Postgres走唯一索引检测路径，会自动将插入值转换为和本地索引匹配的格式再做冲突判断，不会因为类型、排序规则的隐式差异漏判冲突。
• FDW批量写入的锁逻辑不同
  使用约束名写法时，FDW批量拉取数据写入前会提前给约束对应的索引加共享锁，如果刚建完主键未提交事务，索引还处于构建过渡状态，锁粒度会升级为表级锁，批量写入过程中少量行因为排序、锁等待超时，会被FDW直接跳过不报错。列名写法则是逐行插入时申请行级锁，不会出现批量超时丢行的问题。

验证方式：可以尝试把建主键和upsert拆成两个独立事务提交，执行upsert前运行ANALYZE jupiter.code;刷新统计信息，此时约束名写法的丢数概率会明显下降，但在FDW批量写入场景下，稳定性仍然不如直接指定列名的版本。

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