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
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions