japan: clear-hung-alter.sh (cancel a stuck vendor_registry ALTER) + loader retries the column-add 8x to catch a lock gap on the busy table

ffe7b6f5cfdc49a788bc171d8d511a196a828f93 · 2026-07-06 17:38:33 -0700 · Steve

Files touched

M db/load-distributor-lines.js
A scripts/clear-hung-alter.sh

Diff

commit ffe7b6f5cfdc49a788bc171d8d511a196a828f93
Author: Steve <steve@designerwallcoverings.com>
Date:   Mon Jul 6 17:38:33 2026 -0700

    japan: clear-hung-alter.sh (cancel a stuck vendor_registry ALTER) + loader retries the column-add 8x to catch a lock gap on the busy table
---
 db/load-distributor-lines.js | 14 ++++++++++----
 scripts/clear-hung-alter.sh  | 15 +++++++++++++++
 2 files changed, 25 insertions(+), 4 deletions(-)

diff --git a/db/load-distributor-lines.js b/db/load-distributor-lines.js
index ca2d456..67776b9 100644
--- a/db/load-distributor-lines.js
+++ b/db/load-distributor-lines.js
@@ -26,12 +26,18 @@ const ALLOWED = 'ACEGHIJKLOPQRTUWXYZ'.split(''); // no B,D,S,F,M,N,V (hard to he
   // with a short lock_timeout so it fails fast instead of blocking the table.
   const col = await db.query("select 1 from information_schema.columns where table_name='vendor_registry' and column_name='use_for_ai'");
   if (!col.rows.length) {
-    await db.query("SET lock_timeout='4s'");
-    try { await db.query('ALTER TABLE vendor_registry ADD COLUMN use_for_ai boolean DEFAULT false'); }
-    catch (e) {
-      console.error(`ALTER add use_for_ai failed (${e.message}). vendor_registry was locked/busy — add the column in a quiet moment:\n  psql "$DATABASE_URL" -c "SET lock_timeout='10s'; ALTER TABLE vendor_registry ADD COLUMN use_for_ai boolean DEFAULT false;"\nthen re-run this loader.`);
+    // busy prod table → try a few times to catch a brief lock gap (each attempt fails fast).
+    let added = false;
+    for (let i = 1; i <= 8 && !added; i++) {
+      await db.query("SET lock_timeout='2s'");
+      try { await db.query('ALTER TABLE vendor_registry ADD COLUMN use_for_ai boolean DEFAULT false'); added = true; }
+      catch (e) { console.error(`  ALTER attempt ${i}/8: ${e.message}`); await new Promise((r) => setTimeout(r, 3000)); }
+    }
+    if (!added) {
+      console.error('Could not add use_for_ai (vendor_registry stayed busy). If an OLD hung ALTER is holding it, run scripts/clear-hung-alter.sh first, then re-run.');
       await db.end(); process.exit(2);
     }
+    console.log('added column use_for_ai');
   }
   await db.query("SET lock_timeout='4s'");   // keep row-level writes from hanging too
 
diff --git a/scripts/clear-hung-alter.sh b/scripts/clear-hung-alter.sh
new file mode 100755
index 0000000..e8dfff5
--- /dev/null
+++ b/scripts/clear-hung-alter.sh
@@ -0,0 +1,15 @@
+#!/usr/bin/env bash
+# Diagnose + cancel any HUNG "ALTER TABLE vendor_registry" left by the earlier loader run
+# (the pre-fix version had a naked ALTER with no lock_timeout and can hang forever,
+# queue-blocking the live app's vendor_registry queries). Cancels ONLY ALTER statements.
+# Run under Steve:  bash ~/Projects/japan-enrich/scripts/clear-hung-alter.sh
+set -euo pipefail
+ssh root@45.61.58.125 bash -s <<'REMOTE'
+DSN=$(grep -rhoE "^DATABASE_URL=[^ ]+" /root/Projects/*/.env 2>/dev/null | cut -d= -f2- | tr -d '"' | grep dw_admin | head -1)
+[ -z "$DSN" ] && DSN="postgresql:///dw_unified"
+echo "=== queries touching vendor_registry ==="
+psql "$DSN" -c "select pid, state, wait_event_type, now()-query_start as age, left(regexp_replace(query,'\s+',' ','g'),70) as query from pg_stat_activity where query ilike '%vendor_registry%' and pid <> pg_backend_pid() order by query_start"
+echo "=== cancelling hung ALTERs on vendor_registry (ALTER only) ==="
+psql "$DSN" -c "select pid, pg_cancel_backend(pid) as cancelled from pg_stat_activity where query ilike '%alter table vendor_registry%' and pid <> pg_backend_pid()"
+echo "done"
+REMOTE

← c9bf0f6 japan: loader ALTER safety — skip if use_for_ai exists (no l · back to Japan Enrich · japan: use_for_ai via side table vendor_ai_flags (NO ALTER o 23c5514 →