TL;DR: Kill the orphaned database session, then let alembic tell you the truth. Reconnect, find the stuck session in pg_stat_activity (state 'idle in transaction'), terminate it with pg_terminate_backend, 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:
```text
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