PostgreSQL复合主键下按组织自动递增任务ID的并行冲突解决

问题根源 #

你之前用BEFORE INSERT触发器通过SELECT MAX(id)+1生成ID时，并行插入场景下多个事务会读到同一个快照中的最大值，导致各自生成重复的ID，触发复合主键约束冲突——这是因为MAX(id)查询是无锁的快照读，无法保证并发下的唯一性。

最优解决方案（兼容PostgREST原生CRUD） #

方案一：专用计数器表+原子更新（推荐，通用场景） #

通过一个独立的计数器表存储每个组织的当前任务ID序列，利用PostgreSQL的UPDATE ... RETURNING原子操作实现无冲突的ID分配，完全兼容PostgREST的自动插入逻辑。

1. 创建计数器表

CREATE TABLE organization_task_counters (
  organizationId INT PRIMARY KEY, -- 与Tasks表的organizationId类型保持一致
  next_task_id INT NOT NULL DEFAULT 1
);

2. 编写触发器函数

CREATE OR REPLACE FUNCTION assign_task_id()
RETURNS TRIGGER AS $$
DECLARE
  new_task_id INT;
BEGIN
  -- 若组织是首次创建任务，自动初始化计数器（不存在则插入）
  INSERT INTO organization_task_counters (organizationId)
  VALUES (NEW.organizationId)
  ON CONFLICT (organizationId) DO NOTHING;

  -- 原子递增并获取当前可用ID
  UPDATE organization_task_counters
  SET next_task_id = next_task_id + 1
  WHERE organizationId = NEW.organizationId
  RETURNING next_task_id - 1 INTO new_task_id;

  NEW.id := new_task_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

3. 绑定触发器到Tasks表

CREATE TRIGGER trigger_assign_task_id
BEFORE INSERT ON tasks
FOR EACH ROW
WHEN (NEW.id IS NULL) -- 允许手动指定ID，兼容特殊业务场景
EXECUTE FUNCTION assign_task_id();

4. 初始化现有数据（若有历史任务）
   如果Tasks表已有数据，需要同步计数器表的初始值：

INSERT INTO organization_task_counters (organizationId, next_task_id)
SELECT organizationId, MAX(id) + 1
FROM tasks
GROUP BY organizationId
ON CONFLICT (organizationId) DO UPDATE 
SET next_task_id = GREATEST(organization_task_counters.next_task_id, EXCLUDED.next_task_id);

方案二：按组织创建独立序列（适合组织数量较少的场景） #

为每个组织创建专属的序列，利用PostgreSQL序列的原子nextval操作保证ID唯一性，避免并行冲突。

1. 编写触发器函数

CREATE OR REPLACE FUNCTION assign_task_id_via_seq()
RETURNS TRIGGER AS $$
DECLARE
  seq_name TEXT := 'task_seq_' || NEW.organizationId;
BEGIN
  -- 若组织对应的序列不存在，自动创建
  IF NOT EXISTS (SELECT 1 FROM pg_sequence WHERE seqrelid = seq_name::regclass) THEN
    EXECUTE format('CREATE SEQUENCE %I START WITH 1', seq_name);
  END IF;

  -- 获取序列下一个值作为任务ID
  EXECUTE format('SELECT nextval(%L)', seq_name) INTO NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器

CREATE TRIGGER trigger_assign_task_id_seq
BEFORE INSERT ON tasks
FOR EACH ROW
WHEN (NEW.id IS NULL)
EXECUTE FUNCTION assign_task_id_via_seq();

兼容性说明 #

两个方案都完全兼容PostgREST原生CRUD接口：

• 用户调用POST /tasks时，只需提交organizationId及其他任务字段，无需指定id，触发器会自动赋值
• 支持手动指定id插入（通过WHEN (NEW.id IS NULL)条件），满足特殊业务需求
• PostgREST会正常返回插入后的完整任务记录，包括自动生成的id

方案对比 #

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