Fix async database pool idle ping

Task 12842 · Done · Coder · created · updated

On dev (2026-11-02, CP log group `/ecs/jaah-dev-cp`), the async pool (`apps/api/app/db_async.py`, built by C2 of epic 12693) logs `Disconnection detected on checkout, invalidating all pooled connections ... (reason: InvalidatePoolError('pooled connection failed its idle ping: TypeError'` followed by every pooled connection being invalidated. Cause: `db_async._wire` attaches the sync engine's `checkout` listener `app/db.py::_ping_if_idle` to the async engine. That listener runs `with dbapi_connection.cursor() as cursor: cursor.execute("SELECT 1")`. On the async engine, `dbapi_connection` is SQLAlchemy's psycopg async adapter, whose cursor is not a context manager, so the ping raises `TypeError`. The listener turns any failure into `InvalidatePoolError`, which recycles EVERY connection older than now. So each checkout after an idle spell (`PING_IF_IDLE_SECONDS`) throws away the whole async pool and reconnects. That is latency on the request path, and a reconnect burst against RDS. Ask: give the async engine an idle ping that works on the async adapter (or `pool_pre_ping`-equivalent semantics that do not raise `TypeError`). A dead connection must still invalidate; a live idle one must survive. Out of scope: the sync engines' ping (`app/db.py`) as it behaves today; pool sizing; route conversion. Done means: - A test that checks out an async-pool connection after the idle threshold keeps the same connection: no `InvalidatePoolError`, and the pool's connections are not recycled. - A test where the server side of the connection is gone still invalidates that connection. - On dev after deploy, `/ecs/jaah-dev-cp` shows no `failed its idle ping: TypeError` for an hour.

Outcome

Already shipped by Task 12868 (PR #7778). Async idle-ping tests pass, and fail with the old ping restored. Dev /ecs/jaah-dev-cp had 0 'failed its idle ping' events for an hour on the fixed image. No PR.

Artifacts

Activity

  • Created
  • Run
  • Runner started
  • Runner ended (done)