PostgreSQL多线程插入SERIAL类型引发序列重复错误及主键唯一性咨询

我在多线程环境下使用Java的java.sql库向PostgreSQL插入数据，依赖SERIAL类型自动分配主键（原本以为它线程安全），但出现了主键重复错误导致数据丢失，如何确保每个线程获取唯一ID？

错误信息 #

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pg_class_relname_nsp_index"
  Detail: Key (relname, relnamespace)=(table_id_seq, 2200) already exists.

相关代码 #

插入语句 #

insert into "+ MODEL_TABLE +" (b,c,d) values(?,?,?);

建表语句 #

CREATE_MODEL_TABLE =
            "CREATE TABLE if not exists "+ MODEL_TABLE +"(
" +
            "    id SERIAL,
" +
            "    a int,
" +
            "    b int,
" +
            "    c int,
" +
            "    PRIMARY KEY (id,b),
" +
            "  constraint unique_name_%s unique(b,c)"+
            ") PARTITION BY LIST (b);";

问题根源 #

这个错误不是SERIAL本身线程不安全，而是多线程同时执行CREATE TABLE if not exists时，并发创建了同名序列。SERIAL类型会自动生成表名_id_seq的序列，但CREATE TABLE if not exists对序列的创建没有原子性保障，多个线程同时执行时，会重复尝试创建同一个序列，触发pg_class_relname_nsp_index的唯一约束冲突。

解决办法 #

1. 提前初始化表和序列 #

不要在多线程插入流程中动态建表，而是在应用启动阶段用单线程执行建表语句，确保表和对应的序列只被创建一次。后续多线程插入时，序列已存在，不会触发冲突。

2. 显式创建序列并关联主键 #

如果必须动态建表，放弃SERIAL的隐式序列创建，改为显式控制序列的原子性创建：

-- 先原子性创建序列（不存在才创建）
CREATE SEQUENCE IF NOT EXISTS model_table_id_seq OWNED BY model_table.id;

-- 再创建表，手动关联序列
CREATE TABLE if not exists model_table(
    id INTEGER DEFAULT nextval('model_table_id_seq'),
    a int,
    b int,
    c int,
    PRIMARY KEY (id,b),
    constraint unique_name_%s unique(b,c)
) PARTITION BY LIST (b);

CREATE SEQUENCE IF NOT EXISTS是原子操作，多线程并发执行也不会重复创建序列。

3. 为分区分配独立序列（可选） #

由于你的表是按b字段的LIST分区表，高并发场景下可以为每个分区分配独立序列，避免单序列的性能瓶颈，但会增加代码复杂度，按需选择。

4. 捕获冲突重试（兜底方案） #

如果无法避免并发建表，可以捕获pg_class_relname_nsp_index的唯一约束异常，重试插入操作。但这只是临时兜底，不如提前初始化可靠。

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