fix(postgres): replace a changed view that others read, instead of failing to drop it - #273

Problem

A changed view was emitted as DROP VIEW IF EXISTS v; CREATE VIEW v .... PostgreSQL refuses to drop a view that other views, materialised views or policies read:

ERROR:  cannot drop view base_v because other objects depend on it

so the whole migration failed and rolled back, unrelated changes included. Reproduction:

-- both sides
CREATE TABLE items (id int PRIMARY KEY, colour text, grp int);
CREATE VIEW base_v AS SELECT id, colour FROM items WHERE grp = 1;
CREATE VIEW on_top AS SELECT * FROM base_v;
-- source only
CREATE OR REPLACE VIEW base_v AS SELECT id, colour FROM items WHERE grp = 2;

Fix

ViewReplacementPlan decides, per changed view and per direction:

• In place: when the new columns begin with the old ones (same names, types and collations), the view is changed with CREATE OR REPLACE VIEW. What reads it keeps working, and its grants, comments and triggers stay. Grants are then set exactly to the side being migrated to.
• Around its dependants: otherwise (a column renamed, retyped or removed; always for a materialised view), everything that reads it is dropped first, deepest first, and put back complete afterwards, as the side being migrated to has it. That covers views and materialised views at any depth, with their options, grants, comments, triggers and indexes, plus policies on other tables and the view's own triggers. PostgresColumnDependants::ofRelation finds them, reusing the column-change machinery.

Ordering issues found along the way, all fixed here:

• Dependency order: changed views now run in dependency order, not by name. A view sorting first alphabetically could be changed before the view it reads.
• New views reading a changed view: these now run after it. They used to be created before it and read its old columns.
• What a bracket leaves alone: whatever another diff creates or replaces later in the same direction (a new policy or trigger, a changed dependant view) is left to that diff.
• Readers reverted too early in the DOWN: a changed policy, trigger or materialised view that reads the view was reverted before it, against its new columns. It now only comes off at that point, and the bracket puts it back.
• Column type changes: a changed view under a column type change puts back what reads it once it exists again. The column change used to recreate those first, before the view existed.
• Multi-schema diffs: views are ordered across all schemas together.
• Comments: comments on policies and triggers that a bracket recreates are restored.

Tests

ViewReplacementRoundTripPostgresTest round-trips every case: UP applies and converges, and DOWN restores the original, with view metadata (options, grants, comments, triggers, indexes) compared directly.

• the reproduction above, replaced in place;
• a column added, in place going up and around dependants going down;
• a renamed column, with views three deep, a materialised view with an index, a policy, an INSTEAD OF trigger, grants and comments;
• dependency order against name order;
• a view over a changed materialised view;
• a changed view over a retyped column;
• a reader moved from one view to another;
• new, changed and dropped policies and triggers around the view, plus kept ones with comments;
• a changed dependant that the bracket drops;
• a dropped dependant and a reader in another schema.

All fail on master. Run on PostgreSQL 14, 15, 16, 17 and 18. The programmable_objects fixtures change by one line each: a view that gains a column is now CREATE OR REPLACE VIEW.

🤖 Generated with Claude Code