feat(postgres): render DDL with pg_dump when it is available - #200
Merged
Merged
Conversation
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>
|
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.



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.
pg_dump14 fixed, 0 regressed — verified by running the same corpus against the same code with and without
pg_dumponPATH, 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:
Four things that had to be right, each found by getting it wrong
--no-ownerbelongs onpg_restore. Onpg_dump -Fcit is accepted and silently ignored, and ownership statements come out anyway.pg_restore -Lreplays in the order given, not dependency order. Reversing a list emitted a table before its types. The listing is filtered, never re-sorted.ALTER TABLE … ADD GENERATEDarrives under aSEQUENCEentry named after the sequence:function public.h_f() does not exist); reproducing a partition's propagated constraints producedmultiple primary keys for table.Nothing is required
pg_dumpcannot 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 thatpg_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:
and
DBDIFF_PG_DUMP_RENDERER=offpins 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
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