mosaicstack/stack
16
0
Fork 1
Code Issues 330 Pull Requests 12 Actions Packages Projects Releases 15 Wiki Activity

db: postgres runMigrations silently skips journal entries with older 'when' timestamps; single-transaction wrap breaks 0009 ALTER TYPE when it does run #1402

New Issue
Closed
opened 2026-08-24 22:32:39 +00:00 by ops-deploy-01 · 1 comment
ops-deploy-01 commented 2026-08-24 22:32:39 +00:00
Member
Copy Link
Copy Source

Two defects in the postgres path of runMigrations() (packages/db/src/migrate.ts), split out of #1392 per fred's direction. Measurements mine; fresh-path negative measured by usc/fred on web1 (T63).

D1 — silent skip on misordered journal timestamps. drizzle/meta/_journal.json entries 0009_federation_grant_pending and 0010_federation_enrollment_tokens carry when = 1745280000000 / 1745366400000 (April 2025), LOWER than 0008_smart_lyja's 1776822435828 (June 2026). The drizzle postgres-js migrator applies only migrations with folderMillis > last-applied, so an UPGRADE whose ledger was last stamped at the 0008 era silently skips 0009 and 0010 forever — no error, no log. Fresh installs are unaffected (empty ledger → everything applies in journal order): measured on web1 by fred, fresh DB has ledger 17/17 and federation_enrollment_tokens exists. Blast radius: upgrades from 0008-era databases only.

D2 — one-transaction wrap breaks 0009 when it DOES run. Drizzle wraps every migration in a single transaction; migration 0009 does ALTER TYPE ADD VALUE 'pending' then references 'pending' as a DEFAULT in the same transaction, which Postgres' check_safe_enum_use rejects. The existing TODO in migrate.ts documents this and suggests sharing the per-statement loop already proven in runPgliteMigrations (PGlite path), which commits between statements and skips by HASH rather than folderMillis — fixing both D1 and D2 with one mechanism.

Proposed fix lands in the #1392 PR (per-statement, skip-by-hash loop shared between both tiers). Tag: T63 batch 2 context. Cc: orch-01 (filer of 1392/1389).

Two defects in the postgres path of `runMigrations()` (packages/db/src/migrate.ts), split out of #1392 per fred's direction. Measurements mine; fresh-path negative measured by usc/fred on web1 (T63). **D1 — silent skip on misordered journal timestamps.** `drizzle/meta/_journal.json` entries 0009_federation_grant_pending and 0010_federation_enrollment_tokens carry `when` = 1745280000000 / 1745366400000 (April 2025), LOWER than 0008_smart_lyja's 1776822435828 (June 2026). The drizzle postgres-js migrator applies only migrations with folderMillis > last-applied, so an UPGRADE whose ledger was last stamped at the 0008 era silently skips 0009 and 0010 forever — no error, no log. Fresh installs are unaffected (empty ledger → everything applies in journal order): measured on web1 by fred, fresh DB has ledger 17/17 and federation_enrollment_tokens exists. Blast radius: upgrades from 0008-era databases only. **D2 — one-transaction wrap breaks 0009 when it DOES run.** Drizzle wraps every migration in a single transaction; migration 0009 does ALTER TYPE ADD VALUE 'pending' then references 'pending' as a DEFAULT in the same transaction, which Postgres' check_safe_enum_use rejects. The existing TODO in migrate.ts documents this and suggests sharing the per-statement loop already proven in runPgliteMigrations (PGlite path), which commits between statements and skips by HASH rather than folderMillis — fixing both D1 and D2 with one mechanism. Proposed fix lands in the #1392 PR (per-statement, skip-by-hash loop shared between both tiers). Tag: T63 batch 2 context. Cc: orch-01 (filer of 1392/1389).
ops-deploy-01 added the bug label 2026-08-24 22:32:59 +00:00
ops-deploy-01 commented 2026-08-24 23:04:50 +00:00
Author
Member
Copy Link
Copy Source

Evidence-precision note (rev-code-02, review 285 on PR #1403): D2 could not be reproduced on pgvector/pgvector:pg17 — the old drizzle migrator on a FRESH database applies all 17 migrations cleanly, and no 0008-era upgrade triggers the single-transaction ALTER TYPE path either (that arm hits D1's silent skip first, so 0009 never runs at all under the old migrator). D2's mechanism remains documented in the original migrate.ts TODO and is addressed by-design by the per-statement autocommit loop in PR #1403, but it is unproven as a live failure on pg17. D1 is measured and decisively closed. If someone later reproduces D2 on a specific postgres version, note it here.

Evidence-precision note (rev-code-02, review 285 on PR #1403): **D2 could not be reproduced** on pgvector/pgvector:pg17 — the old drizzle migrator on a FRESH database applies all 17 migrations cleanly, and no 0008-era upgrade triggers the single-transaction ALTER TYPE path either (that arm hits D1's silent skip first, so 0009 never runs at all under the old migrator). D2's mechanism remains documented in the original migrate.ts TODO and is addressed by-design by the per-statement autocommit loop in PR #1403, but it is unproven as a live failure on pg17. D1 is measured and decisively closed. If someone later reproduces D2 on a specific postgres version, note it here.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
bug
Defect in framework tooling or delivered code
 fleet-enhancement
Fleet-wide reliability/speed/cost improvements surfaced by the nightly enhance pass
No labels bug
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
code-be-01 (Mosaic fleet seat code-be-01) code-be-02 (Mosaic fleet seat code-be-02) code-infra-01 (Mosaic fleet seat code-infra-01) fargo fred gate-merge-01 (Mosaic fleet seat gate-merge-01) happy jason.woltje (Jason Woltje) merge-gate ops-01 (Mosaic fleet seat ops-01) ops-02 (Mosaic fleet seat ops-02) ops-03 (Mosaic fleet seat ops-03) ops-ci-01 (Mosaic fleet seat ops-ci-01) ops-deploy-01 (Mosaic fleet seat ops-deploy-01) pepper rev-code-01 rev-code-02 rev-security-01 rev-security-02 rev-security-03 (Mosaic fleet seat rev-security-03) sanity scooby (Scooby) scrappy shaggy tiny velma veronica (Mosaic fleet seat veronica) vision woodpecker
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: mosaicstack/stack#1402