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

Refresh a materialized view automatically using a rule or notify

提问by mawimawi

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.

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

Here is what I was thinking about so far:

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

There is a materialized view mat_viewwhich gets its data from tables table1and table2using some join statement.

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

Whenever something in table1or table2changes, I already have a trigger wich updates a little configuration table configconsisting of

每当发生某些事情table1或table2发生变化时，我已经有了一个触发器，它会更新一个config包含以下内容的小配置表

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

So if anything in table1changes (there's a trigger on UPDATE and on DELETE for every statement), the field need_updatein the first row is set to TRUE. The same goes for table2and the second row.

因此，如果有任何table1更改（每个语句的 UPDATE 和 DELETE 上都有一个触发器），need_update则第一行中的字段将设置为TRUE. 这同样适用于table2第二行。

Obviously, if need_updateis TRUE, then the materialized view must be refreshed.

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

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_viewwith 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:

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

• check whether mat_viewshould be updated (SELECT 1 FROM config WHERE mat_view_name='mat_view' AND need_update=TRUE)
• reset the need_updateflag 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_viewas the target.

• 检查是否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 语句，但以 withmat_view作为目标。

UPDATE2: I tried creating the steps above:

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

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;

Create the view v_mat_viewwhich really selects from the function mat_view_selector:

创建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.

The result is unsatisfying:

结果令人不满意：

# 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

in comparison to selecting from the mat_view itself:

与从 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

So essentially it DOES work, but performance might be an issue.

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

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. :-(

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

采纳答案by Jeff Widman

PostgreSQL 9.4 added REFRESH CONCURRENTLYto Materialized Views.

PostgreSQL 9.4 添加REFRESH CONCURRENTLY到物化视图。

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.

使用语句级触发器监视底层表的任何更改，然后同时刷新物化视图。

回答by klin

You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1and table2.

您应该刷新插入/更新/删除/截断对每个语句后触发器中的观点table1和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 refreshof 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 clearpostgres solution.

Postgres 没有，所以似乎没有明确的postgres 解决方案。

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

考虑到这一点，我会考虑在 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;

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

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