alembic asked for confirmation on a destructive downgrade and the agent's run st

Not specified by the original author.

TL;DR: Alembic itself runs headless - the prompt is almost certainly coming from a custom env.py hook or a wrapper script, not from alembic core. Find the prompt's source, review the downgrade SQL offline first with the --sql flag, then answer the prompt programmatically or parameterize the hook. Never let a destructive downgrade wait on a human in an unattended run.

alembic asked for confirmation on a destructive downgrade and the agent's run stalled with zero output for 10 minutes

1. Confirm the prompt exists and find its wording: run the same downgrade command interactively in a terminal. Expected: you see the actual confirmation question and can read which step asks it.

2. Find the prompt's source: search env.py and any wrapper scripts for interactive input calls. Expected: you locate the hook or wrapper that asks for confirmation - alembic core has no such prompt.

3. Review what the downgrade would do before answering anything: run alembic downgrade [target-revision] --sql and read the generated SQL. Expected: the full SQL of the downgrade printed without touching the database.

4. Decide the policy: if the hook is a safety guard, keep it for interactive use but give the agent a non-interactive path (an explicit flag or environment switch that records the approval). If it is leftover scaffolding, remove it. Expected: the agent's run has a deterministic, prompt-free path to the downgrade.

5. Add a stdin guard to the harness: any step that blocks on stdin with no tty fails fast. Expected: future prompt-waits become immediate errors, not 10-minute stalls.

Use this when

• an alembic downgrade (or any alembic run) stalls with zero output
• a confirmation prompt appears that alembic's own docs do not mention
• a destructive downgrade must run in an unattended agent session

Not for this skill when

• the downgrade fails with a SQL error - fix the migration
• you are unsure the downgrade is safe - stop and review it with a human first
• the stall has output (logs, progress) - that is a slow migration, not a prompt

Variant phrasings

• alembic downgrade hangs waiting for confirmation
• alembic run stalls with no output on destructive downgrade
• headless alembic blocked on interactive prompt

Why it happens

Alembic's command line is fully non-interactive by design - upgrades and downgrades never ask questions. Teams often add confirmation hooks into env.py or wrap the CLI in a script that prompts before destructive operations. Those additions work fine for a developer at a keyboard and freeze solid in an agent sandbox with no tty: the process blocks on input that will never arrive, producing zero output for as long as the harness lets it wait.

Edge cases

• Reviewing with --sql shows the SQL but not data-loss consequences - a DROP COLUMN looks innocent in SQL and destroys data. Read it as a human would.
• If the hook reads from the real stdin even with the bypass flag set, fix the hook - a bypass that does not bypass is worse than no bypass.
• Downgrades that drop tables cannot be undone by re-running upgrade - take a backup or snapshot before any destructive downgrade, attended or not.
• An agent that can self-approve destructive downgrades needs that authority stated explicitly in its runbook, not implied.

Provenance

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

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

Thread query: alembic asked for confirmation on a destructive downgrade and the agent's run stalled with zero output for 10 minutes

TL;DR:

Not specified by the original author.

TL;DR: Autogenerate output depends on the Alembic version that renders it. Check the installed version against the project's requirements file, install the pinned version, and regenerate. Never commit autogenerate output produced under a different version than the project uses.

agent's alembic version is newer than the one that wrote the existing revisions - autogenerate produces different output now

1. Detect the skew.

pip show alembic | grep -i version
# compare against requirements.txt / pyproject.toml

Expected: the installed version is newer than the pinned one, and you know both numbers.

2. Install the pinned version in the environment that runs autogenerate.

pip install 'alembic==PINNED_VERSION'

Expected: pip show alembic now reports the pinned version.

3. Re-run autogenerate and compare against the suspect output.

alembic revision --autogenerate -m "describe the change"

Expected: the new revision contains only the genuine schema differences, without the rendering artifacts the newer version introduced.

4. Review the diff carefully before committing: with the pinned version, every remaining diff line should correspond to a real model change.

Expected: no unexplained diffs, no rendering-only noise.

5. Pin the version in CI and in the agent's environment setup so the next autogenerate runs under the same version.

Expected: a fresh environment installs the pinned version automatically.

Use this when

• alembic autogenerate produces different output than it used to for the same models
• The installed Alembic is newer than the project's pinned version
• A generated revision contains diffs nobody can explain
• An agent's sandbox auto-installed the latest Alembic

Not for this skill when

• The versions match and the diff is still surprising - then the models really changed, or the database drifted; investigate those
• The revision was hand-written rather than autogenerated - version skew does not affect hand-written revisions
• env.py itself fails to import - that is an environment or dependency problem, not a rendering skew
• You are deliberately upgrading Alembic - then regenerate the baseline understanding under the new version on purpose and review everything once

Variant phrasings

• alembic autogenerate different output after upgrade
• alembic version newer than revisions, autogenerate noise
• pip installed latest alembic, autogenerate changed
• how to pin alembic version for autogenerate

Why it happens

Autogenerate compares your models against the live database and renders the differences using the installed Alembic's comparison and rendering logic. That logic changes between versions: type comparison strictness, server-default rendering, and how ambiguous differences are reported all evolve. A newer Alembic therefore sees the same database through different eyes and writes different revisions, even when the models have not changed at all.

Edge cases

• An env.py written or modified under the newer version may import APIs the pinned version lacks. If env.py breaks after downgrading, check it for version-specific calls.
• The suspect revision may already be committed or even applied. Do not edit applied revisions - write a corrective revision under the pinned version instead.
• Sandboxes that pip install without a requirements file drift to latest on every fresh run. Always install from the pinned requirements in automation.
• If the project genuinely wants the newer Alembic, upgrade deliberately: update the pin, regenerate, review the full diff once, and tell the team.

Provenance

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

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

Thread query: agent's alembic version is newer than the one that wrote the existing revisions - autogenerate produces different output now

TL;DR:

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.