PostgreSQL更新时触发刷新物化视图报错的解决方案咨询

当你执行UPDATE语句时，WHERE子句引用了物化视图informationspermissions，当前会话会持有该物化视图的引用（锁）。而AFTER触发器在同一会话的同一事务中尝试刷新该视图，PostgreSQL不允许在会话仍在使用物化视图时对其进行刷新，因此抛出错误。

1. 改用定时任务刷新物化视图 #

如果权限数据不需要实时更新，可以放弃触发器实时刷新的方式，改用定时任务（如pg_cron扩展）定期刷新物化视图，从根源上避免事务内的冲突。

示例（使用pg_cron）：

-- 安装pg_cron扩展（需超级用户权限）
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 设置每5分钟刷新一次物化视图
SELECT cron.schedule('refresh-informations-permissions', '*/5 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY informationspermissions;');

2. 将权限校验与更新操作分离 #

先单独查询物化视图获取允许操作的ID，再执行UPDATE语句。这样在UPDATE执行时，会话已不再持有物化视图的引用，触发器刷新时就不会冲突。

示例（单语句实现）：

WITH allowed_ids AS (
    SELECT id FROM informationspermissions WHERE user=<user ID> AND id=123
)
UPDATE informations
SET name='new name'
WHERE id IN (SELECT id FROM allowed_ids);

示例（分步骤实现，适合应用程序调用）：

-- 第一步：获取用户有权限操作的ID
SELECT id INTO v_allowed_id FROM informationspermissions WHERE user=<user ID> AND id=123;

-- 第二步：仅当存在允许的ID时执行更新
IF v_allowed_id IS NOT NULL THEN
    UPDATE informations SET name='new name' WHERE id=v_allowed_id;
END IF;

3. 替换物化视图为普通视图 #

如果权限校验的查询性能可以接受，将物化视图改为普通视图。普通视图无需手动刷新，每次查询都会实时计算结果，这样就不需要触发器，自然避免刷新冲突。

示例：

-- 删除原物化视图及触发器
DROP MATERIALIZED VIEW informationspermissions;
DROP TRIGGER trigger_refresh_informations ON informations;
DROP FUNCTION refresh_informations();

-- 创建普通视图
CREATE VIEW informationspermissions AS (
    -- 原物化视图的业务逻辑
    -- 输出唯一的(id, user)对
);

4. 使用dblink在独立会话中刷新 #

通过dblink扩展在独立会话中执行刷新操作，避开当前UPDATE会话的锁冲突。需要先安装dblink扩展。

示例：

-- 安装dblink扩展
CREATE EXTENSION IF NOT EXISTS dblink;

-- 修改触发器函数
CREATE OR REPLACE FUNCTION refresh_informations()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    -- 连接到当前数据库（独立会话）
    PERFORM dblink_connect('dbname=' || current_database());
    -- 在独立会话中刷新物化视图
    PERFORM dblink_exec('REFRESH MATERIALIZED VIEW CONCURRENTLY informationspermissions');
    -- 断开连接
    PERFORM dblink_disconnect();
    RETURN NULL;
END;
$$;

关于触发器执行时机的说明 #

AFTER语句触发器本身就是在整个UPDATE语句执行完成后触发的，你无法让它在WHERE子句执行完毕后更早运行。问题的核心不是触发器时机，而是同一事务中会话仍持有物化视图的引用，导致无法刷新。上述解决方法均围绕避免在同一事务/会话中同时使用和刷新物化视图展开。

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