Add golden-file edge-case fixtures for column types, constraints, and partial indexes - #177

Change assertExpectedOutput to markTestSkipped when the expected
baseline file is missing. This allows new golden-file tests to be
added incrementally without needing all DB version baselines upfront.
Existing tests with committed baselines are unaffected.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Runs the conformance harness against Postgres 16 in CI. Downloads
regression SQL from pgrust, extracts DDL patterns, and validates
each through DBDiff. Uses continue-on-error since there are known
gaps being tracked. Report uploaded as artifact for review.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Major improvements to the Postgres diff engine based on conformance
testing against Postgres's own regression test suite (42 → 8 failures):

- Diff CHECK constraints: query pg_constraint for check/exclusion
  constraint definitions, previously completely ignored
- Diff PRIMARY KEY constraints: include in fetchConstraints() so
  PK additions and removals are properly detected
- Use ALTER COLUMN instead of DROP+ADD: override changeColumn() in
  PostgresDialect to emit ALTER COLUMN TYPE/SET NOT NULL/DROP NOT NULL/
  SET DEFAULT/DROP DEFAULT instead of destructive drop-and-recreate
- Fix UNIQUE constraint/index duality: filter constraint-backing indexes
  from fetchIndexes() to prevent duplicate CREATE INDEX + ADD CONSTRAINT
- Handle SERIAL columns: create sequences before referencing them in
  ADD COLUMN defaults
- Add CASCADE to DROP COLUMN: prevents failures when columns have
  dependent objects (generated columns, indexes)

Remaining 8 failures are Postgres GENERATED/IDENTITY column edge cases
requiring specialized ALTER syntax (ALTER COLUMN DROP IDENTITY/
DROP EXPRESSION before type changes).

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

…fied columns

PG 14/15 format view definitions with table-qualified column names
(products.id) while PG 16+ uses unqualified names (id). The previous
commit accidentally overwrote these version-specific baselines.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Postgres requires DROP IDENTITY before altering an identity column's
type, and DROP EXPRESSION before altering a generated column. Detect
these via information_schema metadata and emit the prerequisite
statements. When both old and new definitions are generated, only
emit the nullability change.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

When a non-generated column is dropped with CASCADE, Postgres
automatically removes any generated columns whose expression
references it. Detect this dependency and omit the redundant
explicit DROP to avoid "column does not exist" errors.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

New columns are now added in their target ordinal order rather than
alphabetically, fixing schema mismatches when multiple columns are
added simultaneously. Generated/identity columns are altered before
regular columns so their DROP EXPRESSION/IDENTITY removes dependencies
before other columns' types change.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

…xist

When a generated column references other columns that are also changing
type, Postgres blocks the type change. Split the generated column's
change into a DROP (before other changes) + ADD (after), allowing the
dependency to be removed first. Also fixes the cascade detection bug
(was reading from wrong column set) and adds proper handling for
identity-to-identity type changes.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Missing baseline files should cause test failures, not silent skips.
This ensures backwards compatibility issues are caught immediately.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

…tests

Record expected output baselines for the two new test fixtures that were
previously only recorded for PostgreSQL and SQLite, causing all MySQL 8/9
and Dolt CI jobs to fail with "Expected file not found".

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

- Add curly braces around bare continue in TableSchema.php
- Extract changeIdentityColumn/changeGeneratedColumn/changeRegularColumn
  helpers from PostgresDialect::changeColumn to reduce cognitive complexity
  and return count
- Extract generatedColumnOrdering helper from DiffSorter::compare to
  reduce cognitive complexity and return count
- Consolidate early returns in DiffSorter::compareByName

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Rewrite extract-patterns.py to parse PG regression SQL files sequentially,
tracking cumulative table state so each pattern's before_sql includes all
prerequisite columns/constraints from earlier ALTERs.

New capabilities:
- setup_sql field for external dependencies (custom types, domains, functions)
- min_pg_version field for PG 17+ syntax patterns
- skip_reason field to properly categorize untestable patterns
- "no schema diff" cases now count as PASS (correct no-op behavior)
- Runner reports separate counts for SQL errors, version skips, and exclusions

Results: 93 pass / 1 fail (DEFERRABLE) / 11 SQL-error skips / 35 excluded
Previously: 108 pass / 0 fail / 110 skips (many silently hiding errors)

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

- Fix DEFERRABLE constraint handling: PostgresAdapter.fetchConstraints()
  now includes is_deferrable/initially_deferred attributes from
  information_schema on UNIQUE, PK, and FK constraints
- Fix generated column ordering: new AddColumn operations with GENERATED
  ALWAYS AS ... STORED now get isGenerated flag, ensuring they sort after
  ChangeColumn operations that modify referenced columns
- Remove continue-on-error from PG conformance CI job so failures block
- Harden extraction script: detect inline -- error comments, track
  filtered FK constraints to skip orphaned DROP CONSTRAINT, detect PG17
  syntax (SET/DROP EXPRESSION, ADD NOT NULL col), validate column
  existence before accumulating SET/DROP NOT NULL

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Add PostgreSQL 17 and 18 to the conformance testing CI matrix so all
three major versions are validated on every push.

Fix domain column type preservation — columns using custom domains now
emit the domain name instead of the underlying base type. Skip redundant
NOT NULL when the domain itself enforces it (avoids double NOT NULL on
PG18 where named NOT NULL constraints create separate pg_constraint
entries).

Improve extraction script: correct PG17 vs PG18 feature version gates,
add skip reasons for EXECUTE/PREPARE statements and named NOT NULL
constraints, filter unsafe FK accumulations more reliably.

Conformance results: PG16 98/98, PG17 99/99, PG18 106/106 — all pass.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

- Add FK MATCH FULL/PARTIAL and NOT VALID (convalidated) support to
  PostgresAdapter fetchConstraints query
- Fix generated column ordering in DiffSorter: ADD COLUMN operations
  now place generated columns after their dependencies
- Improve extraction script state tracking: use inline-only fail
  comment detection for accumulation (preceding comments describe
  test intent, not statement errors)
- Add ddef5 domain to dependency registry
- Skip self-referencing FK tables in setup_sql to avoid CREATE
  TABLE collisions
- Add proper exclusions: USING cast expressions (4), PRIMARY KEY
  USING INDEX (7), identity-on-default conflicts (2), generated
  column DROP dependencies (2)
- Fix _col_defined check to prevent false-positive column existence
  detection from ALTER text

276 total patterns: 75 excluded, 201 testable (181 PG16, 1 PG17, 19 PG18)

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

- Broaden fail-comment detection to scan all preceding comment lines
- Add DROP COLUMN / FK local-column existence checks in state conflicts
- Add PG18 bare ADD NOT NULL and before_sql named NOT NULL exclusions
- Add self-referencing FK without PK/UNIQUE exclusion
- Add gtest31_1 table to dependency registry for composite-type patterns
- Regenerate patterns.json: 275 total, 87 excluded, 188 testable

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

…apter

- Extract buildConstraintDef() from fetchConstraints() to reduce cognitive complexity
- Extract generatedColumnPairOrder() from generatedColumnOrdering() to reduce return count
- Extract compareWithinTable() from compareByName() to reduce return count

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

- Remove unnecessary temp variable in generatedColumnOrdering
- Remove unused parameter and reduce returns in generatedColumnPairOrder
- Merge UNIQUE/PRIMARY KEY branches in buildConstraintDef to reduce returns

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Extraction now validates every statement against a real Postgres during
pattern building, so before_sql is always self-contained and error tests
are classified by actual PG behaviour — eliminating all silent runtime
skips. detect_skip_reason is reduced to genuine DBDiff feature gaps; PG
rejections are detected live as intentional_error.

DBDiff core: PostgresAdapter now reads PG18 custom-named NOT NULL
constraints (contype='n') and emits them as named constraints, and the
Postgres column-change SQL only asserts NOT NULL/DEFAULT when they
actually change (no more invalid DROP NOT NULL on constraint-backed or
primary-key columns).

Results (0 silent skips on every version):
  PG16 209/209, PG17 212/212, PG18 224/224 testable patterns pass.
  647 unit tests still pass.

Exclusions are now all explicit and honest: live-validated intentional
errors, plus documented DBDiff gaps (unvalidated NOT NULL, NO INHERIT,
PRIMARY KEY USING INDEX, complex USING casts, internal dropped-column
placeholders).

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Postgres changeColumn now compares old and new definitions and skips
redundant SET/DROP NOT NULL and SET/DROP DEFAULT when unchanged. This
produces minimal, correct migrations and avoids an invalid DROP NOT NULL
on primary-key or named-constraint-backed columns during a plain type
change. Golden-file baselines regenerated for PG 14-18 (output is
version-uniform); 647 unit tests and all Postgres comprehensive/e2e
tests pass.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

- Merge resolveParameterisedType into buildColumnType (class back under
  the 20-method limit)
- Replace nested ternary in DiffSorter with an explicit guard

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

PostgreSQL 14/15 render view column references table-qualified via
pg_get_viewdef, unlike PG16+. Regenerated programmable_objects baselines
against real PG14/15 so they carry the version-correct view formatting
alongside the minimized column-change output. All comprehensive Postgres
tests pass on PG14-18.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

…ethods

Reverts the buildColumnType/resolveParameterisedType merge (which raised
returns and cognitive complexity) and instead folds the single-use
getDomainNullability query into fetchColumns, keeping the class within the
20-method limit without adding complexity.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

- PostgresAdapter now carries the convalidated flag for named NOT NULL
  constraints and emits NOT VALID, including default-named unvalidated
  ones (which SET NOT NULL cannot express). Round-trips PG18
  ADD [CONSTRAINT] NOT NULL col NOT VALID.
- CHECK ... NO INHERIT was over-excluded; it already round-trips via
  pg_get_constraintdef. Narrowed the exclusion to the NOT NULL NO INHERIT
  variant only.

Testable patterns: PG16 213/213, PG17 216/216, PG18 248/248 (0 skips).
647 unit tests and Postgres comprehensive/e2e pass.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

Adds a developer-facing note on scripts/pg-conformance (what it does, how
to run it via run.sh, and the PGCONF_DSN live-validation requirement), and
corrects the CI matrix to reflect PostgreSQL 14-18.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>