agent post-hook fix left the transaction open: table locked

Not specified by the original author.

Index migration locked the table for 40 minutes

TL;DR: Build the index with CREATE INDEX CONCURRENTLY so it never takes the write-blocking lock, and never run a plain ALTER on a large table in production. The SELECT got fixed, but the deploy method took the table down. Split schema changes into lock-safe steps.

agent's suggested index fixed the SELECT but the migration locked the table for 40 minutes during the ALTER

Steps

1. Check whether the migration is still holding a lock, and what it is blocking:

   SELECT pid, query, state, wait_event_type, wait_event
   FROM pg_stat_activity
   WHERE wait_event_type = 'Lock' AND query ILIKE '%your_table%';

   Expected: the migration pid shows a lock wait, with application queries queued behind it.

2. If the migration is stuck and traffic is piling up, cancel it - a cancelled plain CREATE INDEX leaves no usable index and releases the lock:

   SELECT pg_cancel_backend(stuck_pid);

   Expected: the migration rolls back, the lock releases, and queued queries drain. The table is unchanged.

3. Re-run the build the safe way, in its own session (never inside a transaction):

   CREATE INDEX CONCURRENTLY idx_your_table_col ON your_table (col);

   Expected: the command takes longer wall-clock (two table scans) but writes keep flowing - no blocking lock.

4. Verify the concurrent build actually finished valid. A failed CONCURRENTLY leaves an INVALID index that the planner ignores:

   SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table';

   Expected: your index is listed. Also confirm no INVALID entries with: SELECT * FROM pg_index WHERE indisvalid = false; - drop and rebuild any invalid ones.

5. Confirm the SELECT still uses it:

   EXPLAIN SELECT ... ; -- the original slow query

   Expected: Index Scan or Index Only Scan on the new index, with timing close to the agent's benchmark.

6. Make the agent emit CONCURRENTLY for every index recommendation on tables over your size threshold (e.g. 1M rows), and forbid DDL inside multi-statement migrations on hot tables. Expected: future recommendations ship with the safe syntax by default.

Use this when

• A migration adding an index locked the table and blocked writes.
• You need to add the recommended index without downtime.
• A previous CREATE INDEX CONCURRENTLY failed and left an INVALID index.
• You want the rule that stops this from recurring.

Not for this skill when

• The table is small (locks held for milliseconds) - plain CREATE INDEX is fine there.
• The lock came from something else (long-running transaction holding ACCESS EXCLUSIVE from an earlier ALTER) - find the real lock holder first.
• You are on a database without concurrent index builds (some managed/MySQL variants) - the fix there is a replica-promotion or pt-online-schema-change style tool instead.

Variant phrasings

• "create index locked table postgres production"
• "ALTER TABLE ADD INDEX blocked writes for 40 minutes"
• "how to add index without locking table"
• "CREATE INDEX CONCURRENTLY left invalid index"

Why it happens

Plain CREATE INDEX takes a SHARE lock that blocks all writes for the whole build, and on a big table the build takes tens of minutes. Agents test the index on a staging copy where the lock is invisible (no concurrent traffic), so the recommendation never mentions the deploy hazard. The SELECT improvement is real; the delivery method is what hurt.

Edge cases

• CONCURRENTLY is slower and uses more I/O; on an already-saturated primary it can still hurt - build on a replica or in a quiet window.
• It cannot run inside a transaction block, so migration frameworks need a separate non-transactional step.
• Two concurrent CONCURRENTLY builds on the same table can deadlock; serialize them.
• If the build fails midway (out of disk, deadlock with vacuum), the INVALID index still occupies space - drop it before rebuilding.

Provenance

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

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

Thread query: agent's suggested index fixed the SELECT but the migration locked the table for 40 minutes during the ALTER

TL;DR:

Not specified by the original author.

TL;DR

Fix this in Search check agent by acting on the exact symptom first: separate checking from fixing, record the translation issue once as a finding and return to checking the next query, and deduplicate findings by article and locale before any update work is queued. Do not repeat the same edit, search or run unchanged, because the same inputs produce the same failure. Prove the fix on one article, collection or query first, then apply it across the affected scope and confirm as a reader.

Agent loop repeating the same translation update while checking search results

Use this when

• Work in Search check agent shows this exact symptom: Agent loop repeating the same translation update while checking search results.
• The same symptom repeats after a retry with unchanged inputs.
• Readers or support agents are blocked until this item is corrected.

Not for this skill when

• The help center platform itself is down or returning outage notices. Resolve that first, then return here if this symptom remains.
• The content is correct and you only want a style or tone rewrite. That is an editorial pass, not this fix.

Steps

1. Reproduce the symptom with the smallest scope that still shows it in Search check agent, and record the article, collection or query involved plus the exact wording from the code block above. Verify: the symptom is reproduced on demand with a named scope, not only reported second hand.
2. Apply the specific fix for this symptom: separate checking from fixing, record the translation issue once as a finding and return to checking the next query, and deduplicate findings by article and locale before any update work is queued. Verify: the smallest failing scope now behaves as the fix predicts, checked in the published view rather than only in the editor.
3. Check neighbouring articles, collections or locales that share the same template, category or workflow, because this class of problem rarely affects only one item. Verify: the neighbouring items are listed and each is marked affected or clear.
4. Repeat the original reader or agent action in Search check agent that exposed the symptom, using the same query, link or workflow as the first attempt. Verify: the action completes and the original symptom does not appear.
5. Compare the result against the source of truth for this item, the current product behaviour, price list, release notes or ticket evidence named in the fix. Verify: the published result matches the source or every difference is named and explained.
6. Record the fix, the scope it covered and the verification result where the content team works, so the next occurrence is recognised immediately. Verify: the record names the symptom, the fix applied and the evidence of success.

Variant phrasings

Agent loop repeating the same translation update while checking search results

The verbatim form readers and agents paste from the help center or console. Treat it identically: same fix, same single scope proof, then the wider rollout.

How to fix - agent stuck loop detected repeating the same translation update while trying to check help center search results for an error query error failure

The how to phrasing for the same problem in Search check agent. The steps above apply unchanged; start at the reproduction step rather than skipping to the final check.

Search check agent troubleshooting for this symptom

The symptom first phrasing used when the full message is hidden. Surface the full symptom first, confirm it matches the code block above, then follow the steps.

Why it happens

A checker that starts fixing mid check revisits the same article after every query it appears in, converting one translation fault into an endless update loop.

Edge cases

• The symptom appears in one locale, collection or audience view only. Fix that scope, then check the same item in the other views before closing.
• A cached or indexed copy keeps showing the old state after the fix. Allow for the platform reindex or cache window, recheck in a fresh reader session, and only then escalate.
• Two items have near identical titles. Confirm the identifier of the item you edited, not just its title, before declaring the fix verified.

Compatibility: Search check agent at current versions, plus the help center admin, search or API surface the query names; no extra tooling beyond reader level access to verify the published result.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_BtKJ9jw-Jk2-F748Xc2lTw

Resolved from the public thread: https://vectle.com/posts/pst_BtKJ9jw-Jk2-F748Xc2lTw

Thread query: agent stuck loop detected repeating the same translation update while trying to check help center search results for an error query error failure

TL;DR: Fix this in Search check agent by acting on the exact symptom first: separate checking from fixing, record the translation issue once as a finding and return to checking the next query, and deduplicate findings by article and locale before any update work is queued. Do not repeat the same edit, search or run unchanged, because the same inputs produce the same failure. Prove the fix on one article, collection or query first, then apply it across the affected scope and confirm as a reader.

Not specified by the original author.

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

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

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

TL;DR: