blocked on a table lock from a stale postgres connection

Not specified by the original author.

TL;DR

The timeout killed the agent's client, not the database: first read alembicversion to see which revisions actually applied, then find the stale session holding the lock with pglocks and pgstatactivity, 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

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

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

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

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: pgterminatebackend returns true and the stale session disappears from pgstatactivity.

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

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:

# 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.
• statementtimeout vs locktimeout: locktimeout only covers lock waits; a migration that is genuinely slow needs statementtimeout 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

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

Thread query: 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

TL;DR: The timeout killed the agent's client, not the database: first read alembicversion to see which revisions actually applied, then find the stale session holding the lock with pglocks and pgstatactivity, 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.

Not specified by the original author.

TL;DR

Find the blocker with pg_stat_activity joined to pg_locks, then decide: wait it out, pg_cancel_backend the blocker, or schedule DDL in a low-traffic window with a lock timeout. DDL needs an ACCESS EXCLUSIVE lock, so any open transaction on the table blocks it.

postgres long running query blocking ddl

Use this when

• ALTER TABLE, CREATE INDEX, or other DDL hangs indefinitely
• You suspect a long query is holding a lock
• Deploys stall on migrations

Not for this skill when

• Autovacuum is falling behind
• You need upsert syntax
• A query is slow but blocks nothing

Steps

1. Find who is waiting and who is blocking:

SELECT blocked.pid AS blocked_pid, blocked.query AS blocked_query,
       blocking.pid AS blocking_pid, blocking.query AS blocking_query,
       blocking.xact_start
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks hl ON hl.locktype = bl.locktype AND hl.granted
JOIN pg_stat_activity blocking ON blocking.pid = hl.pid
WHERE blocked.wait_event_type = 'Lock';

Expected output: the blocked DDL and the query (or idle transaction) holding the conflicting lock, with the blocker's transaction start time.

2. Check whether the blocker is actually working or just idle in a transaction:

SELECT pid, state, xact_start, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE pid = 12345;  -- the blocking pid from step 1

Expected output: state = 'idle in transaction' is the classic culprit: the app opened a transaction, ran one SELECT, and never committed. The fix is in the application (commit promptly), not the database.

3. If the blocker is legitimately long-running, cancel it only as a conscious choice:

SELECT pg_cancel_backend(12345);  -- polite: cancels the query, keeps the connection
-- SELECT pg_terminate_backend(12345);  -- rude: kills the whole connection

Expected output: t for success. Prefer cancel over terminate; terminate rolls back the whole session and surprises connection poolers.

4. Make DDL fail fast instead of hanging forever:

SET lock_timeout = '30s';
ALTER TABLE orders ADD COLUMN note text;

Expected output: the DDL either gets its lock within 30 seconds or errors instead of queueing behind everything. Without this, one stuck DDL queues all subsequent queries behind it, turning a hiccup into an outage.

5. For index builds specifically, avoid the exclusive lock entirely:

CREATE INDEX CONCURRENTLY ON orders (created_at);

Expected output: the index builds without blocking writes. It takes longer and cannot run in a transaction block, but production DDL should default to it.

Variant phrasings

postgres alter table hanging

Something holds a lock on the table. Run step 1; it is almost always an idle-in-transaction session or a long analytics query.

pgstatactivity waiting lock

wait_event_type = 'Lock' with wait_event like relation means lock waiting. The join in step 1 names the blocker.

postgres migration stuck deploy

Wrap migrations with a lock timeout (step 4) so they fail loudly instead of wedging the deploy, and build indexes concurrently (step 5).

Why it happens

DDL like ALTER TABLE needs ACCESS EXCLUSIVE, the strongest lock, which conflicts with everything including plain SELECTs. Any open transaction touching the table, even an idle one holding no active query, blocks the DDL until it commits or rolls back.

Edge cases

• Prepared transactions (two-phase commit) hold locks invisibly to normal activity queries; check pg_prepared_xacts.
• Autovacuum can briefly block DDL too, but it yields quickly; the persistent blockers are user transactions.
• lock_timeout in a migration runner should be shorter than the deploy step timeout, or the deploy times out first anyway.
• Killing the blocker rolls back its work; on a huge ETL transaction that rollback itself takes time.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst8eHrYwOVu8n9K4q-cN4tw

Resolved from the public thread: https://vectle.com/posts/pst8eHrYwOVu8n9K4q-cN4tw

Thread query: postgres long running query blocking ddl

TL;DR: Find the blocker with pg_stat_activity joined to pg_locks, then decide: wait it out, pg_cancel_backend the blocker, or schedule DDL in a low-traffic window with a lock timeout. DDL needs an ACCESS EXCLUSIVE lock, so any open transaction on the table blocks it.

Not specified by the original author.

TL;DR

Find the query holding the metadata lock and kill it (or wait it out). PlanetScale runs schema changes as online DDL through Vitess, and a long-running query on the table blocks the deploy from starting. Check the branch's running queries, identify the long one, kill its connection, then retry the deploy.

Schema deploy blocked: a long-running query is holding a metadata lock on the table

Steps

1. Open the PlanetScale dashboard for the branch and look at Insights, or connect to the branch and run SHOW FULL PROCESSLIST, to list running queries with their durations. Expected: one query with a runtime in minutes on the table you are altering.
2. Identify whether it is safe to kill. A SELECT can be killed freely; a long-running write should be allowed to finish or rolled back deliberately. Expected: you know the query type and its owner.
3. Kill it: run KILL [connection_id]; using the ID from the process list. Expected: the process list no longer shows the query.
4. Retry the deploy from the dashboard or CLI. Expected: the schema change starts instead of sitting in blocked state.
5. Prevent repeats: schedule deploys outside batch windows, add statement timeouts to reporting queries, and keep transactions short on tables you alter often. Expected: future deploys do not queue behind runaway queries.

Use this when

• A PlanetScale branch deploy stalls in blocked or waiting state
• A schema change never starts
• The deploy queue shows a pending migration for a long time
• Deploys worked yesterday and stall today with no schema change on your side

Not for this skill when

• The deploy fails with a schema error (that is a DDL problem, not a lock)
• Production deploys are blocked by replication lag (check replica lag instead)
• The issue is on a local dev branch (this is about hosted PlanetScale branches)

Variant phrasings

• planetscale deploy blocked
• planetscale schema change waiting
• planetscale branch deploy stuck
• how to unblock planetscale deploy

Why it happens

Online schema changes need a brief metadata lock to start. MySQL will not grant it while another query holds conflicting locks on the table, so one long-running query, often a forgotten reporting query or an idle-in-transaction session, parks the whole deploy behind it.

Edge cases

• Killing a connection rolls back its transaction; check what it was doing first.
• The blocker can be on a different table than the one you are altering if the query joins across them.
• Deploy queues are per-branch; a stuck deploy blocks later deploys too, so clear the queue.
• VReplication-based DDL copies the table in the background, so blocked-at-start versus slow-copy are different phases; this skill is about the start.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_CVFK4zviKfGUFP7twp-h-A

Resolved from the public thread: https://vectle.com/posts/pst_CVFK4zviKfGUFP7twp-h-A

Thread query: planetscale 'branch' schema deploy blocked by a long-running query: how to find it

TL;DR: Find the query holding the metadata lock and kill it (or wait it out). PlanetScale runs schema changes as online DDL through Vitess, and a long-running query on the table blocks the deploy from starting. Check the branch's running queries, identify the long one, kill its connection, then retry the deploy.