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

我有一个PostgreSQL 9.3数据库的实例化视图很少有变化(每天大约两次)。但是，如果这样做，我想及时更新其数据。

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

有一个物化视图mat_view，它使用一些join语句从表table1和table2获取其数据。

每当table1或table2中的某些内容发生变化时，我已经有一个触发器，它更新了一些配置表配置

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

因此，如果table1中的任何内容发生变化(UPDATE和DELETE上都有一个触发器)，则第一行中的require_update字段设置为TRUE。
table2和第二行也一样。

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

更新：
由于实体化视图不支持规则(在下面的评论中提到的@pozs)，我将进一步。我将创建一个虚拟视图v_mat_view与定义“SELECT * FROM mat_view”。当用户在此视图上执行SELECT时，我需要创建一个规则ON SELECT，它执行以下操作：

>检查是否应该更新mat_view(SELECT 1 FROM config WHERE mat_view_name =’mat_view’AND need_update = TRUE)
>使用UPDATE配置重置need_update标志SET need_update = FALSE其中mat_view_name =’mat_view’
> REFRESH MATERIALIZED VIEW mat_view
>并最后执行原始的SELECT语句，但以mat_view为目标。

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

创建一个处理上述四点的函数：

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。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!