postgres中怎么样让materialized view自动刷新？

Question

postgres中怎么样可以让materialized view自动刷新？

因为要做BI应用，需要使用到materialized view，但是每次都要手动refresh很麻烦，怎么样可以在数据源产生新数据的时候让materialized view也跟着自动更新？

Answer 1

我认为在PG中可以考虑使用AFTER触发器来实现题主的需求。

假设有一张表foo, 其定义以及现有数据如下所示:

postgres=# \d+ foo;
                                    Table "public.foo"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           | not null |         | plain    |              | 
 name   | text    |           |          |         | extended |              | 
Indexes:
    "idx_foo" PRIMARY KEY, btree (id)
    
postgres=# SELECT * FROM foo;
 id |      name       
----+-----------------
  1 | Paul McCartney
  2 | John Lennon
  3 | George Harrison
  4 | Ringo Starr
(4 rows)

以下步骤将创建一个物化视图并创建与之关联的自动化刷新触发器

1. 创建物化视图

   postgres=# CREATE MATERIALIZED VIEW mvfoo AS SELECT * FROM foo;

2. 创建刷新物化视图的触发器函数

   postgres=# CREATE OR REPLACE FUNCTION tri_foo_func() RETURNS TRIGGER AS $$ BEGIN REFRESH MATERIALIZED VIEW mvfoo; RETURN NULL; END; $$ LANGUAGE plpgsql;

   由于我们将要创建的是一个AFTER触发器, 所以函数返回NULL即可

3. 创建AFTER触发器自身

   CREATE TRIGGER tri_foo AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH STATEMENT EXECUTE PROCEDURE tri_foo_func();

   INSERT, UPDATE, DELETE事件均会触发该触发器

接下来可以进行简单的校验

1. 未对表进行任何更改时查询该物化视图

   postgres=# SELECT * FROM mvfoo;
    id |      name       
   ----+-----------------
     1 | Paul McCartney
     2 | John Lennon
     3 | George Harrison
     4 | Ringo Starr
   (4 rows)

2. 对表进行更新(本例中是INSERT操作)后查询该物化视图

   postgres=# INSERT INTO foo VALUES (5, 'Brain Epstein');
   INSERT 0 1
   postgres=# SELECT * FROM mvfoo;
    id |      name       
   ----+-----------------
     1 | Paul McCartney
     2 | John Lennon
     3 | George Harrison
     4 | Ringo Starr
     5 | Brain Epstein
   (5 rows)

   可见，这套基于触发器的物化视图自动刷新机制生效了

   但是，必须要强调是: 使用触发器对原表的更新性能是有伤害的, 请结合具体应用场景谨慎使用.