Query errors during refresh of Redshift Materialized Views

Hello,

I understand that while trying to refresh materialized views, you intermittently get the below error. You do manual refresh every hour of this materialized view.

"Underlying table with oid 1119447 of view <view-name> does not exist."

I would like to inform you that "ERROR: Underlying table with oid 1119447 of view <view-name> does not exist" might be caused due to concurrent transaction that happen at the same time as when the materialized views gets refreshed to incur the changes and doing select operation at same time causes conflict in transaction and results in the error. This is expected behavior in Redshift.

To avoid this, I would suggest to Ideally apply explicit LOCKs[1] on the base tables when they are updating before triggering the refresh which would make the refresh query of the materialized view wait until the acquired LOCKs are released on the base table (auto or manual). So you can directly use the explicit LOCKs on your base tables to avoid the issue.

Also, when you use the LOCK command, please make be sure that you do the following :

• Lock all tables affected by the transaction, including those affected by read-only SELECT statements inside the transaction.
• Lock 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 operations.

The LOCK command obtains a table-level lock in "ACCESS EXCLUSIVE" mode, waiting if necessary for any conflicting locks to be released. Explicitly locking a table in this way causes reads and writes on the table to wait when they are attempted from other transactions or sessions. An explicit 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.

Below is the Syntax for Lock

LOCK [ TABLE ] table_name [, ...]

Here table_name is the name of the table to lock. You can lock more than one table by using a comma-delimited list of table names. If possible please try to schedule refresh in non-business hours so that it doesn't conflict with users based queries.

Please note, if the above solution doesn't work please feel free to raise a support case with AWS so that resource based troubleshooting can be done and the issue be mitigated.

Thank you!

References:

[1] LOCKS: https://docs.aws.amazon.com/redshift/latest/dg/r_LOCK.html
[2] Refreshing a materialized view: https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh.html
[3] Serializable isolation: https://docs.aws.amazon.com/redshift/latest/dg/c_serial_isolation.html
[4] Concurrent write examples: https://docs.aws.amazon.com/redshift/latest/dg/r_Serializable_isolation_example.html