p24-infra Docs

worker-queue-constraint-vs-function-mismatch

2 min read

Worker-queue functions throw 23514 — CHECK constraint missing a value the function inserts

Trigger

A dev_r_worker_queue slot-reservation function fails with:

ERROR: 23514: new row for relation "dev_r_worker_queue" violates check constraint
       "dev_r_worker_queue_<col>_check"

Seen with reserve_subagent_slots() (inserts job_type='subagent', weight='subagent') and claim_gha_slot() (inserts job_type='github-actions'). The functions were added in migration 038 but the CHECK constraints (defined in migration 036) were never widened, so the functions threw on every call and silently never inserted any rows.

Confirmation

-- list all CHECK constraints on the table
SELECT conname, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'dev_r_worker_queue'::regclass AND contype = 'c';
 
-- compare against the literal values each function inserts
--   reserve_subagent_slots -> job_type='subagent', weight=p_weight ('subagent')
--   claim_gha_slot         -> job_type='github-actions', weight='light'|'heavy'

If a value the function inserts is absent from the matching *_check constraint, that is the bug.

Fix

Widen the constraint with a new migration (drop + re-add; idempotent):

ALTER TABLE public.dev_r_worker_queue DROP CONSTRAINT IF EXISTS dev_r_worker_queue_job_type_check;
ALTER TABLE public.dev_r_worker_queue ADD  CONSTRAINT dev_r_worker_queue_job_type_check
  CHECK (job_type IN ('dev-issue','infra-alert','infra-task','subagent','github-actions'));
 
ALTER TABLE public.dev_r_worker_queue DROP CONSTRAINT IF EXISTS dev_r_worker_queue_weight_check;
ALTER TABLE public.dev_r_worker_queue ADD  CONSTRAINT dev_r_worker_queue_weight_check
  CHECK (weight IN ('light','heavy','playwright','subagent'));

Apply via mcp__claude_ai_Supabase__apply_migration. No existing rows use the new values (the functions never succeeded), so widening rejects no data. Fixed in migration 041 (#1323).

Verification

SELECT count(*) FROM reserve_subagent_slots('canary','subagent',10,'bms-4'); -- expect 10
SELECT claim_gha_slot('canary-gha','bms-4','heavy');                         -- expect a bigint id
-- ALWAYS clean up canary rows afterwards (a sibling DELETE inside the SAME
-- statement won't see them — Postgres CTEs share one snapshot):
DELETE FROM dev_r_worker_queue WHERE parent_session_id='canary' OR github_run_id='canary-gha';

Prevention

When a new migration adds a function that INSERTs a literal into a column with a CHECK constraint, widen that constraint in the same migration. Grep the function body for every job_type=, weight=, status= literal and confirm each is in the corresponding constraint.

Escalation

None required — pure DDL constraint widening. If a DOWN is ever run, it only succeeds when no subagent/github-actions rows exist (delete them first).

Graph View

Backlinks