fix(coderd/database/migrations): stop upgrades from deadlocking with chat traffic - #30307

Migration upgrades no longer deadlock with chat traffic from replicas that keep serving during a rolling deploy. coder server runs all pending migrations in one transaction, and older chat migrations (000600, 000601, 000607, 000608) take and upgrade locks on chats, chat_messages and chats_expanded in orders that cycle with chatd. #30297 fixed 000609 and 000611 only. Upgrades from 585 (v2.37.3), 600, 605 (release/2.38) and 607 still deadlocked: from 585 PostgreSQL killed the migrator, from the others it killed user requests.

The fix lives in the migrator, so it covers every entry point (coder server with external or built-in Postgres, dbtestutil, gen/dump, scripts/migrate-ci, scripts/migrate-test), and future chat migrations get it for free:

1. Lock first. Before the first pending migration runs, the migration transaction locks chats and every table with a foreign key path to it (chat_messages, chat_queued_messages, chat_automations and the other chat child tables, found from pg_constraint) in ACCESS EXCLUSIVE mode, and chats_expanded with a no-op ALTER VIEW (it locks only the view; LOCK TABLE on the view would also lock users). It uses the 000609 pattern: short waits (lock_timeout = min(100ms, deadlock_timeout/2)), release and retry, 2-minute deadline. Because the migrator gives up before deadlock_timeout, PostgreSQL does not pick a chat request as the victim. Attempts alternate between locking the view before and after the tables: with a fixed order, sustained traffic of the opposite order made every attempt time out (measured locally: never migrated in 20 s; alternating: under 0.3 s for each kind of load).
2. Retry on deadlock. If the run still fails with SQLSTATE 40P01, UpWithFS reruns it from the committed version (up to 5 attempts, 100ms to 800ms backoff, one warning per retry). Other errors are never retried.

Tests

• TestUpWithFSChatTrafficDuringUpgrade upgrades databases at 585, 600, 605, 607 and 608 to latest with the real UpWithFS, under the four traffic patterns from migration000609_test.go (lock chat then update, insert message then update chat, read chats_expanded while the migration waits, lock chat then read chats_expanded). It sequences with pg_blocking_pids, not sleeps, and requires no application 40P01, no migrator retry, and a clean final version. With the lock block disabled, 585, 600, 605 and 607 fail with deadlocks on one side or the other.
• TestUpWithFSChatAutomationTraffic upgrades from 610 while an application transaction updates a chat automation's target_chat_id and a later migration locks chat_automations. Before the lock set included tables that reference chats, the update's foreign key check deadlocked with that migration.
• TestUpWithFSRetriesDeadlocks covers one retry, no retry for other SQLSTATEs, and giving up after 5 attempts.
• TestUpWithFSChatTableLocks covers that nothing-pending runs take no chat lock and that the view lock leaves chats_expanded unchanged.
• Dogfood UAT ran remotely through Coder Agents against the pushed branch (two rounds; the second, on the alternating lock order, passed: upgrades under chat traffic and pgbench load, fresh and already-migrated databases, and the lock deadline).

Closes CODAGT-1298
Part of CODAGT-1135

Generated with xum • Model: anthropic:claude-opus-5-5 • Thinking: high