1

I am using Postgres as my DB and I have a bunch of Materialized Views that are created from a view.

The MVs are in Schema B and Views are in Schema A.

They are created like create SchemaB.mv1 as select * from SchemaA.vw1.

However, every time the underlying view is updated, my materialized view is being dropped.

My underlying view is created through DBT using the run command. According to this documentation, DBT drops and re-creates the views that are created.

Because of this drop and re-create process, I am wondering if this causes my MVs drop?

If the underlying table/view of the MV is dropped, is the MV also dropped?

1 Answer 1

2

If the underlying table/view of the MV is dropped, is the MV also dropped?

In a way. If a view has a dependency, such as a materialized view, an attempt to simply DROP VIEW would fail with:

ERROR:  cannot drop view v_test because other objects depend on it 
DETAIL:  materialized view mv_test depends on view v_test 
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

If you then take the hint and do DROP VIEW .. CASCADE, all dependencies (and their dependencies) will also be dropped.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.