Repository navigation
fix(pgsql): pin session output formats so values survive server settings - #314
Merged
Merged
Conversation
… settings DBDiff compares values as PostgreSQL prints them and writes that text into its SQL. How a value prints is a setting a server, database or role can change, and DBDiff used whatever it found: - DateStyle 'SQL, DMY' printed 3 April 2026 as 03/04/2026; a month-first target stored 4 March. Dates in 4713 BC printed with an LMT zone that no target can read back. - extra_float_digits = 0 rounded doubles to 15 digits (0.30000000000000004 arrived as 0.3), and a re-diff under the same setting could not see it. - Two servers in different time zones printed the same instant differently, so every timestamptz row compared unequal and was rewritten on every run. Each connection now pins DateStyle, IntervalStyle, extra_float_digits and bytea_output to the values pg_dump uses, and TimeZone to UTC, after the URL's own options so a URL cannot unpin them. Output from default-configured servers is unchanged. MySQL has the same class of problem for TIMESTAMP columns and the session time_zone; that needs a header in the generated SQL as well and follows separately. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
|
This was referenced Nov 19, 2026
jasdeepkhalsa
added a commit
that referenced
this pull request
Nov 20, 2026
…315) A MySQL `TIMESTAMP` is stored as an instant and printed in the session's `time_zone`, which defaults to the server's. DBDiff read values in the source server's zone and wrote that wall-clock text, so applying the migration in a session in another zone stored a different instant. For example, `10:00` read on a server at `+13:45` and applied at `+00:00` moved by 13h45m. A second diff, reading each side in its own zone, then reported the databases identical, so nothing flagged it. ### Fix - DBDiff's MySQL connections use `time_zone = '+00:00'`, so `TIMESTAMP` values are read and compared as instants. - A migration that changes a table with a `TIMESTAMP` column is wrapped the way mysqldump does it. That covers the table's rows, and also its definition, since `TIMESTAMP` defaults are literals too. The wrapper saves the session's zone, sets UTC, and restores it at the end, in both UP and DOWN: ```sql SET @DBDIFF_TIME_ZONE = @@SESSION.time_zone; SET time_zone = '+00:00'; ... SET time_zone = @DBDIFF_TIME_ZONE; ``` - Migrations that don't touch a `TIMESTAMP` column are unchanged. `DATETIME` is not zone-converted by MySQL and needs nothing. ### Tests - `TimestampZoneMySQLTest`: - It reads on a server whose global zone is `+13:45` and applies the output in a session at `-08:00`. The instants (`UNIX_TIMESTAMP`) then match the source, and the session gets its own zone back. - Identical instants diff as identical. - A migration with only `DATETIME` carries no zone lines. - The first test fails without the fix. - The MySQL 8, MySQL 9 and Dolt golden files gain the wrapper where their fixtures have `TIMESTAMP` columns, re-recorded against the servers. - The new test is skipped on Dolt. Dolt already refuses the data diff of any table with a date or time column (`Incorrect datetime value: ''`), with or without this change, which is why the data cases in `DBDiffComprehensiveTest` skip there too. That's a separate issue. PostgreSQL's counterpart (session `DateStyle`, `extra_float_digits` and `TimeZone`) is #314; each PR stands alone. 🤖 Generated with [Claude Code](https://claude.com/claude-code) 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.



DBDiff compares values as PostgreSQL prints them and writes that text into its SQL. How a value prints is a session setting that a server, database or role can change, and DBDiff used whatever it found. With a source database set differently from the target, the migration was silently wrong:
DateStyle = 'SQL, DMY'03/04/2026; a month-first target stored 4 March. Dates in 4713 BC printed with anLMTzone no target can read.extra_float_digits = 00.30000000000000004arrived as0.3); a re-diff under the same setting could not see itTimeZonediffering between serverstimestamptzrow compared unequal and was rewritten on every runSupabase is affected by default
Supabase's PostgreSQL images ship
extra_float_digits = 0in theirpostgresql.conf, both 15.x and 17.x. So with a Supabase project as the source, everyfloat8/realvalue was read rounded to 15 significant digits:UPDATEs, each truncating a value (1333.3333333333333→'1333.33333333333'). With this change the tables diff as identical.1.7976931348623157e308) rounded up to1.79769313486232e+308, which is out of range, so the migration failed.Fix
Each PostgreSQL connection now pins
DateStyle,IntervalStyle,extra_float_digitsandbytea_outputto the values pg_dump uses, andTimeZoneto UTC. They are applied after the connection URL's ownoptions, so a URL can't unpin them. Every literal written is then unambiguous under any target settings (ISO dates, explicit offsets). Output from default-configured servers is unchanged: every golden file still matches.Tests
SessionFormatsRoundTripPostgresTestsets a source database to every skewed setting above withALTER DATABASE … SET:Both tests fail without the fix.
MySQL
MySQL has the same class of problem:
TIMESTAMPvalues are read and written in the sessiontime_zone, so two servers in different zones shift them silently. Fixing that also needs atime_zoneheader in the generated SQL, so it follows in a separate PR.🤖 Generated with Claude Code