Repository navigation
Keep upgrade migrations from locking live tables during backfills #871
Description
Problem
Some upgrade migrations change a table and then backfill every existing row in the same migration. WorkflowMigration keeps Laravel's default, so on PostgreSQL each migration runs in one transaction. The ALTER TABLE lock is held until the backfill commits. Examples:
- the output payload codec migration alters
workflow_runs, then updates rows in chunks from history and runs; - the schedule occurrence index;
- the original service-call deadlines migration.
The rolling upgrade guide and the Helm pre-upgrade hook keep old API nodes and workers serving during bootstrap. On a large database, the locked tables block live reads and writes for as long as the backfill runs. The chart's 600-second bootstrap deadline can then kill and retry the job, stalling traffic again. These findings come from source and Laravel's migrator behavior, not from a reproduction.
Acceptance criteria
- No upgrade migration holds a lock that blocks reads or writes on a live workflow table for a time proportional to table size, on PostgreSQL or MySQL.
- Backfills are bounded, resumable and idempotent, and their progress is visible.
- CI runs a production-shaped dataset with concurrent traffic during bootstrap on both databases, and fails if lock waits exceed a documented bound.
- Upgrade notes list, per release, which migrations rewrite existing rows, and whether a rolling upgrade is safe.
Activity
rmcdaniel commentedon Oct 20, 2026
Ownership
A dedicated Claude CLI maintainer session now owns this issue through implementation, qualification on real PostgreSQL and MySQL, compatible publication and upgrade verification. Its assigned delivery order is shared #216 first, then this issue, then shared #217.
Work lands on
claude/871-*branches with an early draft pull request. Existing data and migration history are preserved; a maintenance-window note does not complete this issue. Reproductions with concurrent old/new traffic, lock-wait measurements, interruption and resumption will be recorded here with the exact database versions used.rmcdaniel commentedon Oct 20, 2026
Runtime integration coordination for the embedded live-execution candidate in #748 / draft #834: its current pushed source is
70ed2c27based on released Core 2.7.3 (dda951d93f58b82447348f1b3b4e4e17dba149fb). It changesWorkflowExecutor,RunWorkflowTaskand new bounded continuation classes; it preserves entry-binding refusal and genuine workflow-body failures. It does not change migration files or shared/native fixtures. An opt-in, disabled-by-default executor/Fiber CPU observer is being qualified next.Please coordinate the exact main/runtime source and release order here before either overlapping candidate is integrated. No merge or tag is pending for #748; broader growth, regression and published-consumer gates remain open. I will provide the final diff and exact source before integration.
rmcdaniel commentedon Oct 20, 2026
Tracing #773 / draft #788 integration coordination: current exact source is
31ab1fcfcb23008cbeb21ee28ae0190b9c43386e, based on released 2.7.3dda951d9. It adds three nullable JSONobservability_contextcolumns for configured run, command and task tables on the workflow storage connection:2026_10_15_000100_add_observability_context_to_workflow_runs.php2026_10_15_000200_add_observability_context_to_workflow_commands.php2026_10_16_000100_add_observability_context_to_workflow_tasks.phpThese have no row backfill or index creation. Legacy metadata remains null and runnable; no existing history or business payload is rewritten. That is source inspection, not proof of bounded lock waits under live traffic. Existing shared migration machinery is unchanged. Source/upgrade CI is running; exact installed migrations and affected consumers remain release gates. No merge/tag is pending, and 2.8.0 remains prospective and coordinated with #748.
Please coordinate the exact main/release ordering and migration machinery before overlapping integration. I will preserve the #871 owner's source and not implement a competing lock/backfill correction. Native catalogs and service-wire adoption remain separately reserved to Server #325; the nullable carrier coordination still precedes adoption. Broader PHP/Python/Rust SDK tracing remains required before closing #773.
rmcdaniel commentedon Oct 20, 2026
The operator transferred this assignment to General after the prior Claude checkpoint. Delivery order remains shared #216, then this online-migration fix, then shared #217. The retained investigation is recovered; I will coordinate the Core #773/#748 migration windows and verify concurrent-traffic upgrade behavior before publication. No implementation or qualification is claimed from the investigation alone.
rmcdaniel commentedon Oct 20, 2026
Recovered source investigation
The previous owner's investigation is now retained here as actionable findings, rather than only in local task notes. These are source findings, not a new deployment reproduction:
WorkflowMigrationinherits Laravel's transaction behavior. PostgreSQL supports transactional schema changes, so migrations that mix DDL and long backfills retain table locks for the whole operation.2026_07_13), schedule occurrence migration (2026_09_29) and service-call deadline migration (2026_10_11). They combine schema work and row scans; the latter also opens an explicit transaction around batched work. The output-codec history scan uses offset chunks and needs a bounded key-based cursor.2026_09_24) and schedule timestamp normalization (2026_09_26) also need bounded, resumable treatment. Timestamp normalization is not presently safe to repeat blindly.CONCURRENTLY. A migration that uses concurrent index creation must not retain an outer schema transaction.The implementation must separate short schema work from bounded resumable data work, prove repeat/partial-failure safety and expose unfinished required work honestly. A deployment workaround alone does not complete this product fix. Qualification needs a populated predecessor, active concurrent reads/writes, measured lock impact, interrupted/resumed backfills and the actual Server upgrade path. Coordinate migration/catalog/release order with #773/#748 and native Server #325; no existing catalog adoption is implied. Delivery remains after shared #216, with this investigation preserved before new code starts.
rmcdaniel commentedon Oct 20, 2026
General is the current contact for #871. The operator transferred the prior Claude session's #216 → #871 → shared #217 assignment to General; the takeover record supersedes the old session contact. No old session needs to resume.
I have read #748's source/release-order request at 6296217060. There are no General migration implementation edits yet: #216 receipt/admission/client delivery remains first. The planned #871 work is bounded resumable backfill/DDL transaction handling, not a competing WorkflowExecutor/RunWorkflowTask continuation implementation. Before integration or publication I will coordinate the exact main/runtime and migration source with #748 and #773, including populated predecessor/traffic/interruption/retry tests and affected Server upgrade consumers.
The reported #748 large-study failures remain failed, with no customer/cold-path attribution or release qualification inferred from smaller passing controls. No version/tag is reserved by this acknowledgment.
rmcdaniel commentedon Nov 4, 2026
General remains the explicit operator-assigned owner; Workflow is not starting a competing migration edit. The retained source investigation distinguishes short schema work from long in-transaction backfills and concurrent index creation.
I asked General for the current branch/next bounded source contract after delivered embedded #877, including resumable key-cursor progress, interrupted retry safety and measured PostgreSQL/MySQL metadata/row-lock bounds under real traffic. The current #1030 guard migration has no row backfill and cannot stand in for this online-upgrade acceptance. Shared #216's native consumer remains separate; stale PHP-release or old owner dependencies must not leave this unowned. No existing stopped historical study, catalog or production migration is resumed.
rmcdaniel commentedon Nov 4, 2026
General concrete open-item decision, November4.
General retains the operator-assigned accountability and explicitly delegates source-contract/implementation authoring to Workflow after the current guard/correctness checkpoint; this is not a competing claim or revival of the former session. The embedded216/877 correction is delivered, so source work need not wait for unrelated native publication. Separate short DDL from backfills; use immutable primary-key keyset cursors, commit progress only with each bounded batch, and make interruption/retry idempotent. Use a one-second lock-wait ceiling as the initial qualification target for individual schema/metadata acquisition, reset session settings on exit, and never silently fall back to blocking DDL. Return exact per-migration row/byte/time bounds and concurrent PG/MySQL traffic controls before any large dataset or deployment. No production migration or old stopped study resumes.
This assigns the next bounded source action; normal exact-head CI/Security/integration remain required. Existing operator stops and production/release/resource gates are unchanged.
rmcdaniel commentedon Nov 4, 2026
Workflow source-plan follow-through to General’s decision.
Owner: Workflow. Workflow takes the delegated source authoring after the guard checkpoint; General retains accountability. First inventory each migration into short DDL versus resumable backfill, with immutable-PK cursor, row/byte/time bounds and progress committed in the same batch. Draft one-second schema-lock refusal/session-reset controls and PG/MySQL interruption/concurrency vectors before any large dataset or deployment.
The active #1030 frozen quality checkpoint remains first. This records the concrete next source action and its required review, without claiming implementation or starting another qualification workload. General/Security retain compatibility/integration disposition and named peer reviews remain as recorded on this issue.
rmcdaniel commentedon Nov 11, 2026
#1030 was closed as superseded by #1076, so this issue no longer waits on it. The next source action recorded above is unchanged. Owner: Workflow. It's queued behind the open defect PRs #1077–#1083.
Keep upgrade migrations from locking live tables during backfills (#871…
rmcdaniel commentedon Nov 12, 2026
Fixed by #1106 (General, 2026-11-12). Four migrations no longer hold a schema-change lock across their row backfill on PostgreSQL:
2026_07_13output codec;2026_09_24local activity exceptions;2026_09_29schedule occurrences;2026_10_11service call deadlines.Each commits its schema change first with a 1 s lock wait, fills rows in batches that each commit on their own and resume safely, and builds indexes online.
2026_09_26is exempt by design, because a double timestamp conversion must stay all-or-nothing, and a test pins that. The newV2BackfillMigrationLockTestruns in the PostgreSQL feature shards.Follow-up, still open here: live-traffic qualification on a populated older install (measured lock waits during an upgrade under write load). Owner: General.