PostgreSQL并发刷新物化视图时含NULL重复行未触发唯一键报错问题

核心原因 #

该现象是SQL标准中NULL的比较规则与PostgreSQL REFRESH MATERIALIZED VIEW CONCURRENTLY处理逻辑共同作用的结果。

1. SQL中NULL的比较特性 #

SQL标准明确规定：NULL值不与任何值相等，包括另一个NULL。即NULL = NULL的比较结果为UNKNOWN，而非TRUE。这意味着：

• 两行('test','test',NULL)不会被判定为完全相同的行，因为NULL的存在导致行的整体比较不成立。
• 但对于仅基于(a,b)的唯一索引而言，这两行的a和b均为非NULL且值完全相同，理论上应违反唯一约束——这里的关键在于CONCURRENTLY刷新的特殊处理逻辑。

2. REFRESH MATERIALIZED VIEW CONCURRENTLY的检查逻辑 #

当使用CONCURRENTLY刷新物化视图时，PostgreSQL会执行两步关键检查：

• 第一步：完全重复行检查
  系统会先检查新生成的数据集（即物化视图定义的SELECT结果）中是否存在所有列值完全相等的行：
  • 若所有列均为非NULL且存在重复行，会触发错误：new data for materailized view base_table_mv contains duplicate rows without any null columns——这是CONCURRENTLY为避免无法处理的歧义而做的检查。
  • 若重复行包含NULL值，由于NULL不被视为相等，这些行不会被判定为完全重复，因此不会触发上述错误。
• 第二步：唯一索引匹配处理
  CONCURRENTLY依赖物化视图上的唯一索引来识别行的变化，进行增量更新。当新数据中存在多行具有相同的唯一索引键（如(a,b)均为('test','test')）但其他列存在NULL时，系统无法通过唯一索引精准匹配到现有行，会将这些行视为独立的新行插入。

3. 唯一索引未触发错误的深层原因 #

虽然唯一索引的逻辑要求(a,b)相同的行不能共存，但REFRESH MATERIALIZED VIEW CONCURRENTLY在处理含NULL的行时，由于无法通过非索引列的NULL值确定行的唯一性，会绕过部分约束检查，导致重复行被插入而未触发预期的唯一键错误。这并非唯一索引本身的特性，而是CONCURRENTLY刷新机制与NULL比较规则结合后的特殊表现。

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