Postgres唯一索引偶发失效：不区分大小写用户名重复插入问题

唯一索引未生效的可能原因 #

• 索引处于无效（INVALID）状态
  如果你当初创建索引时使用了CONCURRENTLY参数但中途执行失败，或者曾经执行过ALTER TABLE accounts DISABLE TRIGGER ALL之类的操作禁用过表上的所有触发器（唯一校验本质依赖系统触发器实现），都会导致索引被标记为无效。无效索引不会参与写入时的唯一性校验，重复数据可以正常插入。
  可以执行以下语句验证索引状态：

  SELECT indisvalid FROM pg_index WHERE indexrelid = 'accounts_lower_idx'::regclass;
  

  如果返回结果为f就说明索引已失效，是问题的根源。
• 存在字符编码映射问题
  部分Unicode特殊字符的大小写映射规则可能和你预期不一致，比如部分生僻字符、全角半角字符的lower()转换结果可能存在差异，导致你肉眼看起来重复的用户名实际lower()转换结果不同，不过这种情况你新建同规则索引时也会报错的话，概率相对较低。
• 已知版本bug
  PostgreSQL 10.x版本存在少量和表达式唯一索引校验相关的已知bug，极端场景下可能出现校验漏判，不过触发概率极低，优先排查前两种情况。

关于表达式唯一索引的实践合理性 #

用表达式唯一索引实现不区分大小写的用户名唯一性校验不属于不良实践，反而属于PostgreSQL官方推荐的标准实现方式。
唯一约束和唯一索引底层实现完全一致，唯一约束只是在系统表中多了一条约束元数据记录，本质还是依赖唯一索引完成校验。你如果偏好约束的语义，也可以将现有索引绑定为约束：

ALTER TABLE accounts ADD CONSTRAINT accounts_lower_unique UNIQUE USING INDEX accounts_lower_idx;

两种方式在正确性、性能上没有任何差异。

修复步骤 #

1. 先查询所有重复数据处理干净：

SELECT lower(account_name::text), array_agg(id), array_agg(account_name) 
FROM accounts 
GROUP BY lower(account_name::text) 
HAVING count(*) > 1;

2. 如果确认索引无效，删除失效索引后重建：

-- 非生产环境可以直接建
DROP INDEX IF EXISTS accounts_lower_idx;
CREATE UNIQUE INDEX accounts_lower_idx ON public.accounts USING btree (lower((account_name)::text));

-- 生产环境避免锁表可以用CONCURRENTLY，注意执行期间不能有DDL操作
DROP INDEX IF EXISTS accounts_lower_idx;
CREATE UNIQUE INDEX CONCURRENTLY accounts_lower_idx ON public.accounts USING btree (lower((account_name)::text));

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