STV_MV_INFO - Amazon Redshift
Table columnsSample query

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. The information might not be accurate if a refresh hasn't been run since the last restart.

The is_stale column is always set to t if the materialized view depends on a mutable function. A mutable function returns a different result when given the same argument or arguments. For instance, most functions that return a date or timestamp are mutable functions.

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