PostgreSQL如何基于插入值创建多租户自增ID触发器

你原有方案存在两个致命问题，无法正常运行：

• 触发器定义错误：使用AFTER UPDATE触发时机，既不会在INSERT操作时触发，也无法在数据写入前填充id字段
• 裸max(id)+1查询无并发控制，多事务同时写入同一租户数据时，会读取到相同的最大id值，触发主键冲突错误

以下是适配你现有表结构的可直接运行的实现（基于PostgreSQL语法，和你表中timestamp with time zone等字段的数据库环境完全匹配）： #

1. 创建触发器函数 #

函数逻辑会在插入数据前自动判断：如果未手动传入id，就对当前租户的存量记录加排他行锁，阻塞同租户的并发写入，再计算该租户下的下一个自增id赋值给新记录；如果手动传入了id则保留传入值，兼容特殊场景的数据导入需求。

CREATE OR REPLACE FUNCTION set_account_default_tenant_seq_id()
RETURNS TRIGGER AS $$
DECLARE
    next_available_id smallint;
BEGIN
    IF NEW.id IS NULL THEN
        -- 加行锁避免同租户并发插入生成重复ID
        PERFORM id FROM account_default WHERE tenant_id = NEW.tenant_id FOR UPDATE;

        -- 计算当前租户下一个可用ID，空租户从1开始计数
        SELECT COALESCE(MAX(id), 0) + 1
        INTO next_available_id
        FROM account_default
        WHERE tenant_id = NEW.tenant_id;

        NEW.id := next_available_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

2. 绑定行级前置插入触发器 #

触发器设置为BEFORE INSERT行级触发，在数据实际写入表之前完成id字段赋值，不需要修改你原有INSERT语句的写法：

DROP TRIGGER IF EXISTS trg_account_default_auto_id ON account_default;
CREATE TRIGGER trg_account_default_auto_id
BEFORE INSERT ON account_default
FOR EACH ROW
EXECUTE FUNCTION set_account_default_tenant_seq_id();

验证效果 #

直接执行你提供的tenant_id=5的插入语句，不需要传入id字段，插入完成后查询表数据，主键结果完全符合预期：

-- 执行3条tenant_id=5的插入后查询
SELECT id, tenant_id FROM account_default WHERE tenant_id =5;
-- 返回结果：
-- id | tenant_id
-- ----+-----------
--  1 |         5
--  2 |         5
--  3 |         5

性能说明 #

• 普通业务场景（单租户每秒写入<10条）下，上述方案锁粒度精确到租户维度，不会出现跨租户锁阻塞，稳定性足够
• 如果是高并发写入场景，可以替换为「租户ID-序列映射表」方案，为每个租户创建独立序列，避免max查询的开销，性能更高

内容的提问来源于stack exchange，提问作者Đỗ Như Vỹ