STL_MV_STATE
The STL_MV_STATE view contains a row for every state transition of a materialized view.
For more information about materialized views, see Creating materialized views in Amazon Redshift.
STL_MV_STATE is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of data in system tables and views.
Table columns
| Column name | Data type | Description |
|---|---|---|
| userid | bigint | The ID of the user who created the event. |
| starttime | timestamp | The start time of the event. |
| xid | bigint | The transaction id of the event. |
| event_desc | char(500) | The event that prompted the state change. Example
values include the following:
|
| db_name | char(128) | The database that contains the materialized view. |
| base_table_schema | char(128) | The schema of the base table. |
| base_table_name | char(128) | The name of the base table. |
| mv_schema | char(128) | The schema of the materialized view. |
| mv_name | char(128) | The name of the materialized view. |
| state | character(32) | The changed state of the materialized view as follows:
|
The following table shows example combinations of event_desc and state.
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
To view the log of state transitions of materialized views, run the following query.
select * from stl_mv_state;
This query returns the following sample output:
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
