STV_MV_INFO - Amazon Redshift

STV_MV_INFO

The STV_MV_INFO table contains a row for every materialized view, whether the data is stale, and state information.

For more information about materialized views, see Creating materialized views in Amazon Redshift.

STV_MV_INFO 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
db_name char(128) The database that contains the materialized view.
schema char(128) The schema of the database.
name char(128) The materialized view name.
updated_upto_xid bigint Reserved for internal use.
is_stale char(1) A t indicates that the materialized view is stale. A stale materialized view is one where the base tables have been updated but the materialized view hasn't been refreshed. This information might not be accurate if a refresh hasn't been run since the last restart.
owner_user_name char(128) The user who owns the materialized view.
state integer The state of the materialized view as follows:
  • 0 – The materialized view is fully recomputed when refreshed.

  • 1 – The materialized view is incremental.

  • 101 – The materialized view can't be refreshed due to a dropped column. This constraint applies even if the column isn't used in the materialized view.

  • 102 – The materialized view can't be refreshed due to a changed column type. This constraint applies even if the column isn't used in the materialized view.

  • 103 – The materialized view can't be refreshed due to a renamed table.

  • 104 – The materialized view can't be refreshed due to a renamed column. This constraint applies even if the column isn't used in the materialized view.

  • 105 – The materialized view can't be refreshed due to a renamed schema.

autorewrite char(1) A t indicates that the materialized view is eligible for automatic rewriting of queries.
autorefresh char(1) A t indicates that the materialized view can be automatically refreshed.

Sample query

To view the state of all materialized views, run the following query.

select * from stv_mv_info;

This query returns the following sample output.

db_name | schema | name | updated_upto_xid | is_stale | owner_user_name | state | autorefresh | autorewrite ---------+--------------------+---------+------------------+----------+-----------------+-------+-------------+------------ dev | test_ivm_setup | mv | 1031 | f | catch-22 | 1 | 1 | 0 dev | test_ivm_setup | old_mv | 988 | t | lotr | 1 | 0 | 1