记录一次排查PostgreSQL违反唯一性限制的过程

DaiLu
  2025-09-06 21:28:13 2025-09-06 21:28:13 Created   2025-09-10 21:59:51 2025-09-10 21:59:51 Updated      3k Words  13 Mins  
  • Bug的现象：
      当时，主要的目标是通过Docker搭建容器pg-conf，该容器的CMD主要运行了一个python脚本pg-conf-start.py，同时设置了若容器启动失败则自动重启的选项，该python脚本的主要功能是创建数据库db1，然后通过执行提前写好的table_create.sql脚本创建表sys_user，最后，在上述基础上执行table_insert.sql脚本向表sys_user插入几行预定义的数据，pg-conf-start.py、table_create.sql和table_insert.sql脚本的片段，如下所示。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
# just part of code, can't execute directly.
import psycopg2

SQL_FILE_A="/usr/local/pg-conf/table_create.sql"
SQL_FILE_B="/usr/local/pg-conf/table_insert.sql"

DB_NAME = "db1"
DB_USER = "postgres"
DB_PASSWORD = "xxxxxx"
DB_HOST = "pg-conf"
DB_PORT = 5432

def connect(dbname, autocommit=False):
    conn = psycopg2.connect(
        dbname=dbname,
        user=DB_USER,
        password=DB_PASSWORD,
        host=DB_HOST,
        port=DB_PORT
    )
    conn.autocommit = autocommit
    return conn

def create_database_if_needed():
    # 连接到系统默认数据库（不能连接 DB_NAME 因为可能还没创建）
    conn = connect("postgres", autocommit=True)
    try:
        with conn.cursor() as cur:
            cur.execute("SELECT 1 FROM pg_database WHERE datname = %s", (DB_NAME,))
            exists = cur.fetchone()
            if not exists:
                logger.info(f"数据库 {DB_NAME} 不存在，正在创建...")
                cur.execute(f'CREATE DATABASE "{DB_NAME}";')  # 加引号防止大小写问题
            else:
                logger.info(f"数据库 {DB_NAME} 已存在，跳过创建")
    finally:
        conn.close()

def run_sql_file(conn, filepath):
    if not os.path.exists(filepath):
        raise FileNotFoundError(f"SQL 文件不存在: {filepath}")
    with open(filepath, "r", encoding="utf-8") as f:
        sql = f.read()
    with conn.cursor() as cur:
        cur.execute(sql)
    conn.commit()

def ensure_migration_table(conn):
    with conn.cursor() as cur:
        cur.execute("""
        CREATE TABLE IF NOT EXISTS __migration_history (
            name TEXT PRIMARY KEY,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """)
    conn.commit()

def has_run(conn, name):
    with conn.cursor() as cur:
        cur.execute("SELECT 1 FROM __migration_history WHERE name = %s", (name,))
        return cur.fetchone() is not None

def mark_as_run(conn, name):
    with conn.cursor() as cur:
        cur.execute("INSERT INTO __migration_history (name) VALUES (%s)", (name,))
    conn.commit()

def init_postgres():
    create_database_if_needed()

    conn = connect(DB_NAME)
    try:
        logger.info("执行 a.sql ...")
        run_sql_file(conn, SQL_FILE_A)

        ensure_migration_table(conn)

        if not has_run(conn, "b.sql"):
            logger.info("执行 b.sql ...")
            run_sql_file(conn, SQL_FILE_B)
            mark_as_run(conn, "b.sql")
        else:
            logger.info("b.sql 已执行过，跳过")
    finally:
        conn.close()

    logger.info("初始化完成")

if __name__ == "__main__":
    init_postgres()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- table_create.sql
-- public.sys_user definition

-- Drop table

-- DROP TABLE public.sys_user;

CREATE TABLE IF NOT EXISTS public.sys_user
(
    id                   bigserial    NOT NULL,
    created_at           timestamptz  NULL     DEFAULT now(),
    updated_at           timestamptz  NULL     DEFAULT now(),
    username             varchar(36)  NOT NULL,           -- 用户名
    "password"           varchar(200) NOT NULL,           -- 密码
    roles                varchar(36)  NOT NULL,           -- 角色
    description          varchar(255) NULL,               -- 用户描述
    login_type           int2         NOT NULL DEFAULT 1, -- 登录方式，1 密码登录/ 2 radius登录/ 3 双因子登录
    "type"               int2         NOT NULL DEFAULT 1, -- 用户类型0-内置用户 1-自定义用户 2-API用户
    status               int2         NOT NULL DEFAULT 1, -- 锁定状态 1 正常 2 锁定
    error_count          int2         NOT NULL DEFAULT 0, -- 登录错误次数
    pwd_exp              int2         NOT NULL DEFAULT 1, -- 密码是否过期
    block_time           timestamptz  NULL,               -- 锁定时间
    last_login_time      timestamptz  NULL,               -- 最后登录时间
    last_login_ip        varchar(150) NULL,               -- 最后登录ip
    last_change_pwd_time timestamptz  NULL,               -- 最后修改密码时间
    history_password     text         NULL,               -- 历史密码
    radius_name          varchar(150) NULL,               -- Radius服务器名称
    otp_id               text         NULL,               -- OPT序列号
    access_token         varchar      NULL,
    access_token_exp     bigint       NOT NULL DEFAULT 0,
    CONSTRAINT sys_user_pkey PRIMARY KEY (id)
);
ALTER TABLE public.sys_user
    ADD COLUMN IF NOT EXISTS pwd_exp int2 DEFAULT 1; --升级兼容
ALTER TABLE public.sys_user
    ALTER COLUMN "password" DROP NOT NULL; --允许密码为空
CREATE UNIQUE INDEX IF NOT EXISTS sys_user_username_idx ON public.sys_user USING btree (username);
COMMENT ON TABLE public.sys_user IS '用户表';
-- Column comments

COMMENT ON COLUMN public.sys_user.username IS '用户名';
COMMENT ON COLUMN public.sys_user."password" IS '密码';
COMMENT ON COLUMN public.sys_user.roles IS '角色';
COMMENT ON COLUMN public.sys_user.description IS '用户描述';
COMMENT ON COLUMN public.sys_user.login_type IS '登录方式，1 密码登录/ 2 radius登录/ 3 双因子登录';
COMMENT ON COLUMN public.sys_user."type" IS '用户类型0-内置用户 1-自定义用户 2-API用户';
COMMENT ON COLUMN public.sys_user.status IS '锁定状态 1 正常 2 锁定';
COMMENT ON COLUMN public.sys_user.error_count IS '登录错误次数';
COMMENT ON COLUMN public.sys_user.block_time IS '锁定时间';
COMMENT ON COLUMN public.sys_user.last_login_time IS '最后登录时间';
COMMENT ON COLUMN public.sys_user.last_login_ip IS '最后登录ip';
COMMENT ON COLUMN public.sys_user.last_change_pwd_time IS '最后修改密码时间';
COMMENT ON COLUMN public.sys_user.history_password IS '历史密码';
COMMENT ON COLUMN public.sys_user.radius_name IS 'Radius服务器名称';
1
2
3
4
5
6
7
8
9
10
-- table_insert.sql
INSERT INTO public.sys_user (id, username, "password", roles, description, login_type, "type", status)
VALUES (1, 'adm', 'fe5603e812a2097b0013006b805199fd950a06acb88dbd6a397500cba7bb0dab', 'adm', '', 1, 1, 1),
       (2, 'admin', 'fe5603e812a2097b0013006b805199fd950a06acb88dbd6a397500cba7bb0dab', 'admin', '', 1, 1, 1),
       (3, 'audit', 'fe5603e812a2097b0013006b805199fd950a06acb88dbd6a397500cba7bb0dab', 'audit', '', 1, 1, 1);
-- 内置用户，角色 super/首次登录不改密码/密码永不过期
INSERT INTO public.sys_user (username, "password", roles, description, login_type, "type", status, last_change_pwd_time,pwd_exp)
VALUES ('__internal', 'fe5603e812a2097b0013006b805199fd950a06acb88dbd6a397500cba7bb0dab', 'super', '', 1, 3, 1, now(),2);
-- 更新序列（使用自增ID需要）
SELECT setval('sys_user_id_seq', (SELECT MAX(id) FROM sys_user));

  Bug的现象是容器pg-conf能够启动，但是使用docker logs查看日志会发现日志中存在报错，且容器重启了几次，报错记录如下所示。报错提示违反了唯一性原则，出现了重复的key，但是每次重启重复的key都是不一样的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
2025-09-05 14:15:55,792 - INFO - 数据库 wag 已存在，跳过创建
2025-09-05 14:15:55,806 - INFO - 执行 a.sql ...
2025-09-05 14:15:57,022 - INFO - 执行 b.sql ...
  File "/usr/local/postgres/script/pg-conf-start.py", line 116, in init_postgres
    run_sql_file(conn, SQL_FILE_B)
  File "/usr/local/postgres/script/pg-conf-start.py", line 79, in run_sql_file
    cur.execute(sql)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "sys_user_pkey"
DETAIL:  Key (id)=(1) already exists.

2025-09-05 14:15:59,925 - INFO - 数据库 wag 已存在，跳过创建
2025-09-05 14:15:59,938 - INFO - 执行 a.sql ...
2025-09-05 14:16:00,066 - INFO - 执行 b.sql ...
  File "/usr/local/postgres/script/pg-conf-start.py", line 116, in init_postgres
    run_sql_file(conn, SQL_FILE_B)
  File "/usr/local/postgres/script/pg-conf-start.py", line 79, in run_sql_file
    cur.execute(sql)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "sys_user_pkey"
DETAIL:  Key (id)=(2) already exists.

2025-09-05 14:16:03,425 - INFO - 数据库 wag 已存在，跳过创建
2025-09-05 14:16:03,446 - INFO - 执行 a.sql ...
2025-09-05 14:16:03,532 - INFO - 执行 b.sql ...
Traceback (most recent call last):
  File "/usr/local/postgres/script/pg-conf-start.py", line 159, in <module>
    init_postgres()
  File "/usr/local/postgres/script/pg-conf-start.py", line 116, in init_postgres
    run_sql_file(conn, SQL_FILE_B)
  File "/usr/local/postgres/script/pg-conf-start.py", line 79, in run_sql_file
    cur.execute(sql)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "sys_user_pkey"
DETAIL:  Key (id)=(3) already exists.

2025-09-05 14:16:06,361 - INFO - 数据库 wag 已存在，跳过创建
2025-09-05 14:16:06,398 - INFO - 执行 a.sql ...
2025-09-05 14:16:06,512 - INFO - 执行 b.sql ...
2025-09-05 14:16:06,585 - INFO - 初始化完成

  由于换到了新的产线，我刚接触过PostgreSQL数据库和Docker不久，也没有使用过psycopg2，python和MySQL的知识基本也忘的一干二净了。当时遇到这个问题，主要有3个疑问：首先，为什么会报错？其次，为什么每次报错的Id都不相同？最后，为什么pg-conf容器最后又能够正常运行了，数据库的内容也正确？
  遇事不决问DeepSeek，我就把sql脚本和报错的现象丢给了DeepSeek，它给的回答大概是“可能之前插入了数据或者遗留重复的数据,需要使用select * from sys_user;查看是否存在数据…，然后给我一些不会报错的解决方案”等等，具体情况记不清了。但是，我就是想整明白上面的问题。
  我通过使用select * from sys_user;查看在执行table_insert.sql前确实是没有数据的，但是就是不明白我什么会报错，当时认为是由于执行id=1的时候报的错，因此就非常矛盾。
  在此基础上，我当时想的是如果在插入前能够清空表的内容并重置主键的值是不是就不会报这个问题了，然后问了DeepSeek什么命令能够实现上面的任务，得到如下答案：

1
2
-- 清空表并重置主键的值
TRUNCATE TABLE your_table_name RESTART IDENTITY;

  同时，我又问了DeepSeek如何查看当前主键的值是什么，然后它给了我一堆答案，提出出的有效信息是SELECT nextval('sys_user_id_seq');可以获取下一个主键的值，但是会消耗一个主键的值；SELECT last_value FROM sys_user_id_seq;可以获取当前主键的值。后来才明白，创建sys_usr表的sql脚本中CONSTRAINT sys_user_pkey PRIMARY KEY (id)会值自动创建一个名为sys_user_pkey的表记录当前的主键的。我通过执行上述命令，发现清空表并重置自增主键的值后，当前主键的值为1，此时为认为这个值的含义为下一次插入记录是使用的主键的值。然后我执行如下sql命令后，在此查询当前主键的值，发现还是为1。此时就很奇怪，为什么还是1呢？应该变为2才对。

1
2
3
INSERT INTO public.sys_user (username, "password", roles, description, login_type, "type", status)
VALUES ('adm', 'fe5603e812a2097b0013006b805199fd950a06acb88dbd6a397500cba7bb0dab', 'adm', '', 1, 1, 1)

  后来询问DeepSeek明白了：SELECT last_value FROM sys_user_id_seq;打印的是上次使用的值，但是初始化是就是1，同时还有一个标识位is_called表示是否调用了nextval，当自增主键被重置时，is_called=f，此时插入新数据会nextval仍然会返回1，但此时会将is_called置为t，下次调用就会返回2。查看当前自增主键的值和is_called的值，可以通过如下命令：

1
SELECT last_value, is_called FROM sys_user_id_seq;

  得到上述信息之后，我便开始重新测试，将table_insert.sql进一步缩小，如下所示，看看执行完成后主键的值的变化，同时观察是否会报错。

1
2
3
-- table_insert.sql
INSERT INTO public.sys_user (id, username, "password", roles, description, login_type, "type", status)
VALUES (1, 'adm', 'fe5603e812a2097b0013006b805199fd950a06acb88dbd6a397500cba7bb0dab', 'adm', '', 1, 1, 1)

  此时，我发现执行过程并没有报错，同时主键的值也并没有增加，同时is_called仍然为f，因此，可以手动指定主键的值，但是自增主键的值并不会变化。此时把id为1，2，3的三条都加上也没有报错。直到执行下面的sql语句时报错了。

1
2
INSERT INTO public.sys_user (username, "password", roles, description, login_type, "type", status, last_change_pwd_time,pwd_exp)
VALUES ('__internal', 'fe5603e812a2097b0013006b805199fd950a06acb88dbd6a397500cba7bb0dab', 'super', '', 1, 3, 1, now(),2);

  此时，基本已经明白了，由于该语句没有指定id的值，因此会使用nextval获取下一个自增主键的值，此时获取的值为1，但是id为1的记录已经包含了，因此就会报如下错误：

1
2
psql:data.1.sql:6: ERROR:  duplicate key value violates unique constraint "sys_role_pkey"
DETAIL:  Key (id)=(adm) already exists.

  为什么报错的原因找到了？但是为什么Docker容器自动重启了几次python脚本每次报错的id均不同，并且最后可以正常运行了呢？
  通过对python脚本分析发现，在使用psycopg2扩展库的时候，可以指定是否自动提交，由脚本代码看，当不指定自动提交时，只有手动commit才能提交事务，一旦cur.execute(sql)出错，那么该事务就会被回滚，因此当执行插入username为__internal的记录时出错，之前插入的id为1~3的记录都会被回滚，但是询问DeepSeek可知，自增主键每次执行nextval是不会回滚的，主要是为了提高执行效率、避免死锁等原因。因此，每次Docker容器重新执行python脚本时，自增主键就会加1，所以才会造成每次报错的id是不同的，当自增主键增加至3时，再次执行python脚本，就会执行成功，因此最后该python脚本的执行是成功的！
  挺神奇的！哈哈，最初看到这个报错的时候，感觉挺匪夷所思的，但是，通过不断地排查Bug，最终这个报错发现确实非常符合逻辑。

  • Title: 记录一次排查PostgreSQL违反唯一性限制的过程
  • Author: DaiLu
  • Created at : 2025-09-06 21:28:13
  • Updated at : 2025-09-10 21:59:51
  • Link: https://dailu-blogspot.com/2025/09/06/记录一次排查PostgreSQL违反唯一性限制的过程/
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐阅读
second_article second_article 记录一次搭建kafka集群的过程 记录一次搭建kafka集群的过程
推荐阅读
second_article second_article 记录一次搭建kafka集群的过程 记录一次搭建kafka集群的过程
Comments
On this page
记录一次排查PostgreSQL违反唯一性限制的过程