Skip to content

Bug: Views and New column create are not ordered correctly #414

@PadenZach

Description

@PadenZach

When a single apply both adds a new column and creates a view that references that column, the plan
puts the ALTER TABLE ... ADD COLUMN and the CREATE OR REPLACE VIEW into the same implicit transaction
(Transaction Group 1) but emits the view DDL before the column add. Postgres parses the view body,
can't find the column, and the whole transaction aborts with 42703 column "" does not exist.

Expected:

pgschema should order an object whose body references a column after the ALTER TABLE that
adds it — the same dependency-awareness it already applies across transaction groups should hold
within a group.

Repro

Starting state:

  CREATE TABLE foo (id bigint PRIMARY KEY);

Desired state (foo.sql):

  CREATE TABLE foo (
      id bigint PRIMARY KEY,
      run_id uuid
  );

  CREATE OR REPLACE VIEW foo_view AS
  SELECT id, run_id FROM foo WHERE run_id IS NOT NULL;
  pgschema plan --file foo.sql --schema public --output-json plan.json
  pgschema apply --plan plan.json --schema public --auto-approve

Actual plan

  -- Transaction Group #1
  CREATE OR REPLACE VIEW foo_view AS
   SELECT id, run_id FROM foo WHERE run_id IS NOT NULL;

  ALTER TABLE foo ADD COLUMN run_id uuid;

  Apply:

  Executing group 1/1...
    Executing 2 statements in implicit transaction
  Error: failed to execute concatenated statements in group 1:
  ERROR: column "run_id" does not exist (SQLSTATE 42703)

  Expected plan

  -- Transaction Group #1
  ALTER TABLE foo ADD COLUMN run_id uuid;

  CREATE OR REPLACE VIEW foo_view AS
   SELECT id, run_id FROM foo WHERE run_id IS NOT NULL;

Related

#307 - Handles view to view deps
#240 - Discusses dependency issues/mechanism

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions