New table's inline FK ordered before new UNIQUE constraint/index on pre-existing referenced table — apply fails with SQLSTATE 42830

[kinghuang]

Summary

When an incremental migration both (a) adds a UNIQUE constraint or unique index to a pre-existing table and (b) creates a new table whose foreign key references those columns, the generated plan orders the new table's inline FK before the unique constraint/index it depends on. pgschema apply then fails with:

ERROR: there is no unique constraint matching given keys for referenced table "parent" (SQLSTATE 42830)

Applying the same desired state to an empty schema succeeds (everything is inline in CREATE TABLE), so the bug is specific to the incremental path.

This looks adjacent to #248 (fixed by #249, "topological sort for modified tables' constraint dependencies"), but is a distinct shape: #248 had both tables pre-existing (FK added via ALTER TABLE), and that case is indeed fixed — I verified it applies cleanly on v1.12.0. The failing case here is an FK inline in a new table's CREATE TABLE, which apparently isn't included in that dependency sort. Unrelated prior reports from me: #501 (VIRTUAL generated columns), #502 (COMMENT ON COLUMN name collision).

Environment

• pgschema v1.12.0 (1.12.0@62d09975 darwin/arm64) — also reproduces on v1.11.0
• PostgreSQL 18.4 (postgres:18 in Docker) — also reproduces on postgres:14; the mis-ordering is in plan generation, not PG-version-specific
• Target schema: s

Reproduction

Current DB state:

CREATE SCHEMA s;
CREATE TABLE s.parent (id uuid PRIMARY KEY, tenant text NOT NULL);

Desired state file (desired.sql) — variant 1, unique as table constraint:

CREATE TABLE parent (
    id uuid PRIMARY KEY,
    tenant text NOT NULL,
    UNIQUE (id, tenant)
);

CREATE TABLE child (
    id uuid PRIMARY KEY,
    parent_id uuid,
    tenant text NOT NULL,
    FOREIGN KEY (parent_id, tenant) REFERENCES parent (id, tenant)
);

pgschema plan --host localhost --port 5432 --user postgres --db repro \
  --schema s --file desired.sql --output-sql stdout

Generated DDL — the FK-bearing CREATE TABLE precedes the UNIQUE it references:

CREATE TABLE IF NOT EXISTS child (
    id uuid,
    parent_id uuid,
    tenant text NOT NULL,
    CONSTRAINT child_pkey PRIMARY KEY (id),
    CONSTRAINT child_parent_id_tenant_fkey FOREIGN KEY (parent_id, tenant) REFERENCES parent (id, tenant)
);

ALTER TABLE parent
ADD CONSTRAINT parent_id_tenant_key UNIQUE (id, tenant);

pgschema apply output:

Executing group 1/1...
  Executing 2 statements in implicit transaction
Error: failed to execute concatenated statements in group 1: ERROR: there is no unique constraint matching given keys for referenced table "parent" (SQLSTATE 42830)

Variant 2, unique as index — replace the table constraint with CREATE UNIQUE INDEX parent_id_tenant_key ON parent (id, tenant); in the desired state. The new index on the existing table is emitted as CREATE UNIQUE INDEX CONCURRENTLY, which lands in a later execution group than the transaction containing the new table's inline FK:

-- Transaction Group #1
CREATE TABLE IF NOT EXISTS child (
    id uuid,
    parent_id uuid,
    tenant text NOT NULL,
    CONSTRAINT child_pkey PRIMARY KEY (id),
    CONSTRAINT child_parent_id_tenant_fkey FOREIGN KEY (parent_id, tenant) REFERENCES parent (id, tenant)
);

-- Transaction Group #2
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS parent_id_tenant_key ON s.parent (id, tenant);

Apply fails in group 1 with the same SQLSTATE 42830.

Expected behavior

Dependency-aware ordering: a unique constraint/index that is the FK target of another change in the same plan must be created before the FK. For variant 1, emit the ALTER TABLE parent ADD CONSTRAINT ... UNIQUE first (or split the FK out of CREATE TABLE child into a later ALTER TABLE ... ADD CONSTRAINT). For variant 2, the FK needs to move to a group after the concurrent index build.

Observed behavior

FK emitted before its referenced unique constraint/index; apply fails with SQLSTATE 42830 and the migration cannot proceed.

Control cases (both pass on v1.12.0)

• Same desired state applied to an empty schema → success.
• The Bug: Incorrect constraint ordering causes failure in apply #248 shape (both tables pre-existing, FK added via ALTER TABLE ... NOT VALID + VALIDATE) → success; fix: topological sort for modified tables' constraint dependencies #249's sort handles it.

Workaround

Land the unique constraint/index in a separate, earlier migration (apply a desired state containing only the parent change first, then the full desired state). Verified working on v1.12.0.