Skip to content

fix(coderd/database/migrations): remove lock-retry blocks from chat migrations - #31167

Draft
mafredri wants to merge 5 commits into
mainfrom
mafredri/chat-migrations-drop-lock-retry
Draft

mafredri wants to merge 5 commits into
mainfrom
mafredri/chat-migrations-drop-lock-retry

Conversation

@mafredri

@mafredri mafredri commented Oct 22, 2026 •

Copy link
Copy Markdown
Member

Migrations 000609, 000611 and 000615 start with a lock-retry block (added in #30297 and #30412) that is meant to stop live chat traffic from deadlocking with the migration. On a release upgrade it cannot do that, and it can turn a failure into a 2-minute hang or cause a failure that would otherwise not happen. This PR removes the blocks. A release upgrade that runs 000607 to 000615 while chats are in use is unreliable with or without them, so it needs chat traffic stopped, for example by scaling the deployment to zero first.

coder server runs all pending migrations in one transaction, so every migration's locks are held until the transaction commits. On a release upgrade (from v2.38 or earlier), 000607 and 000608 lock chats before any block runs. A chat request that writes chat_messages or chat_queued_messages, or that reads users, holds a lock that a later migration needs and then waits for chats. The block can release only the locks it took, so it can avoid this deadlock only when its migration takes the first chat lock of the upgrade. Postgres checks a waiting request for a deadlock once, after deadlock_timeout (1 second by default). The block pauses 100ms between 100ms lock attempts, so a check that lands in a pause finds no deadlock, the request keeps its lock, and the block retries until it fails the upgrade after 2 minutes, with the request stuck until then.

Without the block, the outcome depends on timing. If the request's check fires after the migration starts waiting for it, Postgres cancels the request and the upgrade continues. If the request had already waited longer than deadlock_timeout, its check found no deadlock, so Postgres cancels the migration instead and the upgrade fails. Main does not finish those cases either: its block was still retrying after a minute, and it gives up at its 2-minute deadline.

000609 and 000611 return to their content before #30297, and 000615 loses the block in both directions. A block's migration takes the first chat lock of an upgrade only when upgrading from a build of main at 608 through 614: dogfood, where the #30297 deploy failed, or a coder-preview image built between 2026-10-02 and 2026-10-11. No release stops in that range, and dogfood is already past 615.

Editing these merged migrations leaves every schema unchanged: no release contains them (every v2.38 release, through v2.38.3, ends at 605), databases that already ran them do not run them again, and dump.sql regenerates without a diff.

TestMigrationChatTablesConcurrentTraffic only covered upgrades where these migrations take the first chat lock, so it is removed. TestMigrationUpgradeFromReleaseDuringChatTraffic runs the v2.38 upgrade through 000609 with chat message inserts waiting and through 000615 with users-then-chats transactions waiting, each started shortly before the migration needs its lock. It passes on this branch; with main's migrations, the migration is still retrying at the 25-second timeout. Requests that waited longer are not covered.

Measurements

Each run starts chat requests while the upgrade transaction is open, on the Postgres 13 test container with the default deadlock_timeout. "Request canceled" means Postgres canceled the chat request as the deadlock victim. "Stuck" means the migration was still retrying when the run was stopped, after 10 seconds unless noted.

Upgrade from Migration Chat requests main This PR
v2.38 (605) 000609 queued message insert upgrade failed after 2m0s request canceled after 1s, upgrade ok
v2.38 (605) 000609 chat message insert stuck request canceled after 1s, upgrade ok
v2.38 (605) 000609 chat message insert that waited 1.2s before 000609 started stuck (stopped after 1 minute) migration canceled after 1s
v2.38 (605) 000609 chat message insert and queued message insert stuck (stopped after 1 minute) migration canceled after 2s
v2.38 (605) 000615 read users, then chats stuck request canceled after 1s, upgrade ok
v2.38 (605) 000615 read users, then chats, plus a chat message insert stuck (stopped after 1 minute) migration canceled after 2s
607 (main build) 000609 queued or chat message insert stuck request canceled after 1s, upgrade ok
610 (main build) 000615 queued message insert stuck request canceled after 1s, upgrade ok
608, 610, 614 (main build) 000609, 000611, 000615 as the first chat lock the four cases in TestMigrationChatTablesConcurrentTraffic no deadlock 2, 1 and 1 of 4 deadlock; in the 000615 case Postgres canceled the migration, in the others a chat request

🤖 This PR was created with the help of Coder Agents, and will be reviewed by a human. 🏂🏻

@mafredri

This comment has been minimized.

@coder-agents-review

coder-agents-review Bot commented Oct 22, 2026 •

Copy link
Copy Markdown
Contributor

Chat: Review posted | View chat
Requested: 2026-10-28 12:26 UTC by @mafredri

Review history
  • R1 (2026-10-22): 12 reviewers, 4 Nit, 2 P1, 2 P3, 3 P4, REQUEST_CHANGES. Review
  • R2 (2026-10-28): 13 reviewers, 10 Nit, 2 P1, 2 P3, 5 P4, COMMENT. Review
  • R3 (2026-10-28): 5 reviewers, 10 Nit, 2 P1, 2 P3, 5 P4, APPROVE. Review

deep-review v0.13.0 | Round 3 | 161b723..a165b0b

Last posted: Round 3, 19 findings (2 P1, 2 P3, 5 P4, 10 Nit), APPROVE. Review

Finding inventory

Finding inventory: PR #31167

Findings

# Sev Status Location Summary Round Reviewer Posted
CRF-1 P3 Author fixed (fc087f8) 000615_remove_chat_automations.up.sql:4 No test guards 000615 against chat traffic on a release upgrade R1 Netero Yes
CRF-2 Nit Author fixed (fc087f8) migration000609_test.go:101 readUpMigration has no doc comment R1 Netero Yes
CRF-3 P1 Author contested; panel closed R2 (5/5 accept) 000609_chat_automations.up.sql:125 v2.38 upgrade still cancelled at 000609 when a waiter's single deadlock check ran before 000609 requests its table R1 Takumi P1, Knuckle P2, Mafuuu P2, Chopper P2, Bisky P3 Yes
CRF-4 P1 Author contested; panel closed R2 (5/5 accept) 000615_remove_chat_automations.up.sql:32 v2.38 upgrade still cancelled at 000615 when a users-then-chats reader waited more than 1s before 000615 R1 Takumi P1, Knuckle P1, Hisoka P2, Pariston P2, Chopper P2 Yes
CRF-5 P3 Author fixed (fc087f8, 253e751) migration000609_test.go:66 Test relies on unenforced 500ms ordering; slow runner fails with a migration error R1 Komugi P3, Bisky P3, Knuckle P3, Leorio P3, Chopper P3, Gon P2, Hisoka P4, Takumi Note, Ryosuke Note, Pariston Note Yes
CRF-6 P4 Author fixed (fc087f8) migration000609_test.go:93 Insert loop accepts nil, so test passes if 000609 stops needing the inserts' lock R1 Bisky Yes
CRF-7 Nit Author fixed (fc087f8) migration000609_test.go:95 Use errors.AsType instead of var plus errors.As R1 Ging-Go Yes
CRF-8 Nit Author fixed (fc087f8) migration000609_test.go:19 Test doc comment repeats itself (Gon P2, downgraded) R1 Gon Yes
CRF-9 P4 Author fixed (fc087f8) migration000609_test.go:26 Doc comment says upgrade fails; test actually reports context expired R1 Leorio Yes
CRF-10 P4 Author fixed (fc087f8) migration000609_test.go:72 500+50*i and range 4 are unnamed values the comment depends on R1 Gon Yes
CRF-11 Nit Author fixed (fc087f8) migration000609_test.go:69 inserted names results expected to be cancelled R1 Gon Yes
CRF-12 Nit Dropped by orchestrator (current title accurately names the change; suggested title overclaims given CRF-3 and CRF-4) PR title Title names mechanism, not failure R1 Leorio No
CRF-13 Note Dropped by orchestrator (verification only, no action) 000609_chat_automations.up.sql:1 000609/000611 byte-identical to pre-#30297; 000615 comment now true R1 Knuckle, Hisoka, Leorio, Gon No
CRF-14 Note Dropped by orchestrator (duplicates the InTx out-of-scope item; PR description states the tradeoff) 000609_chat_automations.up.sql:1 Deadlock-victim chat writes return 40P01 without retry R1 Ryosuke No
CRF-15 OOS Out of scope (body) txnmigrator.go:41 All pending migrations run in one transaction, holding early locks across later waits R1 Ryosuke, Takumi, Pariston No
CRF-16 OOS Out of scope (body) tx.go:41 InTx retries 40001 but not 40P01 R1 Ryosuke No
CRF-17 OOS Out of scope (body) coderd/database (12 sites) Other errors.As(&pqErr) sites use the pre-1.26 form R1 Ging-Go No
CRF-18 Nit Author fixed (a165b0b) migration_chat_traffic_test.go:20 Test doc comment repeats the mechanism stated at lines 40 and 76-84 R2 Gon P2 Yes
CRF-19 Nit Author fixed (a165b0b) migration_chat_traffic_test.go:76 Const comment restates derived values (50ms, 150ms) instead of the constraint R2 Gon P2 Yes
CRF-20 Nit Author fixed (a165b0b) migration_chat_traffic_test.go:93 605 and 606 are one unnamed release baseline written twice R2 Gon P4 Yes
CRF-21 Nit Author fixed (a165b0b) migration_chat_traffic_test.go:151 migrated holds an error; sibling of CRF-11 R2 Gon Yes
CRF-22 Nit Author fixed (a165b0b) migration_chat_traffic_test.go:86 requests is a count but reads as a collection R2 Gon Yes
CRF-23 Nit Author fixed (a165b0b) migration_chat_traffic_test.go:161 Raw SQLSTATE 40P01 where the package compares Code.Name() R2 Gon Yes
CRF-24 P4 Author fixed (a165b0b) migration_chat_traffic_test.go:32 "with or without lock retries" implies Postgres cancels a retrying migration R2 Leorio Yes
CRF-25 P4 Author fixed (a165b0b) migration_chat_traffic_test.go:83 "with the same result" stated unconditionally; holds only for requests waiting under deadlock_timeout R2 Leorio Yes
CRF-26 P4 Author fixed (PR description edited R3) PR description Deadlock mechanism sentence lists the chats read twice with two "then"s R2 Leorio No
CRF-27 Note Note (body) migration_chat_traffic_test.go:157 Test already pins victim choice via timeouts, so the stated reason for declining a migration-cancelled case does not hold R2 Ryosuke No
CRF-28 Note Dropped by orchestrator (no action; the test is this PR's regression guard) migration_chat_traffic_test.go:34 Test guards frozen migrations at ~7s cost R2 Ryosuke No
CRF-29 Note Note (body) 000615_remove_chat_automations.up.sql:4 Preview images 2026-10-02 to 2026-10-11 sit at 608-614; description omits them R2 Knov No
CRF-30 Note Dropped by orchestrator (verification only) migration_chat_traffic_test.go:150 Fix verifications: timing margin 2s vs <60ms gap, comments audited, test fails on main R2 Komugi, Knov, Leorio, Gon No
CRF-31 OOS Dropped by orchestrator R3 (docs/install/operate/upgrade/best-practices.md:33 already recommends scaling to zero before upgrades so migrations get their locks) docs/install/upgrade.md No operator-facing text says a v2.38 upgrade needs chat traffic stopped R2 Takumi, Chopper, Hisoka No
CRF-32 OOS Out of scope (body) 000616_remove_chat_automation_permissions.up.sql:359 000616-000629 take locks chat requests may hold; stop-traffic range may extend past 615 (unverified) R2 Knov No
CRF-33 OOS Out of scope (body) migration_chat_traffic_test.go:177 Moved async doc comment restates its body R2 Gon No

Contested and acknowledged

CRF-3 (P1, 000609_chat_automations.up.sql:125) - v2.38 upgrade cancelled at 000609 when a waiter's single deadlock check already ran

  • Finding: A chat insert that started waiting on chats (held by 000607) more than deadlock_timeout before 000609 requests its table has its only deadlock check before 000609 waits, so Postgres cancels 000609 and every restart repeats this under traffic. Proposed: change the migrations so the ordering cannot happen, or state the limit in the PR description and test comment, extend scale-to-zero advice to v2.38, and add a case asserting the accepted outcome.
  • Author defense: Reproduced; main fails the same cases after a 2-minute retry. No lock order inside 000609 helps because 000607 and 000608 already hold chats. Up-front locks in 000607 are the same kind of per-migration workaround and would not cover a transaction that held a lock before the upgrade. PR description now opens with the limit and the stop-traffic requirement; test doc comment states the uncovered case. Declined a test asserting the migration is cancelled and a docs change.
  • Panel closure (R2, 5/5): Takumi, Knuckle, Mafuuu, Chopper, Bisky, each the R1 agent that raised it. All verified the migration SQL is unchanged since R1 and that the description and migration_chat_traffic_test.go:30-33 state the limit. Knuckle added that chore!: remove the chat automations experiment #30412 (f923f4a) already requires stopping old replicas before the new version migrates, and coder server migrates before serving. Bisky accepted the missing migration-cancelled case because it would test Postgres victim selection, not repository code. Ryosuke (not a raiser) noted the test already pins victim choice via timeouts, so the author's stated reason for declining that case is inaccurate; recorded as a body Note. Release-note wording left to a human (Mafuuu).

CRF-4 (P1, 000615_remove_chat_automations.up.sql:32) - v2.38 upgrade cancelled at 000615 when a users-then-chats reader waited past deadlock_timeout

  • Finding: The window from 000607's chats lock to 000615's ACCESS EXCLUSIVE on users includes chat_messages index builds and ~1s per deadlock 000609 resolves, so a users-then-chats reader's single check runs first and Postgres cancels 000615. Proposed: take users, organizations, chat_model_configs locks before 000607, or state the limit and the stop-traffic requirement.
  • Author defense: Reproduced; main fails the same case after 2 minutes. Taking ACCESS EXCLUSIVE on users from the start of the upgrade blocks every request that reads users until commit, an outage, and is another per-migration workaround. Limit stated in the PR description and test doc comment.
  • Panel closure (R2, 5/5): Takumi, Knuckle, Hisoka, Pariston, Chopper, each the R1 agent that raised it. All agreed the users lock alternative blocks logins and API key checks for the whole upgrade, which costs more than the stop-traffic requirement, and that the PR now states the limit. Operator-facing upgrade guidance recorded as out of scope (CRF-31).

Round log

Round 1

Netero: 1 P3, 1 Nit. No P0, panel proceeds.
Round type: round 1, full panel. Multi-domain (SQL migrations and Go test), target 9-12. Specialists: Knuckle (migrations), Ging-Go (.go), Ryosuke (Go code), Takumi (goroutines, locks, context). Core: Pariston, Mafuuu. Pool: Bisky (tests changed), Komugi (timing-based test), Gon, Leorio (floor), Hisoka (concurrency edge cases). Wildcard: Chopper (random from distilled set).
Cross-check: CRF-3 and CRF-4 share one root cause (Postgres checks each waiter for deadlock once, at deadlock_timeout; any lock the upgrade requests more than 1s after a waiter started makes the migration the victim). Set at P1 per Takumi's and Knuckle's reproductions; main also fails these cases, after 2 minutes, so not a regression, but the PR's stated goal is unmet. CRF-5 settled at P3 over Gon's P2: Komugi measured the setup window at 17 to 150ms against a 500ms budget. Keep-argument for P2: the failure message blames the migration. CRF-8 keep-argument for P2: repository requires concise comments; dropped to Nit because it misstates nothing. CRF-12 keep-argument: subject should name the failure; dropped because the suggested title claims upgrades are unblocked, which CRF-3 and CRF-4 contradict. Totals posted: 2 P1, 2 P3, 3 P4, 4 Nit. Reviewed against b1f5f0b..cd6b154.

Round 2

Churn guard PROCEED: 9 author fixed, CRF-3 and CRF-4 contested. Netero: no findings; CRF-1 and CRF-2 fixes verified.
Round type: restructure round (migration000609_test.go deleted, migration_chat_traffic_test.go added), full panel. Specialists: Knuckle, Ging-Go, Ryosuke, Takumi. Core: Pariston, Mafuuu. Pool: Bisky, Komugi, Gon, Leorio, Hisoka. Dispute check-in (not counted toward size): Chopper. Wildcard: Knov (random from distilled set excluding selected). Dispute personas reused their R1 agents: Takumi, Knuckle, Mafuuu, Chopper, Bisky, Hisoka, Pariston. Others spawned new.
Panel: 9 author fixes verified (CRF-1, 2 by Netero; CRF-5, 6, 9, 10, 11 by Takumi, Bisky, Mafuuu, Hisoka, Chopper, Leorio; CRF-7, 8 by churn guard evidence, no reviewer contradicted). CRF-3 and CRF-4 closed 5/5. Test verified non-tautological by Netero, Bisky, Takumi, Pariston, Knov (both subtests time out with base SQL). New: Gon 2 P2, 1 P4, 3 Nit; Leorio 3 P4; Ryosuke, Knov, Komugi Notes. Cross-check: Gon's two P2 comment findings downgraded to Nit. Keep-argument: repeated mechanism text must be edited in three places when it changes. Downgrade reason: nothing in either comment is false, consistent with CRF-8 in R1. Gon's 605/606 P4 downgraded to Nit. Keep-argument: two linked literals. Downgrade reason: the test pins the fixed v2.38 baseline, named in the doc comment, which does not move. CRF-26 is on the PR description, which has no diff line, so it goes in the body. Totals posted: 3 P4 (1 in body), 6 Nit. Event COMMENT with dismiss of R1 REQUEST_CHANGES (no P0-P1 open). Reviewed against 161b723..253e751.

Round 3

Churn guard PROCEED: CRF-18 to CRF-26 all author fixed (a165b0b and PR description). Author replied that the R2 out-of-scope items will not be ticketed from this PR; they stay Out of scope (body) and need a human decision. Author agreed the stated reason in CRF-27 was wrong and gave a new one (the case adds no coverage: reintroducing a retry block already fails the existing test). Author added the preview image range to the description (CRF-29).
Round type: fix round (one file, comment edits and renames since R2; no added/renamed files, no signature change, effective additions +3). Panel: Gon, Leorio (raised CRF-18 to CRF-25; comments touched, floor), Ging-Go and Ryosuke (.go file changed). Wildcard: Chopper (random from distilled set). Netero: no findings; CRF-18 to CRF-25 fixes verified.
Panel: no findings from Gon, Leorio, Ging-Go, Ryosuke, Chopper. All CRF-18 to CRF-26 fixes verified by at least two reviewers. Remaining open: none. Out of scope still unticketed: CRF-15, CRF-16, CRF-17, CRF-32, CRF-33 (author declined to ticket; human decision). Event APPROVE (auto-downgraded to COMMENT, dismiss attempted). Reviewed against 161b723..a165b0b.

About deep-review

CRF = Coder Review Finding (P0-P4, Nit, Note)

Reviewer Focus
Bisky tests
Chopper ops/errors
Churn-guard change verification
Ging language modernization
Gon naming
Hisoka edge cases
Killua perf
Kite change integrity
Knov contracts
Knuckle SQL
Komugi flake/determinism
Kurapika security
Law decomposition
Leorio docs
Luffy product
Mafu-san process
Mafuuu contracts
Melody dispatch/pairing
Meruem structural
Nami frontend
Netero mechanical checks
Pariston premise testing
Pen-botter product gaps
Razor verification
Robin duplication
Ryosuke Go arch
Takumi concurrency
Zoro shape

🤖 Managed by Coder Agents.

@chatgpt-codex-connector

chatgpt-codex-connector Bot commented Oct 22, 2026 •

Copy link
Copy Markdown

Codex Review Summary

This comment shows the latest Codex review activity on this pull request.

Review Status Commit Review trigger
📝 Code Review ✅ Completed 2026-10-22T14:27:43.590780Z cd6b154 Manual request
ℹ️ About Codex in GitHub

Your team has set up Codex to review pull requests in this repo. Reviews are triggered when you

  • Open a pull request for review
  • Mark a draft as ready
  • Comment "@codex review" or "@codex security review".

Codex reacts with 👀 while any review is running, comments if it has suggestions, and reacts with 👍 once all reviews finish with no findings.

@chatgpt-codex-connector

This comment has been minimized.

coder-agents-review[bot]

This comment was marked as outdated.

@mafredri

This comment has been minimized.

@linear-code

linear-code Bot commented Oct 26, 2026

Copy link
Copy Markdown

CODAGT-1124

@mafredri
mafredri force-pushed the mafredri/chat-migrations-drop-lock-retry branch from 58f9a29 to 253e751 Compare October 28, 2026 11:52
@mafredri

Copy link
Copy Markdown
Member Author

Answers to the remaining asks in the R1 review, so none is left open. This replaces the last sentence of my CRF-3 reply.

  • Out-of-scope list (all pending migrations in one transaction, InTx not retrying 40P01, var pqErr plus errors.As in unchanged code): not this PR's concern and not ticketed. Each is existing code that this PR does not touch.
  • CRF-3, extend the scale-to-zero advice to v2.38 upgrades: no docs change. On main the same orderings do not finish either: the block was still retrying after a minute and gives up at 2 minutes. This PR does not change what an operator has to do.
  • CRF-3, add a case that asserts the accepted outcome: not added. That outcome is Postgres canceling the migration as the deadlock victim, which these migrations do not control.
  • CRF-4, take the users, organizations and chat_model_configs locks before 000607: not done, for the reason in the CRF-4 reply.

🤖 This review was automatically generated with Coder Agents.

@mafredri

This comment has been minimized.

coder-agents-review[bot]

This comment was marked as outdated.

@mafredri

This comment has been minimized.

@mafredri

This comment has been minimized.

coder-agents-review[bot]

This comment was marked as outdated.

coder server runs all pending migrations in one transaction. On a
release upgrade, 000607 and 000608 lock chats before 000609 runs, so a
chat message insert holds chat_messages while it waits for chats, and
000609 needs chat_messages. Postgres checks a waiting request for a
deadlock once, after deadlock_timeout. The lock-retry block in 000609
pauses between short lock waits, so a check that lands in a pause
finds no deadlock, the insert keeps its lock, and the upgrade fails
after 2 minutes. Without the block, Postgres cancels the insert and
the upgrade continues.

Remove the block from 000609 and 000611, which returns them to their
content before #30297, and from both directions of 000615. No release
contains these migrations.

TestMigrationChatTablesConcurrentTraffic tested what the blocks gave
when their migration takes the first chat lock of an upgrade. Replace
it with a test of the v2.38 upgrade during chat message inserts.
Give each insert its own deadlock_timeout instead of sleeping between
inserts. The checks still land at different points of a lock-retry
cycle, and all of them fire after 000609 starts waiting and well
before its own check, so Postgres cancels inserts, not the migration.
Run the upgrade test for 000609 (chat message inserts) and 000615
(transactions that read users and then chats). Give the requests a 2s
deadlock_timeout and the migration a 10s one, so the requests' checks
always find the migration waiting regardless of runner speed, and
require every request to be cancelled as the deadlock victim.

The doc comment now states that the test covers only requests that start
waiting shortly before the migration needs their lock.
Start all chat requests before waiting for them to block, so their
deadlock checks are spaced by their deadlock_timeout values alone. A
retry loop that pauses 100ms then cannot avoid all of them, instead of
depending on how quickly each request was observed blocking.
The doc comment said Postgres cancels the migration "with or without
lock retries", but a migration that retries 100ms lock waits never
reaches its own deadlock check; it retries until its deadline. Keep the
expected outcome and coverage limit in the doc comment and the timing
mechanism in one place next to the constants, stated as the relation
the constants must satisfy. Name the v2.38 version and the request
count, and compare the SQLSTATE by name as txnmigrator.go does.

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant