怎样在PostgreSQL 16中排查物化视图锁定问题-SQL-PHP中文网

refresh materialized view 卡住时，应查 pg_stat_activity 与 pg_locks 联合视图，重点定位 state = 'active' 且 wait_event_type = 'lock' 的进程；若 blocking_query 为 ddl 或另一 refresh，则存在 ddl 冲突或串行刷新抢占，若 blocking_state = 'idle in transaction'，则大概率是长事务未提交锁住底层表。

怎样在postgresql 16中排查物化视图锁定问题

REFRESH MATERIALIZED VIEW 卡住时怎么定位阻塞源

直接查 pg_stat_activity 和 pg_locks 联合视图，重点看状态为 active 但 wait_event_type = 'Lock' 的进程。这类进程不是慢，是被锁住了。

执行以下查询快速抓出正在等锁的刷新任务：

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks l1 ON l1.pid = blocked.pid
JOIN pg_locks l2 ON l2.locktype = l1.locktype
  AND l2.database IS NOT DISTINCT FROM l1.database
  AND l2.relation IS NOT DISTINCT FROM l1.relation
  AND l2.pid != l1.pid
JOIN pg_stat_activity blocking ON blocking.pid = l2.pid
WHERE NOT l1.granted;

如果 blocking_query 是 ALTER TABLE、DROP MATERIALIZED VIEW 或另一个 REFRESH，说明有 DDL 冲突或串行刷新抢占
若 blocking_state 是 idle in transaction，大概率是某个长事务没提交，锁住了底层表
注意：普通刷新（不带 CONCURRENTLY）会持 AccessExclusiveLock，连 SELECT 都会被拦住；而 CONCURRENTLY 只持 ShareUpdateExclusiveLock，不影响读，但会卡住 DROP 或 ALTER

为什么 REFRESH CONCURRENTLY 报 “does not have a unique index”

这不是配置或权限问题，是硬性校验失败。PostgreSQL 16 在执行 REFRESH MATERIALIZED VIEW CONCURRENTLY 前，会严格检查物化视图上是否存在满足全部条件的唯一索引——缺一不可，否则直接报错并拒绝执行。

必须同时满足：

索引类型为 UNIQUE 或 PRIMARY KEY，且状态为 VALID（刚建完可能是 INVALID，需等一次 VACUUM 或 ANALYZE）
索引显式创建在物化视图上：CREATE UNIQUE INDEX ON mv_name (a, b)，基表上的主键或唯一约束不继承
所有索引列必须 NOT NULL；若源字段允许 NULL，得用 COALESCE(col, 0) 包裹后建索引
若物化视图含 GROUP BY a, b，索引必须覆盖全部分组列，只建 (a) 会导致比对逻辑错乱

常见误判：UNIQUE CONSTRAINT 不等于索引——某些迁移后场景下约束未触发隐式索引创建，必须手动 CREATE UNIQUE INDEX。

CONCURRENTLY 刷新期间 SELECT 返回“半新半旧”数据怎么办

这是设计使然，不是 bug。并发刷新本质是先生成新结果集，再逐行比对旧数据做 INSERT/UPDATE/DELETE，整个过程持续数秒到数分钟，中间任意时刻的 SELECT 都可能读到部分已更新、部分未更新的混合状态。

业务代码不能假设某次查询返回的是“某个时间点的完整快照”，必须接受最终一致性：

避免在关键事务中依赖物化视图做强一致性判断（比如资金核对、库存扣减）
若需强一致，要么改用普通刷新（接受停写窗口），要么直接查源表 + 显式加锁
聚合类物化视图尤其危险：SUM() 值可能在刷新中途跳变，导致报表数值抖动

别指望靠加 READ COMMITTED 或 REPEATABLE READ 隔离级别来“修复”——物化视图本身是物理表，它的行版本就是实时更新的，隔离级别只影响源表读取，不影响 MV 行的可见性逻辑。

刷新失败报 “could not lock updated tuple” 或 “duplicate key value violates unique constraint” 怎么办

这两个错误都发生在 CONCURRENTLY 刷新的 merge 阶段，说明业务写入和刷新操作在争抢同一行。

典型原因和应对方式：

could not lock updated tuple：某行刚被业务 UPDATE 过，刷新尝试原子替换时锁不住它。解决方案是缩短业务事务，避免在刷新窗口内长时间持有行锁；或把刷新调度避开业务高峰
duplicate key value violates unique constraint：业务插入了一条和即将刷入的新数据 key 完全相同的记录。这说明物化视图定义和业务写入逻辑存在语义冲突，比如都按 user_id 做去重，但业务侧未同步过滤。必须统一主键生成/去重逻辑，不能靠 MV 被动兜底
高频刷新（如每分钟）会显著放大这类冲突概率，也更容易因临时副本占满 pg_wal 或 base 目录导致磁盘告警

最常被忽略的一点：即使你把所有索引、NULL、分组列都配对了，只要物化视图还处于 WITH NO DATA 状态（空壳），CONCURRENTLY 依然会拒绝执行——必须先用普通刷新填充一次，让数据真实存在。