Why is my materialized view not refreshing for my Amazon Redshift cluster?

Short description

The following scenarios can cause a materialized view in Amazon Redshift to not refresh or take a long time to complete:

• REFRESH MATERIALIZED VIEW is failing with permission error
• You see the error: Invalid operation: Materialized view mv_name could not be refreshed as a base table changed physically due to vacuum/truncate concurrently. Please try again;
• REFRESH MATERIALIZED VIEW is unrefreshable
• REFRESH MATERIALIZED VIEW was submitted and running for long time
• Refresh activity isn't shown on an automated refresh due to an active workload

Resolution

REFRESH MATERIALIZED VIEW is failing with permission error

You must be the owner to perform a REFRESH MATERIALIZED VIEW operation on a materialized view. Also, you must have the following privileges:

• SELECT privilege on the underlying base tables
• USAGE privilege on the schema

If the materialized view is a full recompute instead of an incremental refresh, you must also have the CREATE privilege on the schema. To define privileges, see GRANT. For more information, see Autorefreshing a materialized view.

Invalid operation: Materialized view mv_name could not be refreshed as a base table changed physically due to vacuum/truncate concurrently. Please try again;

The error occurs when REFRESH MATERIALIZED VIEW and VACUUM are submitted to run concurrently on the base table. After the operation completes, the REFRESH MATERIALIZED VIEW can be re-submitted.

REFRESH MATERIALIZED VIEW is unrefreshable

Unrefreshable materialized views can be caused by operations that:

• Rename or drop a column.
• Change the type of a column.
• Change the name of a base table or schema

Note: Materialized views in this condition can be queried but can't be refreshed. The preceding constraints apply even if the column isn't used in the materialized view.

To find whether the data in materialized view is stale, and materialized view state information, use STV_MV_INFO. To view the refresh activity of materialized view, use SVL_MV_REFRESH_STATUS. In this unrefreshable state of materialized view, you must drop and recreate the materialized view to keep the materialized view up-to-date.

The following are example error messages you might see:

```Detail: Procedure <mv_sp_*****_2_1>  does not exist```
```column <column name> does not exist```
```DETAIL:  schema "<schema name>" does not exist ;```
```ERROR: Materialized view <mv namme> is unrefreshable as a base table was renamed.```

REFRESH MATERIALIZED VIEW was submitted and running for long time

REFRESH MATERIALIZED VIEW functions as a normal query that run on your cluster. To confirm the query is running, do the following:

• To view the active queries running on the data, use STV_INFLIGHT.
• To record the current state of queries track by workload management (WLM), use STV_WLM_QUERY_STATE.
• To find out information of queries and query steps that are actively running on compute nodes, use STV_EXEC_STATE.

The REFRESH MATERIALIZED VIEW operation performance is subject to the following factors:

• Table locks: To view any current updates on tables in the database, see STV_LOCKS.
• Allocated resources: To view the service class configuration for WLM, see STV_WLM_SERVICE_CLASS_CONFIG.
• Type of Refresh: Incremental or full refresh. To view the type of refresh that the materialized view underwent, see SVL_MV_REFRESH_STATUS.

If you experience slow REFRESH MATERIALIZED VIEW performance, see Improve query performance.

Refresh activity isn't shown on an automated refresh due to an active workload

Amazon Redshift prioritizes your workloads over autorefresh. This prioritization might stop autorefresh to preserve the performance of your workload and might delay the refresh of some materialized views. In some cases, your materialized views might need more deterministic refresh behavior. To create more deterministic refresh behavior, use the following:

• Manual refresh as described in REFRESH MATERIALIZED VIEW
• Scheduled refresh using the Amazon Redshift scheduler API operations or the console

For more information, see Autorefreshing a materialized view.

---