PostgreSQL DETAIL: Key (id)=(423) already exists. 问题解决

一、问题

发现一些写入数据请求报错：

  File "/.env/python/dist-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 550, in _prepare_and_execute
    self._handle_exception(error)
  File "/.env/python/dist-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 501, in _handle_exception
    self._adapt_connection._handle_exception(error)
  File "/.env/python/dist-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 784, in _handle_exception
    raise translated_error from error
sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "ins_pk"
DETAIL:  Key (id)=(12) already exists.
[SQL: INSERT INTO ins (ins_id, domain, domain_uuid, ins_uuid) VALUES ($1::INTEGER, $2::VARCHAR, $3::VARCHAR, $4::VARCHAR) RETURNING ins.id]
[parameters: (0, 'string', 'string', 'string')]

二、成因分析

1. 并发
猜测，在并发的时候，可能会导致一些数据写入问题。这是一个未验证的猜测。

2. 自增ID导致的问题

后来在测试的时候发现，在新导入的数据，即使单次写入，也会出现这个问题。于是就否定了第一个猜测。观察发现，是因为导入的数据是ID不连续的，而似乎PG的自增ID并不是寻找到最大的而后再增加的。于是就问了下chatgpt，如何将自增ID设置为当然最大值+1。

问题的根源在于从SQL导入数据，或者有插入存在一些ID不连续的数据，导致的自增ID不连续。而PG不会自动处理这种ID。

三、解决方法

chatgpt给出了解决方法：

SELECT setval('scholars_id_seq', (SELECT MAX(id) FROM scholars));

你需要进入pg数据库执行这行SQL。

操作日志 ：

root@ubuntu:/# su - postgres
postgres@ubuntu:~$ psql -U postgres
Type "help" for help.
postgres=# \c http_log
You are now connected to database "http_log" as user "postgres".
http_log=# SELECT MAX(id) FROM domain;
 max  
------
 2724
(1 row)
http_log=# ^C
http_log=# SELECT setval('domain_id_seq', (SELECT MAX(id) FROM domain) + 1);
 setval 
--------
   2725
(1 row)
http_log=# 

也有其他人也遇到了同样的问题：

python - postgresql 插入时报错ID已存在 - SegmentFault 思否

在导出SQL的时候，可以直接插入一条语句直接解决这个问题。

添加重置ID自增：

echo "SELECT setval('domain_id_seq', (SELECT MAX(id) FROM domain) + 1);" >> domain_data.sql

注意修改对应的表名。