向campaign_result表插入数据时主键冲突的原因排查

问题背景 #

我创建了public.campaign_result表，表结构如下：

CREATE TABLE IF NOT EXISTS public.campaign_result
(
    id integer NOT NULL DEFAULT nextval('campaign_result_id_seq'::regclass),
    state result_state NOT NULL DEFAULT 'processing'::result_state,
    data json NOT NULL,
    response json,
    act result_action NOT NULL DEFAULT 'none'::result_action,
    message character varying COLLATE pg_catalog."default",
    campaign_id uuid NOT NULL,
    created_at timestamp with time zone DEFAULT now(),

    CONSTRAINT campaign_result_pkey PRIMARY KEY (id),

    CONSTRAINT campaign_result_campaign_id_fkey 
        FOREIGN KEY (campaign_id)
        REFERENCES public.campaign (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

目前表中已有近1200条数据，但执行插入函数时触发了主键冲突错误：

Failed due to duplicate key value violates unique constraint "campaign_result_pkey"
DETAIL: Key (id)=(137) already exists.

插入函数的Python代码如下：

def campaing_result_insert(item, messsage, campaign_id):
    conn = connect_db()
    with conn:
        cur = conn.cursor()
        cur.execute("""INSERT INTO campaign_result(data, Campaign_id, message) VALUES (%s::json, %s, %s) RETURNING id, data;""",
                    (item, campaign_id, messsage))

        return cur.fetchone()

请问这个主键冲突的原因是什么？

原因分析 #

问题核心是生成id的序列campaign_result_id_seq当前值落后于表中已有的最大id。PostgreSQL默认依赖这个序列自动生成主键值，但现在序列生成的下一个值是137，而该id已经被表中现有数据占用，因此插入时触发唯一约束冲突。

常见触发场景：

• 曾经手动插入过指定id的记录（比如直接执行INSERT INTO campaign_result(id, ...) VALUES(137, ...)），这类操作不会自动更新序列的当前值，导致序列“不知道”已有更大的id存在。
• 表数据是从备份、其他数据库导入的，或者序列被手动重置过，未同步将序列值更新到表中最大id的位置。
• 极端情况下，事务回滚会导致序列值不会回退，但这种情况一般不会让序列值大幅落后于实际数据。

验证步骤 #

可以运行以下SQL确认问题：

-- 查看序列的下一个生成值
SELECT nextval('campaign_result_id_seq');
-- 查看表中现有的最大id
SELECT MAX(id) FROM public.campaign_result;

如果第一条SQL返回的数值小于第二条的结果，即可确认是序列值落后导致的冲突。

修复方法 #

将序列的当前值更新为表中最大id加1，确保后续生成的id不会重复：

SELECT setval('campaign_result_id_seq', (SELECT MAX(id) FROM public.campaign_result) + 1);

执行这条SQL后，再运行插入函数就不会报错了。

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