agent's SSH tunnel dropped mid-migration and alembic left an open transaction holding the lock

TL;DR: Kill the orphaned database session, then let alembic tell you the truth. Reconnect, find the stuck session in pgstatactivity (state 'idle in transaction'), terminate it with pgterminatebackend, and run alembic current. In postgres each migration runs inside a transaction, so the killed session rolls back and the revision simply never applied - re-run it.

The problem as reported:

agent's SSH tunnel dropped mid-migration and alembic left an open transaction holding the lock

1. Re-establish connectivity: bring the tunnel back up (prefer autossh for persistence), or better, run the migration from a host close to the database so a laptop network drop can't strand it again.
2. Find the orphan: select pid, usename, state, extract(epoch from (now() - query_start)) as idle_seconds from pg_stat_activity where state = 'idle in transaction' order by query_start;. Expected: one row with a large idle_seconds value - the dead migration session. Confirm the usename matches your migration user before touching it.
3. Terminate it: select pg_terminate_backend(PID); with the pid from step 2. Expected: returns true, and the lock it held is released.
4. Run alembic current. Expected: shows the revision from before the interrupted one, confirming the killed migration rolled back.
5. Run alembic upgrade head. Expected: the migration applies cleanly this time.
6. Prevention: run long migrations inside a tmux or screen session on a stable host, and set a lock timeout on migration sessions so a stuck lock can't block the database forever.

Use this when

• An SSH tunnel or VPN dropped during an alembic run.
• A migration session sits idle in transaction holding a lock.
• alembic current and the lock state disagree with what the agent remembers.

Not for this skill when

• The database isn't postgres - transaction and lock semantics differ elsewhere.
• The lock is held by a migration that's genuinely still running - check query_start and idle time before killing anything.

Variant phrasings

• alembic stuck idle in transaction
• kill orphaned migration session postgres
• ssh tunnel dropped migration lock held
• alembic left open transaction after disconnect

Why it happens

The network drop kills the client, but the server-side postgres session survives - holding its transaction and its locks until something terminates it. The migration isn't "stuck"; its owner is gone.

Edge cases

• Terminating the wrong pid: always verify usename and idle time first; killing a live session rolls back real work.
• The migration opened multiple connections: check for all of them, not just the first.
• A connection pooler in the path: the stuck session may be a pool artifact - check the pooler's own session list too.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_AavCLukCcdNSTWvjtLZuIA