As background, I work with many materialized views which are based on PostGIS spatial queries, some of which take days to refresh (these views are infrequently refreshed whenever the underlying spatial data (ie road networks) are updated). I then have many other views which are dependent on these materialized views.
While using materialized views in general works well, it becomes a nightmare when I need to alter the definition of one of the views (for instance, changing a join condition or adding new columns). Since there's no equivalent of
CREATE OR REPLACE ... for materialized views, I end up having to delete and then recreate all the dependent objects.
Recently I've started "wrapping" all my materialized views in standard views as a way around this. So basically every materialized view has a corresponding standard view which selects all columns direct from the materialized view. Then no dependents directly reference the materialized view, they only ever reference the wrapper view. This allows me to temporarily replace the definition of the wrapper to point at a different data source (not the materialized view), so I can then alter the definition of the materialized view and then lastly re-direct the wrapper back to the materialized view. Phew! It works, and avoids the need for the cascading drop/recreate object process, but still seems very clunky.
Is there a best practice process for working with materialized views to overcome these limitations? What's the usual approach to take here to avoid the cascading delete/recreate steps?