使用规则或通知自动刷新物化视图 [英] 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'
重置 need_update 标志
• 刷新实体化视图 mat_view
• 最后执行原始 SELECT 语句，但以 mat_view 作为目标.

• 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 with UPDATE 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屋！