使用规则或通知自动刷新物化视图 [英] Refresh a materialized view automatically using a rule or notify
问题描述
我对 PostgreSQL 9.3 数据库有一个实体化视图,该数据库很少更改(大约每天两次).但是当它发生时,我想及时更新它的数据.
I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I'd like to update its data promptly.
这是我目前想到的:
有一个实体化视图 mat_view
,它使用一些连接语句从表 table1
和 table2
中获取数据.
There is a materialized view mat_view
which gets its data from tables table1
and table2
using some join statement.
每当 table1
或 table2
中的某些内容发生变化时,我已经有一个触发器来更新由
Whenever something in table1
or table2
changes, I already have a trigger which updates a little configuration table config
consisting of
table_name | mat_view_name | need_update
-----------+---------------+------------
table1 | mat_view | TRUE/FALSE
table2 | mat_view | TRUE/FALSE
因此,如果 table1
中的任何内容发生更改(每个语句的 UPDATE 和 DELETE 上都有一个触发器),则第一行中的字段 need_update
将设置为 真
.table2
和第二行也是如此.
So if anything in table1
changes (there's a trigger on UPDATE and on DELETE for every statement), the field need_update
in the first row is set to TRUE
.
The same goes for table2
and the second row.
显然,如果 need_update
为 TRUE,则必须刷新物化视图.
Obviously, if need_update
is TRUE, then the materialized view must be refreshed.
更新:由于物化视图不支持规则(如@pozs 在下面的评论中提到的),我会更进一步.我会用定义SELECT * FROM mat_view
"创建一个虚拟视图v_mat_view
.当用户在此视图上执行 SELECT 时,我需要创建一个 ON SELECT 规则,它执行以下操作:
UPDATE:
Since materialized views do not support rules (as @pozs mentioned in a comment below), I would go one step further. I'd create a dummy view v_mat_view
with the definition "SELECT * FROM mat_view
". When the user does a SELECT on this view, I need to create a rule ON SELECT which does the following:
- 检查是否应该更新
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'
重置 刷新实体化视图 mat_view
- 最后执行原始 SELECT 语句,但以
mat_view
作为目标.
need_update
标志- check whether
mat_view
should be updated (SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE
) - reset the
need_update
flag withUPDATE config SET need_update=FALSE where mat_view_name='mat_view'
REFRESH MATERIALIZED VIEW mat_view
- and at last do the original SELECT statement but with
mat_view
as the target.
更新2:我尝试创建上述步骤:
UPDATE2: I tried creating the steps above:
创建一个处理上述四点的函数:
Create a function that handles the four points mentioned above:
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 the view v_mat_view
which really selects from the function 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 本身中选择相比:
in comparison to selecting from the mat_view itself:
# 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
所以本质上它确实有效,但性能可能是一个问题.
So essentially it DOES work, but performance might be an issue.
有人有更好的想法吗?如果没有,那么我将不得不在应用程序逻辑中以某种方式实现它,或者更糟:运行一个每分钟左右运行一次的简单 cronjob.
Anyone have better ideas? If not, then I would have to implement it somehow in the application logic or worse: run a simple cronjob that runs every minute or so.
推荐答案
PostgreSQL 9.4 在物化视图中添加了 REFRESH CONCURRENTLY
.
PostgreSQL 9.4 added REFRESH CONCURRENTLY
to Materialized Views.
当您描述尝试设置物化视图的异步更新时,这可能是您正在寻找的内容.
This may be what you're looking for when you describe trying to setup an asynchronous update of the materialized view.
从实体化视图中进行选择的用户会在刷新完成之前看到错误的数据,但在许多使用实体化视图的场景中,这是一个可以接受的权衡.
Users selecting from the materialized view will see incorrect data until the refresh finishes, but in many scenarios that use a materialized view, this is an acceptable tradeoff.
使用语句级触发器监视底层表的任何更改,然后同时刷新物化视图.
Use a statement level trigger that watches the underlying tables for any changes and then refreshes the materialized view concurrently.
这篇关于使用规则或通知自动刷新物化视图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!