Postgres如何在外部源表数据变更时自动刷新物化视图？

针对你无法通过本地触发器感知远程MSSQL视图变更的问题，以下是几种实现近乎实时刷新物化视图的可行方案：

1. 远程数据库主动触发本地刷新（最优实时性） #

如果能联系到远程MSSQL数据库的管理员，可以请求他们在驱动远程视图的底层源表上创建触发器，当数据发生增删改时，主动调用本地PostgreSQL的刷新命令。

具体实现方式： #

• 在MSSQL的源表触发器中，通过xp_cmdshell调用PostgreSQL的命令行工具psql执行刷新操作：

  -- MSSQL触发器示例（需开启xp_cmdshell，需管理员权限）
  CREATE TRIGGER Trigger_Refresh_PG_MatView
  ON Remote_Source_Table
  AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
      EXEC xp_cmdshell 'psql -h 本地PG_IP -U 用户名 -d 数据库名 -c "REFRESH MATERIALIZED VIEW matview;"';
  END
  

• 也可以通过MSSQL的CLR集成编写.NET代码，直接调用PostgreSQL的数据库接口执行刷新命令，比xp_cmdshell更安全可控。

优缺点：

• 优点：实现真正的实时同步，远程数据变更后立即触发刷新。
• 缺点：依赖远程管理员的配合，若无法获得权限则无法实施。

2. 本地轻量数据指纹检测（无需远程配合） #

如果无法获得远程数据库的操作权限，可以在本地定期检测远程视图的数据指纹（如最后更新时间、数据哈希值），当指纹变化时触发刷新。这种方式不是严格实时，但通过缩短检测间隔可以达到近乎实时的效果。

具体实现方式： #

1. 创建本地记录表存储远程视图的上次检测指纹：

   CREATE TABLE remote_fingerprint (
       view_name TEXT PRIMARY KEY,
       last_check_time TIMESTAMP,
       last_max_updated TIMESTAMP, -- 若远程视图有更新时间字段优先使用
       last_hash TEXT -- 无更新时间时用全表哈希值
   );
   -- 初始化记录
   INSERT INTO remote_fingerprint (view_name, last_max_updated)
   VALUES ('foreign_table', (SELECT MAX(updated_at) FROM foreign_table));
   

2. 使用pg_cron扩展（需先安装）创建定时任务，每分钟检测一次指纹：

   -- 安装pg_cron（若未安装）
   CREATE EXTENSION IF NOT EXISTS pg_cron;
   
   -- 创建定时检测任务
   SELECT cron.schedule(
       'check-remote-table-change',
       '* * * * *', -- 每分钟执行一次
       $$
           DECLARE
               current_max TIMESTAMP;
               stored_max TIMESTAMP;
           BEGIN
               SELECT MAX(updated_at) INTO current_max FROM foreign_table;
               SELECT last_max_updated INTO stored_max FROM remote_fingerprint WHERE view_name = 'foreign_table';
   
               IF current_max != stored_max THEN
                   REFRESH MATERIALIZED VIEW matview;
                   UPDATE remote_fingerprint SET last_max_updated = current_max, last_check_time = NOW() WHERE view_name = 'foreign_table';
               END IF;
           END;
       $$
   );
   

优缺点：

• 优点：无需远程配合，实现简单，资源消耗低（仅查询聚合值或哈希）。
• 缺点：存在1分钟以内的延迟，若远程视图无更新时间字段，计算全表哈希会增加一定开销（更适合小数据量场景）。

3. 基于CDC的本地中间表同步（适合大数据量） #

如果远程MSSQL数据库开启了CDC（变更数据捕获），可以使用Debezium等CDC工具捕获远程源表的变更，将数据同步到本地PostgreSQL的一个中间表，然后给中间表添加触发器，当数据变更时刷新物化视图。

具体实现方式： #

1. 请远程MSSQL管理员开启目标源表的CDC功能。
2. 部署Debezium连接器，捕获MSSQL的变更日志，将数据同步到本地PostgreSQL的local_foreign_sync中间表。
3. 给local_foreign_sync表创建触发器触发物化视图刷新：

   CREATE OR REPLACE FUNCTION refresh_matview_trigger()
   RETURNS TRIGGER AS $$
   BEGIN
       REFRESH MATERIALIZED VIEW matview;
       RETURN NULL;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER Trigger_Refresh_MatView_On_Sync
   AFTER INSERT OR UPDATE OR DELETE ON local_foreign_sync
   FOR EACH STATEMENT EXECUTE FUNCTION refresh_matview_trigger();
   

优缺点：

• 优点：适合大数据量场景，仅同步变更数据，延迟极低，不依赖远程触发器。
• 缺点：需要部署额外的CDC工具，配置相对复杂，若远程未开启CDC则无法实施。

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