使用规则或通知自动刷新物化视图

我对 PostgreSQL 9.3 数据库有一个物化视图，该数据库很少更改（大约每天两次）。但当它发生时，我想立即更新其数据。

到目前为止，这是我的想法：

有一个物化视图mat_view它从表中获取数据table1 and table2使用一些连接语句。

每当有东西在table1 or table2更改，我已经有一个触发器可以更新一些配置表config包含由...组成

table_name | mat_view_name | need_update
-----------+---------------+------------
table1     | mat_view      | TRUE/FALSE
table2     | mat_view      | TRUE/FALSE

所以如果有什么table1更改（每个语句的 UPDATE 和 DELETE 上都有一个触发器），该字段need_update第一行设置为TRUE。 同样适用于table2和第二行。

显然，如果need_update为 TRUE，则必须刷新物化视图。

UPDATE: 由于物化视图不支持规则（正如@pozs 在下面的评论中提到的），我会更进一步。我会创建一个虚拟视图v_mat_view与定义“SELECT * FROM mat_view”。当用户对此视图执行 SELECT 操作时，我需要创建一条 ON SELECT 规则，该规则执行以下操作：

• 检查是否mat_view应该更新（SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE)
• 重置need_update标志与UPDATE config SET need_update=FALSE where mat_view_name='mat_view'
• REFRESH MATERIALIZED VIEW mat_view
• 最后执行原始 SELECT 语句，但使用mat_view作为目标。

UPDATE2: 我尝试创建上述步骤：

创建一个处理上述四点的函数：

CREATE OR REPLACE FUNCTION mat_view_selector()
RETURNS SETOF mat_view AS $body$
BEGIN
  -- here is checking whether to refresh the mat_view
  -- then return the select:
  RETURN QUERY SELECT * FROM mat_view;
END;
$body$ LANGUAGE plpgsql;

创建视图v_mat_view真正从函数中选择mat_view_selector:

CREATE TABLE v_mat_view AS SELECT * from mat_view LIMIT 1;
DELETE FROM v_mat_view;

CREATE RULE "_RETURN" AS
    ON SELECT TO v_mat_view
    DO INSTEAD 
        SELECT * FROM mat_view_selector();
    -- this also converts the empty table 'v_mat_view' into a view.

结果并不令人满意：

# explain analyze select field1 from v_mat_view where field2 = 44;
QUERY PLAN
Function Scan on mat_view_selector (cost=0.25..12.75 rows=5 width=4)
(actual time=15.457..18.048 rows=1 loops=1)
Filter: (field2 = 44)
Rows Removed by Filter: 20021
Total runtime: 31.753 ms

与从 mat_view 本身进行选择相比：

# explain analyze select field1 from mat_view where field2 = 44;
QUERY PLAN
Index Scan using mat_view_field2 on mat_view (cost=0.29..8.30 rows=1 width=4)
  (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (field2 = 44)
Total runtime: 0.036 ms

所以本质上它确实有效，但性能可能是一个问题。

有人有更好的想法吗？ 如果没有，那么我将不得不在应用程序逻辑中以某种方式实现它，或者更糟：运行一个每分钟左右运行一次的简单 cronjob。

您应该在插入/更新/删除/截断后刷新触发器中的每个语句的视图table1 and table2.

create or replace function refresh_mat_view()
returns trigger language plpgsql
as $$
begin
    refresh materialized view mat_view;
    return null;
end $$;

create trigger refresh_mat_view
after insert or update or delete or truncate
on table1 for each statement 
execute procedure refresh_mat_view();

create trigger refresh_mat_view
after insert or update or delete or truncate
on table2 for each statement 
execute procedure refresh_mat_view();

通过这种方式，您的物化视图始终是最新的。对于频繁的插入/更新和零星的选择，这个简单的解决方案可能难以接受。 就您的情况而言（很少每天更改两次），它非常适合您的需求。

意识到延迟刷新对于物化视图，您需要以下功能之一：

• 异步触发
• 选择前触发
• 之前选择的规则

Postgres都没有，所以好像没有clearpostgres 解决方案。

考虑到这一点，我会考虑在 mat_view 上进行选择的包装函数，例如

CREATE OR REPLACE FUNCTION select_from_mat_view(where_clause text)
RETURNS SETOF mat_view AS $body$
BEGIN
  -- here is checking whether to refresh the mat_view
  -- then return the select:
  RETURN QUERY EXECUTE FORMAT ('SELECT * FROM mat_view %s', where_clause);
END;
$body$ LANGUAGE plpgsql;

在实践中是否可以接受取决于我不知道的细节。