Blog · August 7, 2026

The hypothesis said the ON CONFLICT arbiter only watched one of two unique indexes. The CI logs said otherwise.

An earlier post left repo_resolve's 16-way concurrency race with a documented hypothesis and a rule for when to act on it: don't patch a plpgsql concurrency bug on a code-read alone, wait for a third occurrence, and get real diagnostics before touching it. The rule held. What it produced, on the third look, was a different bug than the one the hypothesis named.

What the hypothesis got right — and what it assumed

The suspect was real: repositories carries two independent partial-unique indexes, (tenant_id, url_fingerprint) and (tenant_id, slug), and repo_resolve's INSERT ... ON CONFLICT can only name one of them as its arbiter. Sixteen concurrent callers registering the identical new repository with no owner/name given derive an identical fingerprint and an identical slug from the same raw remote_url — a real double-identity collision waiting to happen. The prior post reasoned from there to "a loser on the unnamed index raises a plain unique_violation the handler isn't watching for" — plausible, well-argued, and wrong in one specific way: the exception handler was catching unique_violation. It just wasn't the exception being raised.

What the CI run actually said

This time the failing run (30611440491) had enough signal to stop guessing: distinct_repo_ids=1 — no duplicate row, the correctness invariant held — paired with not-ok-errs=CONFLICT,CONFLICT from two of the sixteen callers. packages/core/src/errors.ts maps both SQLSTATE 23505 (unique_violation) and 40P01 (deadlock_detected) to the same client-facing code: "CONFLICT", so both failure modes look identical from the caller's side — which is exactly what made the earlier read incomplete. repo_resolve's own handler only ever caught unique_violation. The actual SQLSTATE reaching it, on a genuine simultaneous first-registration burst, was 40P01.

The mechanism: two concurrent losers racing the same insert can each acquire a value lock on one of the two unique indexes while waiting on the other — a lock-order inversion across url_fingerprint and slug that Postgres's own deadlock detector resolves by killing one side. That kill arrives as deadlock_detected, not unique_violation, and it sailed straight through a handler that had never been told to expect it.

The fix is the same shape, aimed at the right target

exception when unique_violation or deadlock_detected then
  -- Same identity: a concurrent twin already won this exact fingerprint -> adopt it.
  select id into v_id from public.repositories
    where tenant_id = v_tenant and url_fingerprint = v_fp and deleted_at is null;
  ...

Widen the caught exception class, and turn the original one-shot retry into a bounded loop (five attempts) that re-checks both the fingerprint and the slug on every failure instead of just the first. A deadlock loser has nothing wrong with its data — the row it wanted either already exists (adopt it) or the slug is genuinely squatted by a different identity (de-collide and retry) — it just needs another chance once the winner's transaction has committed and released its locks. Body-only change, function signature unchanged, so it shipped as a direct migration with no contract or schema drift to regenerate around.

Verified against the same oracle that found it: the adversarial-gates concurrency probe, which had failed two of sixteen callers on both prior occurrences, ran 16/16 clean post-merge, and the suite's full probe count went from 12/13 held to 13/13.

The rule that made this the right third look

The standing rule from the first post — don't patch on a hypothesis, wait for a confirmed third occurrence with real diagnostics — didn't just delay a fix. It changed what the fix ended up being. A patch shipped off the first post's reasoning would have widened the ON CONFLICT arbiter to cover both indexes, which doesn't touch a deadlock at all — a plausible-sounding change that would have left the actual bug live behind a green-looking CI run, at least until the next probe run got unlucky the same way. Waiting for the diagnostics the second occurrence's harness update was built to capture is what turned "the arbiter looks like the problem" into "the arbiter was never the problem — the exception handler's catch list was."

Start at ledgenter.com.