PostgreSQL：CREATE INDEX CONCURRENTLY置为INVALID的原因及锁相关咨询

首先，PostgreSQL文档中记载的会导致CREATE INDEX CONCURRENTLY（以下简称CIC）最终处于INVALID状态的原因包括：

• 死锁
• 触发statement_timeout
• 唯一索引出现唯一性冲突
• 表达式索引或部分索引出现表达式求值错误

针对提出的三个技术疑问，解答如下：

1. 是否存在更多未记载的原因？ #

是的，实际生产环境中还会遇到以下未被文档明确记载的场景，导致CIC生成的索引处于INVALID状态：

• 会话意外终止：执行CIC的客户端连接断开、会话崩溃或被强制终止，此时索引构建未完成，会留下INVALID状态的索引。
• 数据库节点故障：在CIC的两个执行阶段（扫描表构建索引、等待所有旧快照事务结束后标记索引有效）之间，发生主节点宕机、复制中断等故障，恢复后索引可能无法完成最终的有效性标记。
• 存储空间耗尽：构建索引过程中磁盘空间不足，导致索引写入失败，最终留下INVALID的索引对象。
• 权限变更：执行CIC期间，操作用户失去了目标表的读权限、索引创建权限，或表的所有者被变更，导致索引构建中途失败。
• 表结构意外变更：虽然CIC持有SHARE UPDATE EXCLUSIVE锁，但如果在第一阶段完成后、第二阶段开始前，有其他高优先级DDL（如DROP COLUMN、ALTER COLUMN TYPE）绕过锁限制完成了表结构变更（极罕见，通常是锁机制被异常突破），会导致索引无法完成有效性验证，变为INVALID。
• 后台进程异常：负责索引构建的辅助进程（如WAL发送进程、自动清理进程）出现故障，中断了CIC的执行流程。

2. 持有SHARE UPDATE EXCLUSIVE锁的CREATE INDEX CONCURRENTLY语句为何会陷入死锁？ #

死锁的核心是循环等待锁资源，即使CIC持有的是相对温和的SHARE UPDATE EXCLUSIVE（以下简称SUE）锁，也可能和其他会话形成锁等待闭环：

• SUE锁与ACCESS EXCLUSIVE、SHARE EXCLUSIVE、EXCLUSIVE锁互斥。当CIC持有表的SUE锁后，如果需要修改系统表（如pg_class）的元数据，会尝试获取系统表的行级锁；此时若有另一个会话正在执行需要ACCESS EXCLUSIVE锁的DDL（如ALTER TABLE ... RENAME COLUMN），该会话已经持有pg_class的行级锁，同时在等待目标表的ACCESS EXCLUSIVE锁——这就形成了循环等待：CIC等待系统表的行锁，DDL会话等待目标表的SUE锁，最终触发死锁。
• 极端情况下，多个CIC或其他持SUE锁的操作（如ANALYZE）也可能和其他锁操作形成复杂的等待链，触发死锁，但这种场景非常罕见。

3. 仅执行CREATE INDEX CONCURRENTLY时，是否需要设置lock_timeout？ #

即使只执行CIC，设置lock_timeout仍然是必要的，原因如下：

• 避免无限期占用资源：如果有其他会话长时间持有与SUE互斥的锁（比如一个卡住的ALTER TABLE事务），CIC会一直等待，占用数据库连接、会话内存等资源，甚至导致连接池耗尽。
• 规避数据变化带来的风险：等待锁的时间过长，目标表的数据量可能大幅增长，后续构建索引的时间会远超预期，甚至出现磁盘空间不足的问题；同时，长时间等待期间数据分布变化过大，即使最终完成索引构建，索引的统计信息也可能严重滞后。
• 快速反馈异常状态：设置合理的lock_timeout可以让CIC在等待超时后快速失败，及时发现数据库中存在的阻塞DDL，而不是无限期挂起。

你提到的ANALYZE被阻塞影响查询规划器只是其中一个场景，实际生产中资源占用和异常反馈的优先级更高。

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