如何让触发器触发物化视图并行刷新时无需等待直接返回？

我将不同源表的数据处理后存储在物化视图（MV）中，选择MV是因为查询这些数据耗时约3秒且需要频繁调用。我创建了一个触发器，每当源表执行INSERT、DELETE或UPDATE操作时就刷新该MV，但触发器函数会等待物化视图完成刷新后才返回，导致插入操作耗时从0.07秒变成3秒。我希望插入操作能尽快返回，MV在后台并行刷新。

当前使用的触发器函数和触发器：

CREATE OR REPLACE FUNCTION "MOBILIDADE".atualizar_mv_solicitacao()
RETURNS TRIGGER AS
$$
BEGIN
REFRESH MATERIALIZED VIEW CONCURRENTLY "MOBILIDADE"."MV_SOLICITACAO";
RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER solicitacao_atualizar_mv_solicitacao
AFTER INSERT OR DELETE OR UPDATE ON "MOBILIDADE"."GESTAOPROJETOS_SOLICITACAO"
FOR EACH STATEMENT
EXECUTE PROCEDURE "MOBILIDADE".atualizar_mv_solicitacao();

插入语句示例：

INSERT INTO "MOBILIDADE"."GESTAOPROJETOS_SOLICITACAO" (documento_tipo,documento_numero,documento_sigla,documento_ano,requerente,solicitacao,data,data_recebimento_semob,categorias,geom,endereco_regiao,endereco_bairro,endereco_logradouro,anexo,created_by,created_at,acao) VALUES('Indicação',12345,'TESTE',2022,'TESTE','TESTE','2022-09-15','2022-09-15','{"Barreiras físicas" , "Pavimentação"}',ST_Transform(ST_SetSRID(ST_MakePoint(-45.888675631640105,-23.236909838714148),4326),4326),'Sul','Bosque dos Eucaliptos','Rua Lima Duarte',false,1,NOW(),1) RETURNING id

PostgreSQL触发器默认同步执行，要实现异步刷新，得把刷新操作放到后台进程中执行，以下是两种可行方案：

方案一：用pg_notify配合后台监听进程 #

1. 修改触发器函数，只发送通知，不直接执行刷新：

CREATE OR REPLACE FUNCTION "MOBILIDADE".atualizar_mv_solicitacao()
RETURNS TRIGGER AS
$$
BEGIN
  -- 发送通知到指定频道
  PERFORM pg_notify('refresh_mv_solicitacao', '');
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

2. 编写后台监听脚本（比如bash脚本），持续监听通知频道，收到通知后执行刷新：

#!/bin/bash
# 替换成你的数据库名
DB_NAME="你的数据库名称"

while true; do
  # 监听指定频道
  psql -d $DB_NAME -c "LISTEN refresh_mv_solicitacao;"
  # 等待通知并执行刷新
  psql -d $DB_NAME -c "
    BEGIN;
      WAIT NOTIFY;
      REFRESH MATERIALIZED VIEW CONCURRENTLY \"MOBILIDADE\".\"MV_SOLICITACAO\";
    COMMIT;
  "
done

把这个脚本放到后台运行（比如用nohup ./脚本名.sh &或者配置systemd服务），这样源表变更时，触发器只发个通知就返回，后台进程收到通知再去刷新MV。

方案二：用dblink异步执行刷新 #

利用dblink建立本地连接，在连接中异步执行刷新操作，触发器无需等待结果：

1. 先安装dblink扩展（如果未安装）：

CREATE EXTENSION IF NOT EXISTS dblink;

2. 修改触发器函数：

CREATE OR REPLACE FUNCTION "MOBILIDADE".atualizar_mv_solicitacao()
RETURNS TRIGGER AS
$$
BEGIN
  -- 建立到当前数据库的连接
  PERFORM dblink_connect('dbname=' || current_database());
  -- 第三个参数设为true，不等待执行结果直接返回
  PERFORM dblink_exec('', 'REFRESH MATERIALIZED VIEW CONCURRENTLY "MOBILIDADE"."MV_SOLICITACAO";', true);
  -- 断开连接
  PERFORM dblink_disconnect();
  RETURN NULL;
END
$$ LANGUAGE plpgsql;

注意事项 #

• 使用CONCURRENTLY刷新MV要求MV必须有唯一索引，确认你已经给MV_SOLICITACAO创建了合适的唯一索引。
• 如果短时间内源表有多次变更，可能导致MV被频繁刷新，建议在后台处理逻辑中加防抖（比如等待2-3秒再执行刷新），避免重复操作浪费资源。
• 后台监听进程需要保持运行，如果进程意外终止，MV不会自动刷新，建议配置进程监控确保其可用性。

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