PostgreSQL含WHERE NOT IN的INSERT为何触发唯一约束冲突？

背景梳理 #

现有两张PostgreSQL表：

• myschema.posted_url：存储URL的所有出现记录
• myschema.unique_urls：通过MD5唯一索引保证URL唯一性

简化DDL如下：

create table myschema.posted_url (
    record_id serial primary key,
    url varchar not null
);

create table myschema.unique_urls (
    record_id serial primary key,
    url varchar not null,
    record_added timestamptz not null default now()
);

create unique index unique_urls_md5 on myschema.unique_urls (md5(url));

单Python进程操作流程：添加单条URL到posted_url → 执行批量INSERT更新unique_urls → 提交事务 → 处理下一个实体。执行的INSERT语句：

insert into myschema.unique_urls (url) (
    select distinct url from myschema.posted_url where url not in (
        select url from myschema.unique_urls
    )
);

某次执行报错：

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "unique_urls_md5"
DETAIL: Key (md5(url::text))=(<myurlmd5hash>) already exists.

异常现象：posted_url对应记录未提交，但unique_urls中该记录已提交（时间戳为崩溃前），次日进程重启后才提交posted_url的记录。

针对你的疑问逐一分析：

疑问1：单进程下为何触发冲突？ #

已排除其他进程的情况下，可能的触发场景有三个：

1. MD5哈希碰撞：概率极低，但数据量达数百万级时会出现——两个不同URL生成了完全相同的MD5值。此时子查询NOT IN会认为当前URL不在unique_urls中（字符串不匹配），但插入时MD5索引检测到已有相同哈希值的记录，触发约束冲突。
2. URL隐式字符差异：比如URL存在全角/半角空格、大小写不一致、不可见控制字符等，这些字符串在业务上视为同一URL，但数据库严格按字节匹配，导致NOT IN判断当前URL不在unique_urls中，若恰好有另一个URL的MD5与它碰撞，就会触发冲突。
3. 事务控制漏洞：若代码中存在隐式事务拆分——比如添加posted_url后意外提交事务，再执行INSERT时开启新事务，进程崩溃就可能出现unique_urls提交但posted_url未提交的情况，需检查代码是否开启自动提交或中间调用了commit()。

疑问2：单事务INSERT为何出现"查询无冲突但插入冲突"？ #

核心原因是PostgreSQL唯一约束检查时机与快照隔离的差异：

• INSERT语句的子查询在执行初期读取事务快照（READ COMMITTED隔离级别下为语句级快照），此时仅能看到事务内已完成的修改，无法感知其他事务的操作。
• 唯一约束的检查是在插入操作的最后阶段，会检查表的最新状态。如果存在两个字符串不同但MD5相同的URL，子查询会因字符串不匹配允许插入，但插入时MD5索引检测到重复，就会触发冲突——这完全符合你遇到的现象。

另外，NOT IN的逻辑缺陷（如遇到NULL返回空结果）可排除，因为你的DDL中url字段为NOT NULL。

解决办法 #

1. 替换低效且易出问题的批量INSERT #

原语句每次全表扫描两张表，数百万数据下效率极低，还容易触发冲突。改用INSERT ... ON CONFLICT语法，针对单条URL操作，直接跳过重复项：

INSERT INTO myschema.unique_urls (url)
VALUES (%s)
ON CONFLICT (md5(url)) DO NOTHING;

2. 优化业务流程 #

将添加posted_url和插入unique_urls放在同一个事务中，用单条插入替代批量扫描，避免全表开销：

import psycopg2

conn = psycopg2.connect("your_connection_string")
cur = conn.cursor()

try:
    # 插入posted_url
    cur.execute("INSERT INTO myschema.posted_url (url) VALUES (%s)", (current_url,))
    # 插入unique_urls，冲突则跳过
    cur.execute("""
        INSERT INTO myschema.unique_urls (url)
        VALUES (%s)
        ON CONFLICT (md5(url)) DO NOTHING;
    """, (current_url,))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Error: {e}")
finally:
    cur.close()
    conn.close()

3. 排查哈希碰撞（可选） #

若怀疑是MD5碰撞，执行以下SQL查询对应MD5的所有记录：

SELECT url FROM myschema.unique_urls WHERE md5(url) = '<myurlmd5hash>';

若返回多个不同URL，说明确实发生碰撞，可改用SHA-256创建唯一索引：

CREATE UNIQUE INDEX unique_urls_sha256 ON myschema.unique_urls (digest(url, 'sha256'));

或直接在url字段上创建唯一约束（URL长度需在PostgreSQL允许范围内，一般为2712字节以内）：

ALTER TABLE myschema.unique_urls ADD CONSTRAINT unique_urls_url UNIQUE (url);

4. 检查事务控制 #

确认代码中无隐式提交，两个操作必须在同一个事务内完成，避免开启autocommit模式或在操作中间调用conn.commit()。

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