SVL_MV_REFRESH_STATUS - Amazon Redshift
Table columnsSample query

SVL_MV_REFRESH_STATUS

The SVL_MV_REFRESH_STATUS view contains a row for the refresh activity of materialized views.

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

SVL_MV_REFRESH_STATUS 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.

Some or all of the data in this table can also be found in the SYS monitoring view SYS_MV_REFRESH_HISTORY. The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.

Table columns

Column name Data type Description
db_name char(128) The database that contains the materialized view.
userid bigint The ID of the user who performed the refresh.
schema_name char(128) The schema of the materialized view.
mv_name char(128) The materialized view name.
xid bigint The transaction ID of the refresh.
starttime timestamp The start time of the refresh.
endtime timestamp The end time of the refresh.
status text The status of the refresh. Example values include the following:

  • Refresh successfully updated MV incrementally

    If it's a materialized view for streaming, the message might have additional qualifiers regarding the number of records. These include the following:

    • Stream returned no new data – There were no records retrieved.

    • All records received from the stream were skipped – Records were retrieved, but due to errors all were skipped.

    • Some stream records were skipped – Records were retrieved, but due to errors some were skipped.

    If there are no qualifiers, then at least one record was retrieved and all records are available in the materialized view. There is one remaining possible qualifier:

    • The stream may contain more data – The refresh ended before Amazon Redshift determined that there were no further records to consume. The stream can be up to date, but it hasn't been confirmed by Amazon Redshift.

  • Refresh successfully recomputed MV from scratch

  • Refresh partially updated MV incrementally up to an active transaction

  • MV was already updated

  • Refresh failed. A base table column was renamed

  • Refresh failed. A base table column type was changed

  • Refresh failed. A base table was renamed

  • Refresh failed due to an internal error

  • Refresh failed. A base table column was dropped

  • Refresh failed. Schema of MV was renamed

  • Refresh failed. MV was not found

  • Auto refresh aborted due to excessive user workload

  • Refresh failed. Serializable isolation violation
refresh_type char(32) The definition of the refresh type. Example values include Manual and Auto.

Sample query

To view the refresh status of materialized views, run the following query. 

select * from svl_mv_refresh_status;

This query returns the following sample output: 


 db_name | userid |  schema   |  name   |  xid  |         starttime          |          endtime           |                        status                       |  refresh_type
---------+--------+-----------+---------+-------+----------------------------+----------------------------+-----------------------------------------------------+-----------------
 dev     |    169 | mv_schema | mv_test |  6640 | 2020-02-14 02:26:53.497935 | 2020-02-14 02:26:53.556156 | Refresh successfully recomputed MV from scratch     |  Manual
 dev     |    166 | mv_schema | mv_test |  6517 | 2020-02-14 02:26:39.287438 | 2020-02-14 02:26:39.349539 | Refresh successfully updated MV incrementally       |  Auto
 dev     |    162 | mv_schema | mv_test |  6388 | 2020-02-14 02:26:27.863426 | 2020-02-14 02:26:27.918307 | Refresh successfully recomputed MV from scratch     |  Manual
 dev     |    161 | mv_schema | mv_test |  6323 | 2020-02-14 02:26:20.020717 | 2020-02-14 02:26:20.080002 | Refresh successfully updated MV incrementally       |  Auto
 dev     |    161 | mv_schema | mv_test |  6301 | 2020-02-14 02:26:05.796146 | 2020-02-14 02:26:07.853986 | Refresh successfully recomputed MV from scratch     |  Manual
 dev     |    153 | mv_schema | mv_test |  6024 | 2020-02-14 02:25:18.762335 | 2020-02-14 02:25:20.043462 | MV was already updated                              |  Manual
 dev     |    143 | mv_schema | mv_test |  5557 | 2020-02-14 02:24:23.100601 | 2020-02-14 02:24:23.100633 | MV was already updated                              |  Manual
 dev     |    141 | mv_schema | mv_test |  5447 | 2020-02-14 02:23:54.102837 | 2020-02-14 02:24:00.310166 | Refresh successfully updated MV incrementally       |  Auto
 dev     |      1 | mv_schema | mv_test |  5329 | 2020-02-14 02:22:26.328481 | 2020-02-14 02:22:28.369217 | Refresh successfully recomputed MV from scratch     |  Auto
 dev     |    138 | mv_schema | mv_test |  5290 | 2020-02-14 02:21:56.885093 | 2020-02-14 02:21:56.885098 | Refresh failed. MV was not found                    |  Manual