使用规则自动刷新实例化视图或通知 [英] 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.

这是到目前为止我在想什么:

Here is what I was thinking about so far:

有一个实例化视图mat_view，该视图使用一些join语句从表table1和table2中获取其数据.

There is a materialized view mat_view which gets its data from tables table1 and table2 using some join statement.

每当table1或table2中的某些内容发生变化时，我已经有了一个触发器，该触发器可以更新由以下组成的一个小配置表config

Whenever something in table1 or table2 changes, I already have a trigger wich 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将设置为TRUE. 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)
• 将need_update标志重置为UPDATE config SET need_update=FALSE where mat_view_name='mat_view'
• REFRESH MATERIALIZED VIEW 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.

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

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;

创建视图v_mat_view，该视图实际上是从功能mat_view_selector中选择的:

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屋！