Why doesn’t my materialized view refresh for my Amazon Redshift cluster?

Short description

The following scenarios prevent or cause long completion times for a materialized view refresh in Amazon Redshift:

• Permission errors prevent REFRESH MATERIALIZED VIEW operations.
• Base table concurrent VACUUM and TRUNCATE operations block a refresh and return the following 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 can't be refreshed.
• Active workloads block automated refresh activities.

Resolution

Note: For Amazon Redshift Serverless clusters, use the SYS monitoring views instead of the system views (STL, STV, SVL, SVV) that are used in provisioned clusters. For more information, see System view mapping for migrating to SYS monitoring views.

REFRESH MATERIALIZED VIEW permission errors

To perform a REFRESH MATERIALIZED VIEW operation on a materialized view, you must be the owner. You must also have SELECT privilege on the underlying base tables and USAGE privilege on the schema.

For full recompute operations, you must have CREATE privilege on the schema. For more information about how to define privileges, see GRANT.

For more information about automatic refresh, see Autorefreshing a materialized view.

The following error occurs when REFRESH MATERIALIZED VIEW and VACUUM are submitted to run concurrently on the base table: "Invalid operation: Materialized view mv_name could not be refreshed as a base table changed physically due to vacuum/truncate concurrently. Please try again;".

After the operation completes, resubmit the REFRESH MATERIALIZED VIEW command.

REFRESH MATERIALIZED VIEW refresh fails

The following operations can cause a refresh of materialized views to fail:

• You rename or drop a column.
• You change the type of a column.
• You change the name of a base table or schema.

Note: Materialized views in these conditions can be queried, but they can't be refreshed. The constraints apply even if the column isn't used in the materialized view.

Example error messages:

• 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 <my name> is unrefreshable as a base table was renamed.

To view the refresh activity of the materialized view, use SVL_MV_REFRESH_STATUS for an Amazon Redshift provisioned cluster or SYS_MV_REFRESH_HISTORY for an Amazon Redshift Serverless cluster.

To find whether the data in the materialized view is stale and to see materialized view state information, use STV_MV_INFO, SYS_MV_STATE, or SYS_MV_REFRESH_HISTORY.

When a materialized view can't refresh, drop and recreate it to maintain current data.

Long-running REFRESH MATERIALIZED VIEW operation

The REFRESH MATERIALIZED VIEW command executes as a normal query on your cluster.

To verify query execution, take the following actions:

• To check active queries on the data, use STV_INFLIGHT.
• To monitor current WLM query states, use STV_WLM_QUERY_STATE.
• To review active queries and query steps on compute nodes, use STV_EXEC_STATE.

To monitor REFRESH MATERIALIZED VIEW operations, use the following system views:

• To view any current updates on tables in the database, use STV_LOCKS.
• To view the service class configuration for WLM, use STV_WLM_SERVICE_CLASS_CONFIG.
• To determine whether the materialized view received an incremental or full refresh, use SVL_MV_REFRESH_STATUS.

If your REFRESH MATERIALIZED VIEW performance is slow, then see Query performance improvement.

Hidden refresh status during active workload

Amazon Redshift gives priority to your workloads over autorefresh. This prioritization can halt autorefresh to maintain your workload's performance and delay updates to some materialized views. Some situations might require more predictable refresh behavior for your materialized views.

To facilitate predictable refresh behavior, take the following actions:

• Run REFRESH MATERIALIZED VIEW manually.
• Schedule refreshes through the Amazon Redshift scheduler API or console.