刷新分区物化视图

自动刷新

由系统内部识别自动刷新，通过以下命令刷新。

REFRESH MATERIALIZED VIEW materialized_view_name;

该命令会尝试刷新物化视图，如果满足分区刷新的条件则进行分区刷新，否则转为全量刷新。

手动刷新指定分区

通过以下命令指定需要刷新的物化视图分区。

REFRESH MATERIALIZED VIEW materialized_view_name PARTITION（partname1,partname2);

该命令会尝试刷新物化视图分区partname1，partname2，如果当前分区不需要刷新，则不会刷新，并会提示数据为最新。

强制手动刷新指定分区

通过以下命令强制刷新物化视图指定分区。

REFRESH MATERIALIZED VIEW materialized_view_name PARTITION （partname）RESTRICT;

查看分区物化视图

查看分区的数据是否更新。

通过pg_matview_partition的active字段可以查看当前分区是否已更新。

\d  pg_matview_partition;
Table "pg_catalog.pg_matview_partition"
      Column        |           Type           | Modifiers
--------------------+--------------------------+-----------
matpartid           | oid                      | not null
active              | boolean                  | not null
refresh_start_time  | timestamp with time zone | not null
refresh_finish_time | timestamp with time zone | not null

其中，

• matpartid，表示物化视图分区OID
• active，表示当前分区是否已更新。 t表示已更新，f表示失效。
• refresh_start_time，表示最后一次分区刷新开始时间。
• refresh_finish_time，表示最后一次分区刷新结束时间。

示例

1. 创建时间上卷物化视图level_month并查看视图信息。

   CREATE MATERIALIZED VIEW level_month enable query rewrite
   DISTRIBUTE BY HASH(start_time)
   PARTITION BY date_trunc('month', start_time)
   AS SELECT fact_table.start_time, dimen_table.id FROM fact_table JOIN dimen_table ON fact_table.id = dimen_table.id;

   1 2 3 4 5 6 7 8

   SELECT * FROM pg_matview_partition; matpartid | active | refresh_start_time | refresh_finish_time ----------+--------+-------------------------------+------------------------------- 17092 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17093 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17094 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17095 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17096 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08

2. 向基表插入数据。

   1	INSERT INTO fact_table (start_time, id) VALUES ('2023-05-01',1);

3. 查看分区失效情况。

   1 2 3 4 5 6 7 8

   SELECT * FROM pg_matview_partition; matpartid | active | refresh_start_time | refresh_finish_time ----------+--------+-------------------------------+------------------------------- 17092 | f | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17093 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17094 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17095 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17096 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08

4. 刷新物化视图。

   1	REFRESH MATERIALIZED VIEW level_month;

   由于只是分区失效，则会触发分区刷新。

5. 查看刷新后的状态。

   1 2 3 4 5 6 7 8

   SELECT * FROM pg_matview_partition; matpartid | active | refresh_start_time | refresh_finish_time ----------+--------+-------------------------------+------------------------------- 17093 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17094 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17095 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17096 | t | 2024-11-18 15:15:32.177697+08 | 2024-11-18 15:15:32.204009+08 17092 | t | 2024-11-18 15:16:28.902902+08 | 2024-11-18 15:16:28.940159+08