p24-infra Docs

queue-dispatcher-dead-column-cleanup

3 min read

Playbook — dead DB-column references in the queue dispatcher

Symptom: scripts/queue-dispatcher-loop.py (or another script) reads a dev_r_server_capacity column with server.get("<col>") or server.get("<dropped_col>", default) where <dropped_col> no longer exists. The .get() for a dropped column always returns None, so the or short-circuits to the hardcoded default — silently masking the real DB value and any bug in it. This is the Python-side mirror of the SQL SELECT mismatch that halted dispatch for ~1.5 h on 2026-06-27 (issue #1766): a migration removed columns while code still referenced them.

This is a latent failure, not a crash: the dispatcher keeps running but computes RAM budget from constants instead of live capacity, so over-/under-subscription goes unnoticed.

Context — which columns were dropped and when

MigrationFileEffect
044monitoring/supabase/migrations/044_server_ram_capacity.sqlAdded total_ram_gb, system_reserve_gb to dev_r_server_capacity
045monitoring/supabase/migrations/045_drop_redundant_ram_cols.sqlDropped both — they duplicated the pre-existing os_ram_gb / reserved_ram_gb

After migration 045 the only valid RAM columns are os_ram_gb and reserved_ram_gb. Any code still naming total_ram_gb or system_reserve_gb is dead.

Note: the originating issue (#1766) loosely attributed the drop to “migration-044”. The add was 044; the drop was 045. Always confirm against the actual DROP COLUMN migration.

Trigger / confirm

  1. Grep every script for the dropped column names — must return nothing but this playbook:

    cd /opt/p24-infra
grep -rn 'total_ram_gb\|system_reserve_gb' --include='*.py' --include='*.sh' scripts/ monitoring/

    A hit in a .py/.sh SELECT, INSERT, or server.get(...) call → dead reference, fix it.

  2. Confirm the columns are actually gone from the table (key names only, never values):

    # via SOPS-loaded service key — prints column names only
sops exec-env secrets/monitoring.env.sops 'curl -s \
  "$SUPABASE_URL/rest/v1/dev_r_server_capacity?select=*&limit=1" \
  -H "apikey: $SUPABASE_SERVICE_KEY" -H "Authorization: Bearer $SUPABASE_SERVICE_KEY"' \
  | python3 -c "import sys,json; print(sorted(json.load(sys.stdin)[0].keys()))"

    total_ram_gb / system_reserve_gb absent from the printed key list → confirmed dropped.

  3. Confirm the dispatcher still parses cleanly after any edit:

    python3 -m py_compile scripts/queue-dispatcher-loop.py && echo OK

Fix

In scripts/queue-dispatcher-loop.py, read the live columns directly with a sane default — no or server.get(<dropped>, …) fallback:

# BEFORE (dead fallback — always resolves to the constant):
os_ram     = server.get("os_ram_gb") or server.get("total_ram_gb", 8)
reserve    = server.get("reserved_ram_gb") or server.get("system_reserve_gb", 2)
 
# AFTER:
os_ram     = server.get("os_ram_gb", 8)
reserve    = server.get("reserved_ram_gb", 2)

Then re-run the grep and py_compile from “Trigger / confirm”. The on-server copy updates when vps-i1 pulls the merged commit (cd /opt/p24-infra && git pull); the dispatcher loop picks up the new file on its next cycle (no restart required for the loop script, but a restart forces it immediately if needed).

Escalation

If a dropped column is referenced inside a SQL SELECT sent to PostgREST (not just a Python .get()), the request returns HTTP 400 / SQLSTATE 42703 and curl -f treats it as a hard failure — this can zero out the server list and halt dispatch entirely (the #1766 outage). That is a P1: hotfix the SELECT on the leader (sed -i on vps-i1), then land the code fix via PR. Send the Discord alert (P24_DISCORD_INFRA_SCRIPTS_ERRORS_WEBHOOK_URL) and open a bug issue per the error-notification standard.

Prevention

When a migration drops (or renames) a column, the PR that ships it MUST:

  1. Grep the whole repo for the column name before merging: 
    grep -rn '<dropped_column>' --include='*.py' --include='*.sh' --include='*.sql' .
  2. Remove the column from every SELECT / INSERT / server.get(...) it appears in — including “fallback” or server.get(<col>, default) patterns, which become silent dead code, not errors.
  3. Prefer server.get("<col>", <default>) over server.get("<a>") or server.get("<b>", <default>) when one column has fully superseded another — a single source of truth can’t silently mask the other being null.
  4. Add the dropped column names to this playbook’s table so the next migration author can grep here.

Graph View

Backlinks