STL_MV_STATE - Amazon Redshift
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

如果我们为英文版本指南提供翻译,那么如果存在任何冲突,将以英文版本指南为准。在提供翻译时使用机器翻译。

STL_MV_STATE

STL_MV_STATE视图包含一个用于实质化视图的每个状态过渡的行。

有关具体化视图的更多信息,请参阅Creating materialized views in Amazon Redshift

STL_MV_STATE 对所有用户可见。超级用户可以查看所有行;普通用户只能看到自己的数据。有关更多信息,请参阅 Visibility of data in system tables and views

Table columns

列名称 数据类型 Description
userid bigint 创建事件的用户的 ID。
starttime timestamp 事件的开始时间。
xid bigint 事件的事务 ID。
event_desc char(500) 提示状态更改的事件。示例值包括:
  • Column type was changed

  • Column was dropped

  • Column was renamed

  • Schema name was changed

  • Small table conversion

  • TRUNCATE

  • Vacuum

db_name char(128) 包含具体化视图的数据库。
base_table_schema char(128) 基表的架构。
base_table_name char(128) 基表的名称。
mv_schema char(128) 具体化视图的架构。
mv_name char(128) 具体化视图的名称。
state character(32) 具体化视图的已更改状态如下所示:
  • Recompute

  • Unrefreshable

下表显示了 event_descstate 的示例组合。

event_desc | state -------------------------+--------------- TRUNCATE | Recompute TRUNCATE | Recompute Small table conversion | Recompute Vacuum | Recompute Column was renamed | Unrefreshable Column was dropped | Unrefreshable Table was renamed | Unrefreshable Column type was changed | Unrefreshable Schema name was changed | Unrefreshable

Sample query

要查看具体化视图的状态转换的日志,请运行以下查询。

select * from stl_mv_state;

此查询返回以下示例输出。

userid | starttime | xid | event_desc | db_name | base_table_schema | base_table_name | mv_schema | mv_name | state --------+----------------------------+------+-----------------------------+---------+----------------------+----------------------+----------------------+---------------+--------------- 138 | 2020-02-14 02:21:25.578885 | 5180 | TRUNCATE | dev | public | mv_base_table | public | mv_test | Recompute 138 | 2020-02-14 02:21:56.846774 | 5275 | Column was dropped | dev | | mv_base_table | public | mv_test | Unrefreshable 100 | 2020-02-13 22:09:53.041228 | 1794 | Column was renamed | dev | | mv_base_table | public | mv_test | Unrefreshable 1 | 2020-02-13 22:10:23.630914 | 1893 | ALTER TABLE ALTER SORTKEY | dev | public | mv_base_table_sorted | public | mv_test | Recompute 1 | 2020-02-17 22:57:22.497989 | 8455 | ALTER TABLE ALTER DISTSTYLE | dev | public | mv_base_table | public | mv_test | Recompute 173 | 2020-02-17 22:57:23.591434 | 8504 | Table was renamed | dev | | mv_base_table | public | mv_test | Unrefreshable 173 | 2020-02-17 22:57:27.229423 | 8592 | Column type was changed | dev | | mv_base_table | public | mv_test | Unrefreshable 197 | 2020-02-17 22:59:06.212569 | 9668 | TRUNCATE | dev | schemaf796e415850f4f | mv_base_table | schemaf796e415850f4f | mv_test | Recompute 138 | 2020-02-14 02:21:55.705655 | 5226 | Column was renamed | dev | | mv_base_table | public | mv_test | Unrefreshable 1 | 2020-02-14 02:22:26.292434 | 5325 | ALTER TABLE ALTER SORTKEY | dev | public | mv_base_table_sorted | public | mv_test | Recompute