We are using Materialised Views in Redshift to house queries used in our Looker BI tool.
We regularly refresh our base data and so these views are required to be refreshed every hour, and so we have set these views to auto refresh with the following command.
ALTER MATERIALIZED VIEW view_name AUTO REFRESH YES
This works for a period of time, and then just ceases with no failure messages showing in SVL_MV_REFRESH_STATUS, the refreshes simply cease across all mat. views.
At this point, we have tried to manually refresh the mat. views using REFRESH MATERIALIZED VIEW but this query hangs for long periods of time and does not get killed until we manually cancel the PID some time later.
After failing to refresh the views manually, we then attempted to DROP the mat. views but this query also hangs and needs to be manually killed using the PID.
The only solution we have found thus far is rebooting the Redshift cluster, which obviously is not a robust solution and doesn't get us to the root of the problem.
Has anybody encountered this issue and could shed some light on it?
Thanks!