Migration fails when other objects were dependent on a dropped and recreated materialized view

[idpaterson]

pgschema properly drops and recreates a materialized view when changes are made to its schema. However, it does not search for objects dependent on that materialized view, so this fails with ERROR: cannot drop materialized view my_mat_view because other objects depend on it. In order for this to work, pgschema would need to determine everything that is dependent on the materialized view (and everything dependent on those things and so on), drop them all, recreate the materialized view, then recreate all of the dependencies.

Alternatively, perhaps views dependent on the materialized view could be replaced to point at a new copy of the materialized view before the old is dropped. That would avoid cascading more than one step away from the materialized view. After the new mat view is recreated with a temporary name, update the views to point at it instead, then delete the original mat view and rename the temporary one. This works well with the view facade approach where each materialized view has a SELECT * FROM ... view with which the rest of the database interacts. That pattern ensures that only one object interacts directly with the materialized view and temporarily pointing that single view elsewhere is less painful than tracking down and recreating all dependents.

Does this seem like it would be possible, or is it outside of the scope of pgschema? I tried the latest source build and also compared against pg-schema-diff which behaved the same way.

Example

Let's start with a simple schema with one table, one materialized view that depends on the table, and one view that depends on the materialized view:

--
-- pgschema database dump
--

-- Dumped from database version PostgreSQL 14.20
-- Dumped by pgschema version 1.6.1


--
-- Name: my_table; Type: TABLE; Schema: -; Owner: -
--

CREATE TABLE IF NOT EXISTS my_table (
    id varchar
);

--
-- Name: my_mat_view; Type: MATERIALIZED VIEW; Schema: -; Owner: -
--

CREATE MATERIALIZED VIEW IF NOT EXISTS my_mat_view AS
 SELECT my_table.id
   FROM my_table;

--
-- Name: my_view; Type: VIEW; Schema: -; Owner: -
--

CREATE OR REPLACE VIEW my_view AS
 SELECT my_mat_view.id
   FROM my_mat_view;

Then, change the materialized view in a way that requires it to be dropped and recreated, such as by adding a new column.

CREATE MATERIALIZED VIEW IF NOT EXISTS my_mat_view AS
 SELECT my_table.id,
   'foo' as "bar"
   FROM my_table;

The plan drops the materialized view, but in order to succeed it would also have to drop the dependent view and recreate it after.

Plan: 1 to modify.

Summary by type:
  materialized views: 1 to modify

Materialized views:
  ~ my_mat_view

DDL to be executed:
--------------------------------------------------

DROP MATERIALIZED VIEW my_mat_view RESTRICT;

CREATE MATERIALIZED VIEW IF NOT EXISTS my_mat_view AS
 SELECT my_table.id,
    'foo'::text AS bar
   FROM my_table;

Applying the plan errors out:

Executing group 1/1...
  Executing 2 statements in implicit transaction
Error: failed to execute concatenated statements in group 1: ERROR: cannot drop materialized view my_mat_view because other objects depend on it (SQLSTATE 2BP01)

[tianzhou]

I am going to address the most common case as shown in the bug report. View that depends on materialized view.

[idpaterson]

Well done, this handles the common case nicely! If I have a materialized view A with a view B depending on it the view is dropped, then the materialized view, then both are recreated. If a second view C depends B then the plan drops C, then B, then A and recreates them all. Very nice!

The only issue I ran into was if view C did not already exist in the database. In that scenario pgschema created the new view C as the first step of the migration, then tried to drop view B in the second step:

CREATE OR REPLACE VIEW view_c AS
 SELECT * FROM view_b;

DROP VIEW IF EXISTS view_b RESTRICT;

DROP MATERIALIZED VIEW mat_view_a RESTRICT;

... recreate A and B

[tianzhou]

@idpaterson do you mean you change mat_view_a and create view_c in the same migration?

[idpaterson]

Sorry, yes that is what I had attempted. I didn't have a particularly compelling reason to do that, was just testing out the latest pgschema logic.

[tianzhou]

That's a more edged case. The PR does handle change mat_view_a and create view_b in the same migration.