Re-running AI/OCR after a completed job raises IntegrityError #82
Loading…
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Severity: MEDIUM
The bug
backend/app/api/routes/jobs.py:97-114buildsidempotency_key = f"{job_type}:{photo_id}"on acolumn with a
unique=Trueconstraint, but only short-circuits when an existing job isqueuedor
running. If the previous job reached a terminal state, the code falls through and inserts asecond row with the same key →
IntegrityError→ 500.That means the second-ever rerun of a type on a photo fails — which is the normal case: "the
AI got it wrong, run it again."
Latent only because no worker currently moves jobs to terminal states. It will surface the moment
#2 lands.
Fix
Either suffix the key with an attempt counter or timestamp for rerun-type jobs, or clear/replace
the key when the prior job reaches a terminal state. Return the existing job for genuine
concurrent duplicates.
Done when
References
backend/app/api/routes/jobs.py:97-114backend/app/models/models.py(BackgroundJob.idempotency_key)Related: #2 describes the concurrent-duplicate requirement but not this case.
Done in
a239b8e. All three "done when" items covered.Keys now carry an attempt number:
{job_type}:{photo_id}:{n}, wherencounts prior jobs of that type for that photo. Sequential reruns get distinct keys and simply succeed.The part worth recording is which check enforces uniqueness. The
get_active_for_entityshort-circuit is a fast path only. Relying on it to prevent duplicates would reintroduce exactly the check-then-write race just fixed in #77 — two requests both see no in-flight job, both insert. So the unique constraint stays the arbiter: two simultaneous requests compute the same attempt number, one wins the insert, and the loser catchesIntegrityError, re-reads the winner's job and returns it. Nobody sees a 500, and no second job is created.I took the attempt-counter option rather than clearing the key on terminal state, because the latter needs the worker to cooperate and the worker doesn't exist yet — this had to be fixable without #2.
Tests cover both cases the issue asks for plus the ones around them: a second rerun after
success, afterfailed(retrying a failure being the most likely reason to rerun at all), four sequential reruns all distinct, a concurrent duplicate returning the same job id with exactly one row in the table, a planted attempt-1 key forcing attempt 2, and independence across job types and across photos.Worth noting the blast radius was larger than "an edge case": since the short-circuit only covered
queued/running, the second ever rerun of a type on a photo returned a 500. That's the normal case — "the AI got it wrong, run it again" — and it was invisible only because nothing currently moves jobs to a terminal state. It would have surfaced on day one of #2.