Skip to content

feat(postgres): render DDL with pg_dump when it is available - #200

Merged
jasdeepkhalsa merged 1 commit into
masterfrom
claude/pgdump-renderer
Sep 1, 2026
Merged

jasdeepkhalsa merged 1 commit into
masterfrom
claude/pgdump-renderer

Conversation

@jasdeepkhalsa

Copy link
Copy Markdown
Member

Reconstructing DDL from the catalog is a large surface, and the built-in renderer reproduces 52 of 90 cases in the shared conformance corpus. pg_dump is the reference implementation, maintained in lockstep with the server.

renderer reproduces
built-in 52 / 90
with pg_dump 66 / 90

14 fixed, 0 regressed — verified by running the same corpus against the same code with and without pg_dump on PATH, so the comparison isolates this change rather than anything else.

No SQL is parsed

A diff needs one object at a time while pg_dump emits a whole schema. Splitting SQL by hand fails on dollar-quoted bodies, semicolons inside string literals and trailing comments — 6 of 6 adversarial cases when I measured it. None of that applies, because pg_dump splits its own output:

pg_dump -Fc          writes an archive
pg_restore -l        lists one entry per object
pg_restore -L file   emits SQL for exactly the entries selected

Four things that had to be right, each found by getting it wrong

  1. --no-owner belongs on pg_restore. On pg_dump -Fc it is accepted and silently ignored, and ownership statements come out anyway.
  2. pg_restore -L replays in the order given, not dependency order. Reversing a list emitted a table before its types. The listing is filtered, never re-sorted.
  3. A table's DDL lives under other names. An identity column's ALTER TABLE … ADD GENERATED arrives under a SEQUENCE entry named after the sequence:
    218;  1259 19929 TABLE    public h
    217;  1259 19928 SEQUENCE public h_id_seq   <- the identity clause
    3277; 1259 19937 INDEX    public h_i
    
    Matching on the table name alone dropped identity options and indexes and scored 34/90 — worse than the renderer it replaced. Related names now come from the catalog.
  4. Triggers and partitions stay with the existing code. Emitting a trigger here put it ahead of the function it executes (function public.h_f() does not exist); reproducing a partition's propagated constraints produced multiple primary keys for table.

Nothing is required

pg_dump cannot be bundled — the released binaries are static PHP. Absent or too old, DBDiff falls back to the built-in renderer and records why. The guard is that pg_dump's major must be at least the server's, since it refuses to read anything newer.

Reproducibility

Because the renderer is chosen from what is installed, the same DBDiff version emits different SQL on different machines. Two mitigations:

-- DBDiff migration
-- Generated: 2026-09-01 00:06:13
-- Renderer: pg_dump

and DBDIFF_PG_DUMP_RENDERER=off pins a run to the built-in renderer — which is also how the fixture-based suites stay deterministic regardless of the machine.

Security

The password travels in the environment, never on the command line where it would be visible to anyone who can list processes. Commands run without a shell, so no argument needs quoting.

Verification

check result
Unit 753
Postgres 55 (8 new)
MySQL 17
SQLite 16 (1 skipped)
PG conformance exit 0
end-to-end generated migration applies and converges

CI installs a version-matched client in the Postgres and conformance jobs — without it the new tests skip and the job would go green having exercised none of this.

🤖 Generated with Claude Code

Reconstructing DDL from the catalog is a large surface and the built-in
renderer reproduces 52 of the 90 cases in the shared conformance corpus.
pg_dump is the reference implementation, maintained in lockstep with the
server. Using it where possible takes that to 66 of 90, with no regressions:
14 cases fixed, 0 broken, verified by running the same corpus against the same
code with and without pg_dump on PATH.

A diff needs one object at a time while pg_dump emits a whole schema, and
splitting SQL by hand goes wrong on dollar-quoted bodies, semicolons inside
string literals and trailing comments — 6 of 6 adversarial cases. None of that
applies here, because pg_dump splits its own output:

  pg_dump -Fc          writes an archive
  pg_restore -l        lists one entry per object
  pg_restore -L file   emits SQL for exactly the entries selected

No SQL is parsed at any point. Four things had to be got right, each found by
getting it wrong first:

  --no-owner belongs on pg_restore; on pg_dump -Fc it is silently ignored.

  pg_restore -L replays entries in the order given, not dependency order, so
  the listing is filtered and never re-sorted.

  A table's DDL is spread across entries under other names — an identity
  column's ALTER TABLE ... ADD GENERATED arrives under a SEQUENCE entry named
  after the sequence. Matching on the table name alone dropped identity
  options and indexes, and scored 34 of 90, worse than the renderer it
  replaced. Related names now come from the catalog.

  Triggers and partitions are left to the existing code. Emitting a trigger
  here put it ahead of the function it executes; reproducing a partition's
  propagated constraints produced "multiple primary keys for table".

Nothing is required. pg_dump cannot be bundled — the released binaries are
static PHP — so an absent or too-old pg_dump falls back to the built-in
renderer with the reason recorded. The version guard is that pg_dump's major
must be at least the server's, since it refuses to read anything newer.

Because the renderer is chosen from what happens to be installed, the same
DBDiff version emits different SQL on different machines. Migrations say which
produced them (`-- Renderer: pg_dump`), and DBDIFF_PG_DUMP_RENDERER=off pins a
run to the built-in renderer — which is also how the fixture-based suites stay
deterministic.

The password travels in the environment, never on the command line where it
would be visible to anyone who can list processes, and commands run without a
shell.

Verified: Unit 753, Postgres 55 (8 new), MySQL 17, SQLite 16, conformance
exit 0. CI installs a version-matched client, since without it these tests
skip and the job would go green having exercised none of this.

Co-Authored-By: Claude <noreply@anthropic.com>
@github-actions github-actions Bot added mysql Related to Mysql php Pull requests that update php code postgres Related to Postgres sqlite Related to Sqlite labels Sep 1, 2026
@sonarqubecloud

sonarqubecloud Bot commented Sep 1, 2026

Copy link
Copy Markdown

@jasdeepkhalsa
jasdeepkhalsa merged commit af4e734 into master Sep 1, 2026
72 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

mysql Related to Mysql php Pull requests that update php code postgres Related to Postgres sqlite Related to Sqlite

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant