PostgreSQL基于LOWER(col)唯一索引并发冲突问题求解

第一个错误：修改唯一索引为LOWER("Id")后报idx_test_table_id冲突 #

• 核心原因：ON CONFLICT的冲突目标必须和要命中的唯一索引表达式完全一致才能触发UPSERT逻辑。
• 此时你的表存在两个唯一约束：
  1. 主键约束test_table_pkey：校验原始"Id"字段唯一
  2. 自定义唯一索引idx_test_table_id：校验LOWER("Id")的计算结果唯一
• 你函数中ON CONFLICT ("Id")只匹配主键约束的冲突，无法命中LOWER("Id")的唯一索引冲突。高并发下多个请求同时插入相同Id时，所有请求都先判定主键无冲突可以插入，随后校验LOWER("Id")唯一索引时发现重复，因为没有对应的ON CONFLICT规则处理，就直接抛出该错误。

第二个错误：修改ON CONFLICT为(LOWER("Id"))后报test_table_pkey冲突 #

• 核心原因：此时ON CONFLICT仅匹配LOWER("Id")的唯一索引冲突，没有覆盖主键约束的冲突场景。
• 你的测试代码用的是完全相同的原始Id字符串发起请求，高并发下多个请求同时判定LOWER("Id")无冲突可以插入，随后校验主键约束时发现原始"Id"值重复，没有对应的ON CONFLICT规则处理，因此抛出主键冲突的错误。

解决方案参考 #

如果要避免这类并发冲突，可根据业务需求选择方案：

1. 如果业务要求不区分Id大小写，建议直接把主键存储的值改为LOWER(Id)，不需要额外建立小写唯一索引，这样只用ON CONFLICT ("Id")就能覆盖所有冲突场景。
2. 如果必须保留原始Id作为主键同时要求小写值唯一，可在业务代码中捕获唯一冲突错误后重试，或者调整事务隔离级别为可序列化避免并发竞争。

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