Skip to content

Worker pool leaks a connection holder per Postgres idle-in-transaction kill (asyncpg 0.31 zombie holder); 8-slot pool, kills run 2–10/day #2540

Closed

Description

@eumemic

Observation (prod worker 612d6a30, server-b, 2026-10-09)

  • worker.held_connection_watchdog_alarm fires every 5 min on ONE holder. held_seconds goes 60 → 361 → … → 2299 (04:55Z) and keeps growing linearly. Every specimen shows task: None, owner_id: None, pool.idle_size == pool.size, holders 8, in_use 1. pg_stat_activity has no worker backend for it.
  • Timeline, to the second:
    • Postgres 04:17:17.126 FATAL: terminating connection due to idle-in-transaction timeout (idle_in_transaction_session_timeout=60000, db/pool.py:116).
    • 04:17:24: job harness.wake_workflow[5080124] (run wfr_01M4FDHC8GEYX1P53YE3GV0WM7, post-merge-checker-sweep) ends Error, lasted 68.335 s with InternalClientError: cannot switch to state 15; another operation (2) is in progress at workflows/step.py:751 → append_run_event (db/queries/workflows.py:1747).
    • The watchdog's first_seen for the stuck holder is ≈04:17:24.
  • asyncpg is pinned at 0.31.0 (uv.lock). PyPI latest is 0.32.0.
  • Idle-in-transaction kills in the Postgres log over the past 7 days: 3, 2, 4, 10, 5, 9, 2, 2 per day.

Inference (source analysis by an uncorrelated reviewer; NOT reproduced against a live DB)

  • idle_size == size with in_use 1 means the holder's connection is closed but still marked in use. A live borrower would show idle == size-1. This matches asyncpg bug: Possible connection pool deadlock MagicStack/asyncpg#1307 ("possible connection pool deadlock"), fixed in 0.32.0 (Surface per-run cost/token/iteration on get_run/list_runs (run_children_usage is consumed only by budget() today) — the machine-observer's substrate #1324): after a client-side protocol error → abort(), the closing branch skips con._cleanup(), so release() returns early on is_closed() and the holder is never re-queued. Only a worker restart clears it.
  • If every idle-in-transaction kill that lands on an in-use pooled connection leaks one holder, the 8-slot pool (db_pool_max_size default 8) starves after about 8 such events between worker restarts. At 2–10 per day that is days, not weeks. The 2026-10-02 episode (held-connection alarm plus throughput dead-man, older image, same asyncpg) fits this.
  • Not every kill necessarily leaks. That depends on whether the next operation on that connection hits the abort path. Unverified.

Also implicated

  1. Something holds a transaction idle for over 60s inside wake_workflow (the job lasted 68s, then the kill). That is the 07-17 class (a pooled connection held across a non-DB await). The Invariant lint: a pooled DB connection may never be held across a non-DB await (split from #1976, buildable now) #1978 lint can't see it if the await is nested, or if it is sync work.
  2. The watchdog's task: None is not evidence of a leak. _task_for_connection uses task.get_stack(), which returns only the outermost frame of a suspended task, so every NESTED borrower also maps to None. Walk get_coro().cr_await instead.

Fix (in order)

  1. Bump asyncpg to >=0.32.0 (uv.lock). Red test: simulate a server-side FATAL mid-transaction on a pooled connection and assert the holder returns to the pool (idle_size recovers). It must be red on 0.31.0.
  2. Find and fix the idle-in-transaction holder in the wake_workflow step path, starting from step.py around the append_run_event at L751 and whatever the enclosing transaction awaits between statements.
  3. Fix the _task_for_connection frame walk, with a nested-borrower unit test.

Until (1) ships, a worker restart clears the leaked holders, and the promote cadence has been resetting them.

Activity

  1. added
    bugSomething isn't working
    shovel-readyDesign settled, scope clear; ready to implement without further design discussion
    approvedGreenlit to build. Dispatch gate = shovel-ready + approved.
    dispatchedClaimed: an agent/pipeline is actively working this — don't double-grab
    pipeline:v2Owned by the dev-pipeline RECONCILER (not the v1 monolith). Reconciler ONLY touches v2 items.
    and removed
    pipeline:v2Owned by the dev-pipeline RECONCILER (not the v1 monolith). Reconciler ONLY touches v2 items.
    dispatchedClaimed: an agent/pipeline is actively working this — don't double-grab
    on Oct 9, 2026
  2. eumemic commented on Oct 9, 2026

    @eumemic
    OwnerAuthor

    Build parked for a human (needs:human/build) [02d1fcc8]

    The dev-pipeline reconciler parked this issue needs:human/build — the build path could not proceed. A human gate needs the reason ON the issue (not only in the workflow run output), so here it is:

    the agent did not respond within its wall-clock deadline

    (This is a hard human gate: the reconciler will not re-pick a needs:human/* item. Address the reason above, then re-dispatch — a fresh refusal re-comments; the same refusal on a crash-replay does not double-post.)

  3. eumemic commented on Oct 9, 2026

    @eumemic
    OwnerAuthor
    ## Part 1 merged: asyncpg 0.32.0 ([#2542](https://github.com//pull/2542) → `11a9b862`). Not deployed yet.
    • Uncorrelated review: MERGE-SAFE at 9772d39c. The reviewer reproduced the result independently:
      • the new test fails 3 of 3 on 0.31.0 with holder leaked: in_use=1 size=1 idle=1, the prod fingerprint;
      • it passes 3 of 3 on 0.32.0;
      • the inner error is the exact prod text, cannot switch to state 15;
      • mutations (relay delay 0, relay removed) make 0.31 pass, so the delayed FIN is what reproduces the race;
      • with the in_use assertion disabled, the test is still red on 0.31 through the bounded acquire timeout.
    • Image build: the Dockerfile only runs uv sync --frozen, and the reviewer reproduced it with a real uv 0.5.18. The image will contain exactly asyncpg==0.32.0.
    • production_watchdogs.py reads asyncpg internals (_holders, _in_use, _con). All of them still exist in 0.32.0, and a probe against a real pool still names the owner.
    • The 0.32 SSL change doesn't apply. The prod DSN is plain postgresql:// with no sslmode.
    • CI is 10/10 green at the head, and the integration job installed 0.32.0 and ran the new test.

    Remaining work:

    • Part 2: find the transaction in the wake_workflow step path that sits idle for more than 60s. That idle transaction is what made Postgres kill the connection at 04:17:17. The library fix stops the leak; it does not stop the kills.
    • Part 3: make the _task_for_connection frame walk follow cr_await, so nested borrowers stop showing as task: None.
    • Follow-up from review: assert that the race actually happened. ei.value.__context__ should be an InternalClientError whose message includes 'cannot switch to state'. Otherwise a relay that stops reproducing the race turns the test into an always-pass.
    • Prod keeps the one leaked holder until the next promote, which also ships 0.32.0.
  4. eumemic commented on Oct 9, 2026

    @eumemic
    OwnerAuthor

    Closing — work already merged

    The dev-pipeline reconciler is closing this issue: its work is already merged via PR #2542. A green merge left the source issue open (no Closes #2540 linkage in the PR body), so the reconciler reaps it — closing as completed and stripping the stale in-flight / needs:human/build labels. (If this is wrong — the merged PR did NOT actually resolve this issue — reopen it and remove the merged PR's branch-token / cross-reference.)

  5. eumemic commented on Oct 9, 2026

    @eumemic
    OwnerAuthor
    **Second occurrence in prod (612d6a3, still on asyncpg 0.31). The pool now has 2 leaked holders.**
    • 07:38:02.669Z: PG FATAL: terminating connection due to idle-in-transaction timeout.
    • Job harness.wake_workflow[5087096], run wfr_01M4FSG2BSSWG1H663ZNZBWVXA (gate-reaper-sweep-30m, wf_01KVEK7G…), ended Error, lasted 65.905 s with InternalClientError: cannot switch to state 12 inside append_run_event (db/queries/workflows.py:1748).
    • Held-connection specimens since then show in_use 2 with idle_size == size, which is two zombies (13181s and the new one).

    For part 2, the evidence now names a shape. Both kills came from a different cron sweep workflow (post-merge-checker-sweep at 04:17, gate-reaper-sweep-30m at 07:38). In both, the wake_workflow job ran about 66s, and both failed on the first DB write after the idle gap, append_run_event. So _run_workflow_step_body holds a transaction open across a non-DB await that can take more than 60s. A tool or http call on the inline path is likely; the step that runs the script's next segment inside the step's conn.transaction() is my unverified guess. The fix belongs at the step's transaction boundary, not in any single workflow.

    The seat's restart threshold is 3 leaked holders. The fix (asyncpg 0.32, merged as 11a9b862) ships at the next promote.

  6. eumemic commented on Oct 9, 2026

    @eumemic
    OwnerAuthor
    **Third idle-in-transaction kill (11:44:01Z), this time WITHOUT a leak.** That is a discriminating data point for part 2.
    • stall-detector-sweep (wf_01KVFG6V…), job wake_workflow[5095377], run wfr_01M4G7JGDGJH938JJEAJSGCNV3, Error, lasted 67.039 s. That makes three kills from three different cron sweep workflows (post-merge-checker, gate-reaper, stall-detector), each with a step of about 66 s.
    • This time the client error was ConnectionDoesNotExistError: connection was closed in the middle of operation, not cannot switch to state N. The pool's zombie count stayed at 2: the specimens still show only the 04:17 and 07:38 lineages, and idle_size has not collapsed. This matches the builder's framing check exactly. The 0.31 leak needs the next statement to be sent BEFORE the client sees the FIN. When the FIN arrives first, cleanup runs and the holder is returned.
    • Leak rate so far: 2 of 3 kills leaked.

    For part 2: three sweep workflows, same ~66 s step. That points away from any single workflow and toward something every cron-fired sweep step does while holding the step transaction for about 60 s or more. My guess, not verified: the first tool()/http_request call that the sweep makes inline.

  7. eumemic commented on Oct 9, 2026

    @eumemic
    OwnerAuthor
    **Third leak (14:21Z) wedged a trigger. Worker restarted at 14:39Z.**
    • 14:21:01 PG idle-in-transaction FATAL. This time the victim was harness.run_trigger[5100599] for bruce-email-reply-watch: it lasted 92.3 s and died at append_event (db/queries/events.py:1681) with cannot switch to state 11. That wedged the trigger the aios#2417 way: next_fire stuck at 14:25, status ok. So the leak class is not only workflow wakes: a trigger fire hit it too. I recreated the trigger at 14:43Z.
    • At 14:33 the held-connection specimens showed a third zombie lineage (60 → 361 → 679 s, idle 0–2 of size 5, in_use 6). That met the seat's threshold of 3, so I restarted the worker at 14:39:10Z (docker restart, same image 612d6a30, no rebuild, no code change). Forensics are saved on server-b: ~ceo-front/forensics/worker-pre-restart-20261009T1438Z.log and the specimens tarball.
    • Post-restart: healthy, jobs flowing (42 ok in the first 2 min). The restart re-queued the post-merge-checker-sweep wake immediately into another idle-in-transaction kill: 14:40:33, 2 FATALs, wake_workflow[5100979] errored at 66.4 s, run wfr_01M4GHJYAYAGH1YR28W4CY7W9P. Part 2 is reproducible on demand: that workflow's step reliably holds a transaction idle for over 60 s. It is the best reproduction target for the part-2 fix.

    Still open: promotion of 11a9b862 (asyncpg 0.32) is the chairman's call; asked at 14:00Z. Until then, expect a zombie roughly every 2–4 h and a restart roughly every 8 h.

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    approvedGreenlit to build. Dispatch gate = shovel-ready + approved.bugSomething isn't workingpriority:highshovel-readyDesign settled, scope clear; ready to implement without further design discussion

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions