AWS Aurora PostgreSQL 9.3中如何暂停物化视图刷新以修改表列？

PostgreSQL 9.3（包括AWS Aurora该版本）没有原生的「暂停物化视图刷新」功能，不过可以通过以下两种可靠方法实现需求：

方法一：锁定物化视图阻止刷新 #

刷新物化视图需要获取目标视图的ACCESS EXCLUSIVE锁，因此我们可以主动持有该锁，直到表结构修改完成，期间所有刷新请求会被阻塞：

1. 批量锁定所有相关物化视图
   可手动执行或写脚本循环处理17个视图：

   -- 单个视图锁定示例
   LOCK MATERIALIZED VIEW mv_name_1 IN ACCESS EXCLUSIVE MODE;
   LOCK MATERIALIZED VIEW mv_name_2 IN ACCESS EXCLUSIVE MODE;
   -- ... 依次处理所有17个物化视图
   

   该锁会阻塞视图的所有读写操作（包括查询和刷新），务必选择业务低峰期执行，确保这段时间无依赖这些视图的业务流量。

2. 执行列类型修改
   先检查数据是否符合numeric(10,2)的范围，避免修改失败：

   -- 排查超出范围的数据
   SELECT your_column 
   FROM your_table 
   WHERE your_column > 99999999.99 OR your_column < -99999999.99;
   

   确认数据正常后执行修改：

   ALTER TABLE your_table ALTER COLUMN your_column TYPE numeric(10,2);
   

3. 释放锁
   完成修改后，关闭当前SQL会话即可自动释放所有锁，物化视图的刷新和查询会恢复正常。

方法二：临时修改物化视图所有权（不阻塞查询） #

如果需要保留物化视图的查询能力，仅阻止刷新，可以临时将视图所有权转移给超级用户，操作完成后再归还：

1. 记录所有物化视图的原所有者

   SELECT matviewname, rolname AS original_owner
   FROM pg_matviews 
   JOIN pg_roles ON pg_matviews.matviewowner = pg_roles.oid
   WHERE matviewname IN ('mv_name_1', 'mv_name_2', ...); -- 列出所有17个视图
   

   保存查询结果，后续用于恢复所有权。

2. 批量修改所有权为超级用户

   ALTER MATERIALIZED VIEW mv_name_1 OWNER TO your_superuser;
   ALTER MATERIALIZED VIEW mv_name_2 OWNER TO your_superuser;
   -- ... 依次处理所有视图
   

   原所有者此时将失去刷新权限，无法操作自己创建的物化视图。

3. 执行列类型修改
   同方法一，先检查数据，再执行ALTER TABLE命令。

4. 恢复原所有者
   根据之前记录的结果，将每个视图的所有权改回原用户：

   ALTER MATERIALIZED VIEW mv_name_1 OWNER TO original_owner_1;
   ALTER MATERIALIZED VIEW mv_name_2 OWNER TO original_owner_2;
   -- ... 依次恢复所有视图
   

关键注意事项 #

• 操作前务必备份目标表和所有物化视图，避免意外数据损坏。
• 若物化视图有自动刷新的定时任务（如cron或Postgres定时触发器），需同时暂停这些任务，否则任务会因锁阻塞或权限不足报错。
• AWS Aurora 9.3的锁机制与原生PostgreSQL 9.3一致，无需担心兼容性问题。

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