我对PostgreSQL 9.3数据库有一个materialized 视图,它很少更改(大约每天两次).但当它这样做时,我想及时更新它的数据.
以下是我到目前为止的 idea :
有一个materialized 视图mat_view
,它使用一些join语句从表table1
和table2
获取数据.
每当table1
或table2
中的某些内容发生变化时,我已经有了一个触发器,它会更新一个小的配置表config
,其中包括
table_name | mat_view_name | need_update
-----------+---------------+------------
table1 | mat_view | TRUE/FALSE
table2 | mat_view | TRUE/FALSE
因此,如果table1
中的任何内容发生变化(每个语句都有一个UPDATE和DELETE触发器),则第一行的字段need_update
设置为TRUE
.
显然,如果need_update
为真,则必须刷新materialized 视图.
UPDATE:
- 判断是否应更新
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
.
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
中 Select 的视图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_视图本身 Select 相比:
# 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
所以从本质上讲,它确实有效,但性能可能是一个问题.
有谁有更好的主意吗?