PostgreSQL中INSERT ON CONFLICT结合部分索引偶发失效问题问询

你遇到的是PostgreSQL 13.9版本中，预处理语句结合部分唯一索引使用时，当ON CONFLICT的WHERE子句使用参数而非常量，会出现偶发的约束匹配失败错误。

问题重现 #

创建测试表和部分唯一索引：

CREATE TABLE IF NOT EXISTS test
(
    type character varying,
    id   integer
);

CREATE UNIQUE INDEX IF NOT EXISTS uniq_id_test 
    ON test USING btree (type, id) 
    WHERE (type = 'Test');

定义带参数化ON CONFLICT WHERE的预处理语句：

PREPARE test (text, int, text) AS
    INSERT INTO test (type, id)
    VALUES ($1, $2)
    ON CONFLICT (type, id)
    WHERE type = $3 DO
    UPDATE
    SET id = EXCLUDED.id;

连续执行6次：

EXECUTE test('Test', 1, 'Test');
EXECUTE test('Test', 2, 'Test');
EXECUTE test('Test', 3, 'Test');
EXECUTE test('Test', 4, 'Test');
EXECUTE test('Test', 5, 'Test');
EXECUTE test('Test', 6, 'Test'); -- 此处抛出错误

错误信息：

[42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

销毁预处理语句后重建，会重复“成功5次→第6次报错”的规律；但将ON CONFLICT WHERE中的$3替换为常量'Test'，所有执行均正常。

深层原因解析 #

这个问题的核心是PostgreSQL的计划缓存机制与部分索引匹配逻辑的冲突：

1. 预处理语句的计划切换规则
   PostgreSQL对预处理语句会生成两种执行计划：

   • 定制计划：每次执行时根据传入的参数值生成针对性的计划
   • 通用计划：一次生成后复用的通用计划，不依赖具体参数值
     默认情况下，预处理语句执行5次后，会自动切换为使用通用计划（可通过plan_cache_mode参数调整）。

2. 部分索引的匹配要求
   部分索引（带WHERE谓词的索引）要被ON CONFLICT子句识别，要求ON CONFLICT的WHERE条件必须与索引的谓词完全匹配，且这个匹配必须在计划生成阶段确定。

3. 参数化WHERE子句的问题
   当ON CONFLICT WHERE使用参数$3时：

   • 前5次执行使用定制计划：每次执行时PostgreSQL会检查参数值（'Test'）是否匹配部分索引的谓词，此时能正确关联到uniq_id_test索引，执行成功。
   • 第6次切换为通用计划：通用计划在生成时无法确定$3的具体值，因此不会绑定到任何特定的部分索引。当后续执行传入'Test'时，通用计划无法找到匹配的唯一约束，就会抛出错误。

   而如果ON CONFLICT WHERE用常量'Test'，计划生成阶段就能明确匹配到uniq_id_test索引，通用计划也会绑定该索引，因此所有执行都正常。

解决方案 #

1. 改用常量WHERE条件（最直接）：
   如你已经尝试的，将ON CONFLICT WHERE中的参数替换为与部分索引谓词一致的常量：

   PREPARE test (text, int, text) AS
       INSERT INTO test (type, id)
       VALUES ($1, $2)
       ON CONFLICT (type, id)
       WHERE type = 'Test' DO
       UPDATE
       SET id = EXCLUDED.id;
   

2. 强制使用定制计划：
   如果必须保留参数化逻辑，可以在预处理语句中指定force_custom_plan，强制每次执行都生成定制计划：

   PREPARE test (text, int, text) AS
       INSERT INTO test (type, id)
       VALUES ($1, $2)
       ON CONFLICT (type, id)
       WHERE type = $3 DO
       UPDATE
       SET id = EXCLUDED.id
   WITH (plan_cache_mode = force_custom_plan);
   

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