PostgreSQL 12中唯一约束失效异常问题排查

表结构说明 #

表的DDL如下：

create table market_post
( 
    .
    . 
    .
    d_id  varchar(20) constraint unique_d_id unique,
    .
    . 
    .
);
create index market_post_d_i_219a22_idx on market_post (d_id, is_deleted);

注：表在已存有大量数据时，通过ALTER语句添加了上述唯一约束和索引。

异常现象 #

d_id字段的唯一约束表现出不一致性：有时允许重复值，有时触发约束报错。

测试1 #

执行查询：

SELECT id,d_id
FROM public.market_post 
WHERE id in (1910764,2584556)

返回结果：

--------------------------------
|    id  |   d_id   |is_deleted|
--------------------------------
|1910764 | QYynk1fG | true    |
--------------------------------
|2584556 | gYkgfj_M | true    |
--------------------------------

执行更新语句：

UPDATE public.market_post SET d_id = 'gYkgfj_M'WHERE id = 1910764

执行结果：

[2022-07-24 10:31:52] 1 row affected in 116 ms

此时表中存在两行d_id重复的记录：

---------------------
|    id  |   d_id   |
---------------------
|1910764 | gYkgfj_M |
---------------------
|2584556 | gYkgfj_M |
---------------------

但执行以下查询仅返回一行：

SELECT id,d_id FROM public.market_post  WHERE d_id='gYkgfj_M'

查询结果：

---------------------
|    id  |   d_id   |
---------------------
|1910764 | gYkgfj_M |
---------------------

测试2 #

执行查询：

SELECT id,d_id
FROM public.market_post 
WHERE id in (191076 , 258455)

返回结果：

--------------------------------
|    id  |   d_id   |is_deleted|
--------------------------------
|191076 | SYyFk1fA  |  false    |
--------------------------------
|258455 | fYkDfjbb  |  false     |
--------------------------------

执行更新语句：

UPDATE public.market_post SET d_id = 'fYkDfjbb' WHERE id = 191076

触发唯一约束错误：

[23505] ERROR: duplicate key value violates unique constraint "unique_d_id" 
Detail: Key (d_id)=(fYkDfjbb) already exists.

可见唯一约束仅对is_deleted=false的行生效。

疑问 #

PostgreSQL的唯一约束为何失效？是否受联合索引影响？新表测试无此问题，仅存量数据的旧表异常，数据库版本为12。

问题分析与解决 #

核心原因推测 #

最可能的原因是唯一约束基于部分索引实现：若添加约束时，实际使用了仅包含is_deleted=false行的唯一索引作为支撑，那么约束只会对未删除的行生效，已删除行的d_id重复不会触发检查。此外，索引损坏也可能导致查询与约束检查的结果不一致。

排查与解决步骤 #

1. 验证唯一约束的支撑索引类型
   执行以下查询，查看约束对应的索引是否为部分索引：

   SELECT idx.indexname, idx.indexdef
   FROM pg_index idx
   JOIN pg_class cls ON idx.indexrelid = cls.oid
   JOIN pg_constraint con ON con.conindid = cls.oid
   WHERE con.conname = 'unique_d_id';
   

   若indexdef中包含WHERE is_deleted=false，则确认是部分唯一约束导致的问题。

2. 替换为全局唯一约束（若需求为全局唯一）
   若需要d_id在全表范围内唯一，需先处理现有重复数据，再重建约束：

   • 先查询全表的d_id重复情况：

     SELECT d_id, COUNT(*)
     FROM market_post
     GROUP BY d_id
     HAVING COUNT(*) > 1;
     

   • 处理重复数据（如修改重复的d_id或删除冗余行）后，删除现有约束并重建全局唯一约束：

     ALTER TABLE market_post DROP CONSTRAINT unique_d_id;
     DROP INDEX IF EXISTS unique_d_id;
     ALTER TABLE market_post ADD CONSTRAINT unique_d_id UNIQUE (d_id);
     

3. 重建索引排查损坏问题
   若索引存在损坏，执行以下语句重建相关索引：

   REINDEX INDEX unique_d_id;
   REINDEX INDEX market_post_d_i_219a22_idx;
   

   重建后再次测试更新和查询操作，验证问题是否解决。

4. 检查索引状态
   执行以下查询查看索引的使用状态，确认是否存在异常：

   SELECT * FROM pg_stat_user_indexes WHERE relname='market_post';
   

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