Postgres Materialized Views keeps getting dropped every time underlying views is updated



  • 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 https://docs.getdbt.com/reference/commands/run , 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?



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2