## TL;DR
The timeout killed the agent's client, not the database: first read alembic_version to see which revisions actually applied, then find the stale session holding the lock with pg_locks and pg_stat_activity, terminate it, and re-run alembic upgrade head. Alembic skips already-applied revisions, so the resume is safe. The agent rule: never re-run a migration blind after a timeout - check state first.

## The query

```text
migration agent ran alembic upgrade head and it timed out at 10 minutes - the migration was blocked on a table lock from a stale postgres connection
```

## Use this when

- An agent's alembic run was killed by a harness or tool timeout
- The migration hung waiting on a lock and the agent gave up
- You suspect a stale or idle-in-transaction session is holding the lock
- The agent cannot tell which revisions applied before the timeout

## Not for

- Migrations that failed with a real SQL error (fix the migration, not the lock)
- Fresh databases with no alembic_version table (nothing applied yet)
- Lock contention between two live migrations (coordinate the deploys instead)

## Steps

### 1. Read the ground truth: what actually applied

```sql
SELECT version_num FROM alembic_version;
```

Expected output: the current revision. Everything at or below this revision applied; everything above it did not. This is the state the timed-out run left behind.

### 2. Find the session holding the lock

```sql
SELECT pid, usename, state, wait_event_type, query_start, query
FROM pg_stat_activity
WHERE pid IN (
  SELECT pid FROM pg_locks WHERE NOT granted
  UNION
  SELECT pid FROM pg_locks WHERE granted AND locktype = 'relation'
)
ORDER BY query_start;
```

Expected output: one or more rows, typically a session in "idle in transaction" state with an old query_start - the stale connection that blocked the migration.

### 3. Terminate the blocking session

```sql
SELECT pg_terminate_backend([blocking pid]);
```

Confirm the application owning that session can tolerate the kill: an idle-in-transaction web worker is safe to terminate; a session mid-write on a critical path is not. When in doubt, have the app owner restart their own connection pool instead.

Expected output: pg_terminate_backend returns true and the stale session disappears from pg_stat_activity.

### 4. Re-run the migration - it resumes where it stopped

```bash
alembic upgrade head
```

Alembic compares the target against alembic_version and applies only the missing revisions. Re-running is idempotent by design.

Expected output: "Running upgrade ... -> ..." lines only for the revisions that had not applied, ending at head.

### 5. Set lock timeouts so this cannot hang again

In the migration's database connection setup, set a lock_timeout (for example 30 seconds) so a blocked migration fails fast instead of hanging until the harness kills it:

```python
# in env.py, after the connection is created
connection.execute(text("SET lock_timeout = '30s'"))
```

Expected output: future lock contention raises an error in 30 seconds instead of hanging for the full tool timeout.

### 6. Give the agent a state-check before every migration run

Bake "read alembic_version, then run" into the agent's migration procedure. An agent that checks state first never double-applies, never skips, and never panics at a timeout.

Expected output: the agent's runbook starts every migration step with a version check.

## Variant phrasings

### alembic upgrade head timed out waiting for lock

Same playbook: steps 1 through 4. The timeout is a symptom; the stale lock is the cause.

### alembic stuck on table lock from idle connection

Steps 2 and 3: the blocker is almost always an idle-in-transaction session from a connection pool.

### resume alembic migration after timeout without re-running

Step 4: you do not need to avoid re-running. Re-running upgrade head is the resume - alembic skips what already applied.

## Why it happens

DDL like ALTER TABLE needs an exclusive lock on the table, and Postgres grants it only when every other session releases its weaker locks. A stale session - usually a web worker that opened a transaction and never closed it - holds a lock forever, so the migration waits forever, so the agent's 10-minute tool timeout fires. The migration was never slow; it was queued behind a dead connection.

## Edge cases

- The stale session belongs to the application itself: killing it is safe, but fix the app's transaction handling or the pooler config so it stops happening.
- Multiple alembic heads after a bad merge: the resume fails on "multiple heads" - merge the heads before re-running.
- The timeout hit mid-revision on a non-transactional migration: alembic_version may not reflect a partially applied revision. Read the revision script and check the schema by hand before re-running.
- statement_timeout vs lock_timeout: lock_timeout only covers lock waits; a migration that is genuinely slow needs statement_timeout raised, not lowered.
- Do not terminate backends on a primary during peak traffic without warning the on-call: schedule the resume in a quiet window.

## Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ZYcOf-8QqRmTHCjUctJvog