`scripts/clear-hung-alter.sh`

16 lines
#!/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