Skip to content

fix(pgsql): pin session output formats so values survive server settings - #314

Merged
jasdeepkhalsa merged 1 commit into
masterfrom
fix/pin-session-formats
Nov 20, 2026
Merged

jasdeepkhalsa merged 1 commit into
masterfrom
fix/pin-session-formats

Conversation

@jasdeepkhalsa

@jasdeepkhalsa jasdeepkhalsa commented Nov 19, 2026 •

Copy link
Copy Markdown
Member

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:

Source setting What happened
DateStyle = 'SQL, DMY' 3 April 2026 printed as 03/04/2026; a month-first target stored 4 March. Dates in 4713 BC printed with an LMT zone no target can read.
extra_float_digits = 0 doubles rounded to 15 digits (0.30000000000000004 arrived as 0.3); a re-diff under the same setting could not see it
TimeZone differing between servers the same instant printed differently on each side, so every timestamptz row compared unequal and was rewritten on every run

Supabase is affected by default

Supabase's PostgreSQL images ship extra_float_digits = 0 in their postgresql.conf, both 15.x and 17.x. So with a Supabase project as the source, every float8/real value was read rounded to 15 significant digits:

  • Synced into a target holding the exact values, one 1,334-row table produced 1,334 UPDATEs, each truncating a value (1333.3333333333333 → '1333.33333333333'). With this change the tables diff as identical.
  • The largest double (1.7976931348623157e308) rounded up to 1.79769313486232e+308, which is out of range, so the migration failed.
  • Between two Supabase projects, both sides print rounded, so a second diff couldn't see the loss.

Fix

Each PostgreSQL connection now pins DateStyle, IntervalStyle, extra_float_digits and bytea_output to the values pg_dump uses, and TimeZone to UTC. They are applied after the connection URL's own options, 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

SessionFormatsRoundTripPostgresTest sets a source database to every skewed setting above with ALTER DATABASE … SET:

  • UP applied to a default-configured target reproduces the source's rows exactly (compared under one fixed set of formats), and a second diff finds nothing;
  • the same rows on two differently configured databases diff as identical.

Both tests fail without the fix.

MySQL

MySQL has the same class of problem: TIMESTAMP values are read and written in the session time_zone, so two servers in different zones shift them silently. Fixing that also needs a time_zone header in the generated SQL, so it follows in a separate PR.

🤖 Generated with Claude Code

… 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>
@github-actions github-actions Bot added bug mysql Related to Mysql php Pull requests that update php code postgres Related to Postgres labels Nov 19, 2026
@sonarqubecloud

Copy link
Copy Markdown

@jasdeepkhalsa
jasdeepkhalsa merged commit 256f8b2 into master Nov 20, 2026
84 checks passed
@jasdeepkhalsa
jasdeepkhalsa deleted the fix/pin-session-formats branch November 20, 2026 07:47
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>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

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

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant