Composite FK referenced column order incorrect when FK order differs from table column order

[curthasselschwert]

Description

When dumping a composite foreign key constraint where the FK column order differs from the table's column definition order (attnum), pgschema outputs the referenced columns in the wrong order. This causes the generated DDL to fail when applied.

How I Found This

I was setting up pgschema on an existing project. I ran pgschema dump to extract our schema, then tried to apply it to a fresh database. The apply failed with foreign key type mismatch errors on tables with composite FKs where the FK column order differed from the table's column definition order.

I used Claude to help investigate the codebase (I don't have Go experience) and we traced it to the ForeignOrdinalPosition value in buildConstraints.

Reproduction

CREATE TABLE parent_table (
    col_a integer NOT NULL,
    col_b integer NOT NULL,
    PRIMARY KEY (col_a, col_b)
);

CREATE TABLE child_table (
    id serial PRIMARY KEY,
    -- Columns defined in opposite order from FK
    col_b integer NOT NULL,  -- lower attnum
    col_a integer NOT NULL,  -- higher attnum
    CONSTRAINT fk_child_parent
        FOREIGN KEY (col_a, col_b) REFERENCES parent_table(col_a, col_b)
);

Run pgschema dump and observe the FK output.

Expected:

CONSTRAINT fk_child_parent FOREIGN KEY (col_a, col_b) REFERENCES parent_table(col_a, col_b)

Actual:

CONSTRAINT fk_child_parent FOREIGN KEY (col_a, col_b) REFERENCES parent_table(col_b, col_a)

The referenced columns are in the wrong order, causing type mismatch errors when the DDL is applied:

ERROR: foreign key constraint cannot be implemented (SQLSTATE 42804)

Root Cause

In ir/inspector.go, when building FK constraints, ForeignOrdinalPosition (which is fa.attnum - the column's position in the foreign table) is used for the referenced column's Position instead of using the local column's constraint position. Since local and referenced columns are paired in the FK definition, they should share the same position value.

Suggested Fix

In buildConstraints, use the local column's constraint position for the referenced column:

refConstraintCol := &ConstraintColumn{
    Name:     refColumnName,
    Position: position,  // Use local column's constraint position, not fa.attnum
}

I've created a patch and test case locally and verified all tests pass. Happy to submit a PR if that would be helpful.

Environment

• PostgreSQL: 17
• pgschema: v1.6.1 (latest from main branch)

[tianzhou]

@curthasselschwert happy to review the PR

[curthasselschwert]

@tianzhou PR opened.