I’m attempting to replace a Redshift materialized view with a new table definition that has updated fields. Previously i would just DROP MATERIALIZED VIEW accounts and then immediately CREATE MATERIALIZED VIEW accounts and end users would be none-the-wiser. However, someone has created their own view based on my view. When i try to drop the original accounts view I get an error that it cant be dropped since there is a dependent view.
I tried:
drop materialized view accounts restrict false;but that throws an error.alter materialized viewdoesn’t seem to allow altering the definition of the table, just the flags used to refresh.CREATE or REPLACE MATERIALIZED VIEW accountsas well asCREATE or REPLACE VIEW accounts. One threw a syntax error the other said “Materialized views cannot be replaced”.
How can I replace the current view with the updated view without cascade dropping the dependent view?
You can’t.
If you want to make this change seamless you can gather all the DDL for dependent views, including ownership and grants, and rebuild them after a DROP CASCADE. You can even do this in a transaction so no one sees them missing.
Look at the awslabs GitHub repo and there some good scripts to get this up and working.
You can tell users to define only late binding views so that the underlying tables/views can be changed. This is helpful if you expect this type of changes to keep happening
1