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

我有一个关于 PostgreSQL 9.3数据库的物化视图，它很少更改(大约一天两次)。但一旦发生，我希望能及时更新数据。

以下是我到目前为止一直在思考的问题:

有一个物化视图 mat_view，它使用某种连接语句从表 table1和 table2获取数据。

每当 table1或 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，则必须刷新物化视图。

更新 : 由于物化视图不支持规则(正如@pozs 在下面的评论中提到的那样) ，我将更进一步。我将创建一个定义为“ SELECT * FROM mat_view”的虚拟视图 v_mat_view。当用户在这个视图上执行 SELECT 操作时，我需要创建一个 ON SELECT 规则，该规则执行以下操作:

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

更新2 : 我尝试创建以上步骤:

创建一个函数来处理上面提到的四点:

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;

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

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。

89530

小开

You should refresh the view in triggers after insert/update/delete/truncate for each statement on 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();

In this way your materialized view is always up to date. This simple solution might be hard to accept with frequent inserts/updates and sporadic selects. In your case (seldom changes about twice a day) it ideally fits your needs.

To realize deferred refresh of a materialized view you need one of the following features:

asynchronous trigger
trigger before select
rule on select before

Postgres has none of them, so it seems that there is no clear postgres solution.

Taking this into account I would consider a wrapper function for selects on mat_view, e.g.

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;

If it is acceptable in practice depends on particulars I do not know about.