PostgreSQL循环序列主键冲突：触发器实现的优化与风险咨询

背景与场景 #

在PostgreSQL中创建带循环属性的序列及关联表：

CREATE SEQUENCE public.test_sequence
    CYCLE
    MINVALUE 1
    MAXVALUE 2147483647;

CREATE TABLE public.test_table
(
    id integer NOT NULL DEFAULT nextval('test_sequence'::regclass),
    name text,
    CONSTRAINT test_table_pkey PRIMARY KEY (id)
);

插入大量数据后，序列因CYCLE属性循环回到起始值，若旧记录未删除，插入时会触发duplicate key violates unique constraint错误。原本误以为nextval会自动选取空闲ID，实际并非如此。

受限于嵌入式设备存储空间，选择添加BEFORE INSERT触发器，循环调用nextval直到找到空闲ID的方案，现针对触发器代码寻求以下反馈：

1. 该锁定方案是否存在并发问题？
2. 是否有更优的触发器写法？
3. 此方案存在哪些潜在陷阱？

现有触发器实现代码 #

冲突处理函数

CREATE OR REPLACE FUNCTION fix_id_collision()
RETURNS TRIGGER AS $$
DECLARE
  found_id integer;
  column_default text;
BEGIN
  -- Loop until we find a free id value.
  LOOP
    -- Check if the id already exists in the table.
    -- Use row level "FOR UPDATE" locking to hopefully ensure
    -- that concurrent INSERT queries don't receive the same id
    -- and collide.
    EXECUTE format('SELECT id FROM %I.%I WHERE id = %L FOR UPDATE', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.id) INTO found_id;
    IF found_id IS NULL THEN
      RETURN NEW;
    END IF;
    EXECUTE format('SELECT column_default FROM
information_schema.columns WHERE table_schema=%L AND table_name=%L', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO column_default;
    EXECUTE format('SELECT %s', column_default) INTO NEW.id;
  END LOOP;
END;
$$
LANGUAGE plpgsql;

批量安装触发器函数

CREATE OR REPLACE FUNCTION install_id_collision_triggers()
RETURNS VOID
AS $$
DECLARE
  tbl_schema text;
  tbl_name text;
BEGIN
  FOR tbl_schema, tbl_name IN SELECT table_schema, table_name FROM information_schema.columns WHERE column_name='id'
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS id_collision_trigger_%s ON %I.%I', tbl_name, tbl_schema, tbl_name);
    EXECUTE format('CREATE TRIGGER id_collision_trigger_%s BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE FUNCTION fix_id_collision()', tbl_name, tbl_schema, tbl_name);
  END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT install_id_collision_triggers();

问题解答 #

1. 锁定方案的并发问题 #

当前实现的FOR UPDATE锁定仅针对已存在的ID行，如果多个并发请求同时拿到同一个不存在的ID，此时SELECT ... FOR UPDATE不会返回任何行，也不会加锁，就会导致多个请求都认为该ID可用，最终插入时触发唯一约束冲突。

此外，频繁的循环查询和锁操作会增加事务等待时间，在高并发场景下容易引发锁竞争，降低整体插入性能。

2. 更优的触发器写法优化 #

优化点1：避免重复查询information_schema

每次循环都查询information_schema.columns获取默认值是冗余操作，可以在触发器初始化时就获取序列名，避免重复IO：

CREATE OR REPLACE FUNCTION fix_id_collision()
RETURNS TRIGGER AS $$
DECLARE
  found_id integer;
  seq_name regclass;
BEGIN
  -- 仅在第一次循环时获取序列名称
  IF seq_name IS NULL THEN
    SELECT regexp_replace(column_default, '^nextval\(''(.*)''::regclass\)$', '\1')::regclass
    INTO seq_name
    FROM information_schema.columns
    WHERE table_schema = TG_TABLE_SCHEMA
      AND table_name = TG_TABLE_NAME
      AND column_name = 'id';
  END IF;

  LOOP
    -- 直接使用主键索引检查存在性，避免不必要的行锁
    PERFORM 1 FROM ONLY %I.%I WHERE id = NEW.id;
    IF NOT FOUND THEN
      RETURN NEW;
    END IF;
    -- 直接调用nextval获取新ID，避免动态SQL解析
    NEW.id := nextval(seq_name);
  END LOOP;
END;
$$
LANGUAGE plpgsql;

优化点2：批量安装函数的筛选逻辑

原批量安装脚本会给所有带id列的表加触发器，建议增加筛选条件，仅针对使用循环序列的表，避免误触发：

CREATE OR REPLACE FUNCTION install_id_collision_triggers()
RETURNS VOID
AS $$
DECLARE
  tbl_schema text;
  tbl_name text;
BEGIN
  FOR tbl_schema, tbl_name IN 
    SELECT c.table_schema, c.table_name
    FROM information_schema.columns c
    JOIN information_schema.sequences s 
      ON c.column_default LIKE '%nextval(''' || s.sequence_schema || '.' || s.sequence_name || '''::regclass)%'
    WHERE c.column_name = 'id'
      AND s.cycle_option = 'YES'
  LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS id_collision_trigger_%s ON %I.%I', tbl_name, tbl_schema, tbl_name);
    EXECUTE format('CREATE TRIGGER id_collision_trigger_%s BEFORE INSERT ON %I.%I FOR EACH ROW EXECUTE FUNCTION fix_id_collision()', tbl_name, tbl_schema, tbl_name);
  END LOOP;
END;
$$
LANGUAGE plpgsql;

3. 潜在陷阱 #

• 性能损耗：当序列循环后，空闲ID稀疏时，触发器会频繁调用nextval和查询ID存在性，插入性能大幅下降，嵌入式设备资源有限，这个问题会更突出。
• 死锁风险：如果多个事务同时锁定不同的已存在ID行，在循环获取新ID的过程中可能引发循环等待，导致死锁。
• ID排序失效：由于ID循环复用，无法再依赖ID字段判断插入顺序，业务逻辑如果依赖ID排序会出现错误。
• 序列“跳号”加剧：每次冲突都会多调用一次nextval，导致序列号跳号更严重，进一步加快序列循环的频率。
• 事务阻塞：长时间的循环会拉长事务执行时间，占用数据库连接资源，在嵌入式设备的小连接池场景下容易导致连接耗尽。

已知补充：后续测试发现bigint与integer空间差异极小，不推荐此方案，但仍需针对已选方案的实现进行优化。

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