Repository navigation
fix(postgres): types, domains and partitions as real schemas use them - #285
Merged
Merged
Conversation
Run against widely used public schemas (AdventureWorks, Pagila, GitLab's
structure.sql, pgvector), PostgreSQL migrations were wrong in six ways the
synthetic corpus had no case for.
- An array column was written as the catalog's internal name, `_varchar`:
varchar(20)[] became varchar[], numeric(10,2)[] numeric[], bit(4)[]
bit(1)[], and an array of a mixed-case or non-public type (`_Mood`,
`_status`) did not resolve. The re-diff then compared the same internal
names and called the columns identical. Arrays are now spelled by the
server, format_type(atttypid, atttypmod).
- A type modifier on an extension type was dropped: vector(1536) became
vector, on which an HNSW index fails ("column does not have dimensions").
- A domain in `public` was written bare. `"Name"` folded to PostgreSQL's own
`name` type, so its columns were created with a different type, and
`"Flag"` failed as unknown. It is quoted wherever it needs to be.
- A changed domain was dropped and recreated, which fails while any column
uses it. A default, NOT NULL or CHECK change is now made with ALTER DOMAIN;
only a new base type or collation still replaces it.
- A NOT VALID check was written into CREATE DOMAIN, which does not accept
it. It is added after the domain exists, still unvalidated.
- A column added to a partitioned table was also added to every partition,
which PostgreSQL refuses ("cannot add column to a partition"); a column a
table only inherits is no longer added or dropped on it. And a partition in
another schema than its parent named the parent in its own schema.
MySQL, SQLite and Dolt are unaffected: every change is in PostgreSQL-only
code, and the shared TableSchema path only skips columns PostgreSQL reports
as inherited, which other engines never do.
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
PostgreSQL 18 adds virtual generated columns, and makes them the default. Every generated column was written STORED, so a virtual one was created stored, and the comparison could not tell them apart: the kind was not in the pre-scan hash, the column definition parser skipped the keyword, and a change between the two was not a regeneration. The kind is now read from attgenerated, written as VIRTUAL or STORED, hashed, and compared; a change between them drops and re-adds the column, as PostgreSQL cannot switch it in place. A generated column recreated around a dependency's retype keeps its kind. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
GitLab's `uploads` takes its id from a sequence it owns, and every partition carries a copy of the default. Counted as further uses, those copies made the sequence look shared, so it was modelled standalone and created — while the column was still written bigserial, which made a second sequence (`uploads_id_seq1`) that every partition's default then pointed at. A default on a column the table inherits is no longer counted. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
Keeps PostgresSchemaHelper within the 20-method limit; no behaviour change. Co-Authored-By: Claude Opus 5.5 <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.



Running DBDiff against widely used public schemas (AdventureWorks, Pagila, GitLab's
structure.sql, a pgvector table) turned up eight PostgreSQL shapes that generated wrong or unrunnable migrations. None of them is in the synthetic corpus. Four were silent: the migration ran, and a re-diff called the result identical.varchar(20)[],numeric(10,2)[],timestamptz(3)[],bit(4)[]_varchar,_numeric, … — modifier lost, and the re-diff saw no differencecharacter varying(20)[], … spelled byformat_type"Mood"[],app.status[])_Mood,_status— type does not exist"Mood"[],app.status[]vector(1536)(any extension type with a modifier)vector— an HNSW index on it then failsvector(1536)"Name"inpublicName, which folds to the built-innametype"Name"CHECK … NOT VALIDDROP DOMAIN+CREATE DOMAIN … NOT VALID— a syntax error, and the drop fails while columns use itALTER DOMAIN … ADD CONSTRAINT … NOT VALIDADD COLUMNon the parent and every partition — refusedbigserial, so a second…_seq1sequence fed every partitionSTORED, and stored vs virtual compared as identicalVIRTUAL; a change between them drops and re-adds the columnThe array and domain spellings matter for the built-in renderer, which runs whenever no
pg_dumpat least as new as the server is installed. Domain changes and partitioned tables were wrong under both renderers.A domain is still replaced when its base type or collation changes, since
ALTER DOMAINcan't do that.Tests
tests/RealWorldShapesPostgresTest.phpcovers each shape as a live round trip (UP, re-diff empty, DOWN restores). It runs under both renderers. Because the re-diff was itself blind to the lost modifiers, each case also compares the catalog directly:format_typeper column, domain constraints withconvalidated, and partition parents.tests/Unit/PostgresColumnTypeTest.phpcovers type spelling.ObjectKindsSQLTest, the domain cases now pinALTER DOMAIN, and a separate case keeps the base-type change as a replacement.master, 9 of the 11 new round-trip cases and 7 of the 11 new unit cases fail. The two round-trip cases that pass onmasterare the pg_dump-renderer variants of the domain and array tests, whichpg_dumpalready rendered correctly.MySQL, SQLite and Dolt are unaffected. Every change is in PostgreSQL-only code. The shared
TableSchemapath only skips columns PostgreSQL reports as inherited, which the other engines never report. The MySQL matrix in CI confirms it.🤖 Generated with Claude Code