PostgreSQL中INSERT ON CONFLICT场景下INSERT与UPDATE触发器引发的超表无效行问题及解决方案咨询

这个问题我之前也碰到过，PostgreSQL里*INSERT ... ON CONFLICT*的*BEFORE INSERT*触发器触发时机确实是个容易踩的坑——不管最终是插入新行还是冲突后执行更新，*BEFORE INSERT*都会在冲突检测前先执行一遍，这就直接导致了你说的“为更新行创建无效超表行”的问题。结合你手动实现类表继承的场景，给你几个可行的解决方案，从调整现有触发器到重构结构都有：

方案一：在触发器里提前判断冲突，避免无效超表行创建 #

这个方案不需要大改现有表结构，只需要修改*BEFORE INSERT*触发器的逻辑：在创建超表行之前，先检查当前要插入的子表行是否已经存在（也就是是否会触发冲突），只有当不存在时才创建超表行。

比如你的子表sub_table1是用specific_field作为冲突唯一键，触发器函数可以改成这样：

CREATE OR REPLACE FUNCTION sub_table1_insert_trigger_fn()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.super_id IS NULL THEN
    -- 先检查当前行是否会触发冲突，加FOR UPDATE防止竞态
    PERFORM 1 FROM sub_table1 
    WHERE specific_field = NEW.specific_field 
    FOR UPDATE;
    
    -- 只有不存在冲突行时，才创建超表行
    IF NOT FOUND THEN
      INSERT INTO super_table (type, created_at, updated_at, is_deletable)
      VALUES ('some_type', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, false)
      RETURNING id INTO NEW.super_id;
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这样，当*INSERT ... ON CONFLICT*处理会更新的行时，触发器里的PERFORM会找到已存在的行，就不会创建新的超表行，完美解决无效行问题。

方案二：改用视图+INSTEAD OF触发器统一处理逻辑 #

如果你的批量插入逻辑比较复杂，可以把超表和子表的关联封装成一个视图，然后用*INSTEAD OF*触发器精准控制插入/更新行为，这样*INSERT ... ON CONFLICT*可以直接作用在视图上，完全避免触发器时机的问题。

1. 首先创建关联视图：

CREATE VIEW v_sub_table1 AS
SELECT 
  st.id AS super_id, st.type, st.created_at, st.updated_at, st.is_deletable,
  st1.specific_field
FROM sub_table1 st1
JOIN super_table st ON st1.super_id = st.id;

2. 然后给视图创建*INSTEAD OF INSERT*触发器，区分插入和更新场景：

CREATE OR REPLACE FUNCTION v_sub_table1_insert_trigger_fn()
RETURNS TRIGGER AS $$
BEGIN
  -- 先判断是否是冲突更新
  IF EXISTS (SELECT 1 FROM sub_table1 WHERE specific_field = NEW.specific_field) THEN
    -- 冲突更新：只更新子表和超表的需要修改的字段
    UPDATE sub_table1 
    SET specific_field = NEW.specific_field, updated_at = CURRENT_TIMESTAMP
    WHERE specific_field = NEW.specific_field;
    
    UPDATE super_table 
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = (SELECT super_id FROM sub_table1 WHERE specific_field = NEW.specific_field);
  ELSE
    -- 新行插入：先创建超表行，再插入子表
    INSERT INTO super_table (type, created_at, updated_at, is_deletable)
    VALUES ('some_type', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, false)
    RETURNING id INTO NEW.super_id;
    
    INSERT INTO sub_table1 (super_id, specific_field)
    VALUES (NEW.super_id, NEW.specific_field);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER v_sub_table1_insert_trigger
INSTEAD OF INSERT ON v_sub_table1
FOR EACH ROW
EXECUTE FUNCTION v_sub_table1_insert_trigger_fn();

之后你直接对视图执行*INSERT ... ON CONFLICT*操作即可，触发器会精准控制什么时候创建超表行。

方案三：改用PostgreSQL内置表继承替代手动关联 #

如果你是为了实现“公共字段存在超表，特有字段在子表”的类继承模式，其实可以直接用PostgreSQL的内置表继承，完全不需要手动的super_id关联和触发器，从根源上避免问题：

1. 创建父表（超表）：

CREATE TABLE super_table (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  type TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_deletable BOOLEAN NOT NULL DEFAULT false
);

2. 创建子表并继承父表：

CREATE TABLE sub_table1 (
  specific_field TEXT NOT NULL
) INHERITS (super_table);

-- 约束子表的type只能是指定值，保证类型正确
ALTER TABLE sub_table1 ADD CONSTRAINT sub_table1_type_check CHECK (type = 'some_type');

这样，插入子表时自动包含父表的所有字段，不需要触发器创建超表行（因为子表行本身就是父表的一部分，SELECT * FROM super_table会查到所有子表的行）；删除子表行时，对应的父表行也会被删除，完全符合你的规则。

方案四：用批量插入的显式标记控制触发器行为 #

如果你不想改触发器逻辑，也可以在批量插入时给要更新的行加一个标记，让触发器跳过超表行创建。比如给子表加一个临时/永久的布尔字段：

1. 给子表加字段：

ALTER TABLE sub_table1 ADD COLUMN is_upsert_update BOOLEAN DEFAULT false;

2. 批量插入时显式设置标记：

INSERT INTO sub_table1 (specific_field, is_upsert_update)
VALUES ('val1', false), ('val2', false)
ON CONFLICT (specific_field) DO UPDATE SET
  specific_field = EXCLUDED.specific_field,
  updated_at = CURRENT_TIMESTAMP,
  is_upsert_update = true;

3. 修改触发器函数，只给标记为false的行创建超表行：

CREATE OR REPLACE FUNCTION sub_table1_insert_trigger_fn()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.super_id IS NULL AND NEW.is_upsert_update = false THEN
    INSERT INTO super_table (type, created_at, updated_at, is_deletable)
    VALUES ('some_type', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, false)
    RETURNING id INTO NEW.super_id;
  END IF;
  -- 重置标记，避免影响后续操作
  NEW.is_upsert_update = false;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这个方案最简单，只需要加个字段和修改少量代码，适合快速解决问题。

内容来源于stack exchange