SYS_MV_STATE - Amazon Redshift
Table columnsSample queries
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SYS_MV_STATE

The results include information about the state of all materialized views. It includes base table information, schema properties, and information about recent events, like dropping a column.

SYS_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
user_id bigint The ID of the user who created the event.
transaction_id bigint The transaction ID of the event.
database_name char(128) The database that contains the materialized view.
event_desc char(500) The event that prompted the state change. Example values include the following:

  • Column type was changed

  • Column was dropped

  • Column was renamed

  • Schema name was changed

  • Small-table conversion

  • TRUNCATE

  • Vacuum

Note that there are other possible values for this column.
start_time timestamp The start time of the event.
base_table_database_name char(128) The database name for the base table.
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, which are as follows:

  • Recompute

  • Unrefreshable

Sample queries

The following query shows the materialized view state.

select * from sys_mv_state;

The query returns the following sample output:

 user_id | transaction_id | database_name | event_desc                  | start_time                 | base_table_database_name | base_table_schema | base_table_name     |  mv_schema  | mv_name                    | state 
---------+----------------+---------------+-----------------------------+----------------------------+--------------------------+-------------------+---------------------+-------------+----------------------------+--------------
 106     | 12720          | tickit_db     | TRUNCATE                    | 2023-07-26 14:59:12.788268 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_a1f3f862 | Recompute
 106     | 12724          | tickit_db     | ALTER TABLE ALTER DISTSTYLE | 2023-07-26 14:59:51.409014 | tickit_db                | mv_schema         | test_table_58102435 | mv_schema   | materialized_view_ca746631 | Recompute
 106     | 12720          | tickit_db     | Column was renamed          | 2023-07-26 14:59:12.822928 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_5750a8d4 | Unrefreshable
 106     | 12727          | tickit_db     | Table was renamed           | 2023-07-26 15:00:08.051244 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_5750a8d4 | Unrefreshable
 106     | 12720          | tickit_db     | Column was renamed          | 2023-07-26 14:59:12.857755 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_5750a8d4 | Unrefreshable
 106     | 12727          | tickit_db     | Table was renamed           | 2023-07-26 15:00:08.051358 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_5ef0d754 | Unrefreshable
 106     | 12720          | tickit_db     | TRUNCATE                    | 2023-07-26 14:59:12.788159 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_5750a8d4 | Recompute
 106     | 12720          | tickit_db     | Column was renamed          | 2023-07-26 14:59:12.857799 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_a1f3f862 | Unrefreshable
 106     | 12720          | tickit_db     | TRUNCATE                    | 2023-07-26 14:59:12.788327 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_5ef0d754 | Recompute
 106     | 12727          | tickit_db     | ALTER TABLE ALTER SORTKEY   | 2023-07-26 15:00:08.006235 | tickit_db                | mv_schema         | test_table_58102435 | mv_schema   | materialized_view_ca746631 | Recompute
 106     | 12720          | tickit_db     | Column was renamed          | 2023-07-26 14:59:12.82297  | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_a1f3f862 | Unrefreshable
 106     | 12727          | tickit_db     | Table was renamed           | 2023-07-26 15:00:08.051321 | tickit_db                | mv_schema         | test_table_95d6d861 | mv_schema   | materialized_view_a1f3f862 | Unrefreshable