Skip to content

[BUG] Postgres error when changing column type of a table with dependant view #280

@feelixs

Description

@feelixs

Describe the bug**

When a table column's type is changed and a view depends on that column, pg-schema-diff generates ALTER TABLE ... ALTER COLUMN ... SET
DATA TYPE without first dropping the dependent view. Postgres rejects the migration:

ERROR: 0A000: cannot alter type of a column used by a view or rule     

Expected behavior**

The view should be marked for recreation (dropped before, recreated after) whenever a column it depends on has its type altered, the same way it already is when a dependent column is deleted.

To Reproduce

  • Start with a schema containing a table and a view selecting a column from it, e.g.:
    CREATE TABLE foo (id int, val text);
    CREATE VIEW foo_v AS SELECT id, val FROM foo;
  • Change the column type in the new schema:
    CREATE TABLE foo (id int, val varchar(100));
    CREATE VIEW foo_v AS SELECT id, val FROM foo;
  • Run pg-schema-diff plan and apply the generated SQL.
  • The ALTER TABLE foo ALTER COLUMN val SET DATA TYPE varchar(100) statement fails with the error above.

Context

pg-schema-diff version: main (reproduces on current main)
pg-schema-diff usage: LIBRARY / CLI (both)

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions