How do I resolve the "Underlying table with oid of view does not exist" error that I receive when refreshing Redshift materialized views?

Short description

When you create a materialized view, the content reflects the state of the underlying database tables at that time. Data in the materialized view is unchanged, even if the data in the underlying tables are changed. To update the data in the materialized view (full refresh or incremental refresh), use the REFRESH MATERIALIZED VIEW statement to manually refresh materialized views.

When you manually refresh a materialized view, you might intermittently receive the following error:

SQL Error [<5700]: Underlying table with oid (example-oid) of view (example-materialized-view-name) does not exist

This error is caused by a concurrent transaction that occurs at the same time that the materialized view is being manually refreshed.

Example scenario:

• Transaction 1: REFRESH MATERIALIZED VIEW is initiated.
• Transaction 2: select * from (example-materialized-view-name)

In the preceding example, both transactions occur at the same time. Transaction 2 is conflicting with transaction 1 and this causes an error. Transaction 2 can also be a select on a different view that has the materialized view as a base object. This is default Amazon Redshift behavior.

Resolution

To resolve this error, complete the following steps:

1.    Apply LOCK on all the base tables of the materialized view before refreshing as follows:

• Lock all tables affected by the transaction, including tables affected by read-only SELECT statements inside the transaction.
• Lock all tables in the same order regardless of the order that operations are performed in.
• Lock all tables at the beginning of the transaction before performing any other operations.

LOCK [TABLE] (example-table-name),(example-table-name),(...)

The preceding command makes the refresh query of the materialized view wait until the acquired locks are released on the base table (auto or manual).

Note: The LOCK command obtains a table-level lock in the ACCESS EXCLUSIVE mode. Locking a table in this method causes read and writes on the table to wait when they're attempted from other transactions. A table lock created by one user temporarily prevents another user from selecting data from that table or loading data into it. The lock is released when the transaction that contains the LOCK command completes.

2.    Schedule your REFRESH MATERIALIZED VIEW during non-business hours. This makes sure that your transaction doesn't occur with other user transactions.