如何在CockroachDB v20.2中查询物化视图的最后刷新时间

在CockroachDB v20.2中，可通过下述方式获取*物化视图（Materialized Views）*的列表：

> SELECT * FROM pg_catalog.pg_matviews;
  schemaname | matviewname | matviewowner | tablespace | hasindexes | ispopulated | definition
----------------------------------------------------------------------------------------------------
  public     | VIEWNAME    | root         | NULL       |   false    |    true     | VIEWDEFINITION

拿到上述物化视图列表后，可按照以下方法查询对应物化视图的最近*刷新（Refresh）*时间：

版本基础特性说明 #

CockroachDB v20.2版本的 pg_catalog.pg_matviews 系统表仅存储物化视图的基础结构元数据，本身不包含刷新时间相关字段，无法直接从该表中获取刷新时间信息。

可用查询方案 #

• 方案1：通过集群内部事件表回溯短期刷新记录
  CockroachDB默认会将所有物化视图的刷新操作作为集群事件存储在内部系统表 crdb_internal.cluster_events 中，执行如下SQL即可查询指定周期内所有物化视图的最近刷新时间：

  SELECT
    (info->>'matview') AS full_matview_name,
    MAX(timestamp) AS last_refresh_time
  FROM crdb_internal.cluster_events
  WHERE event_type = 'refresh_materialized_view'
  GROUP BY full_matview_name;
  

  注意：该方法仅能查询到事件留存周期内的记录，v20.2版本默认事件保留时长为7天，超过留存周期的历史刷新记录会被自动清理，无法回溯。

• 方案2：自建日志表长期追踪刷新记录
  如果需要长期留存所有物化视图的刷新时间，可预先创建独立的日志存储表，在每次执行刷新操作时同步写入日志：

  1. 先创建日志表：

     CREATE TABLE IF NOT EXISTS matview_refresh_log (
       matview_full_name STRING,
       refresh_time TIMESTAMPTZ DEFAULT now(),
       refresh_result STRING
     );
     

  2. 后续执行刷新操作时，同步向日志表写入对应记录，例如：

     -- 执行物化视图刷新
     REFRESH MATERIALIZED VIEW public.VIEWNAME;
     -- 写入刷新日志
     INSERT INTO matview_refresh_log (matview_full_name, refresh_result)
     VALUES ('public.VIEWNAME', 'success');
     

  查询时按物化视图名称分组取最大的refresh_time值，即可得到对应物化视图的最近刷新时间，该方式没有留存周期限制，可按需长期保存记录。

内容的提问来源于stack exchange，提问作者Elle Fie