Original correlated-subquery form was O(files × manifests) and
O(blobs × manifests) — hit statement_timeout on a large production
customer's DB and hard-failed app boot (migration rolls back → sqlx
marks failed → next start also fails; only recovery was bumping the
role-level timeout manually before restart).
Rewrite:
* SET LOCAL statement_timeout = 0 (tx-scoped, auto-reset at COMMIT)
— lifts the safety net for THIS migration only, so operators
with restrictive session defaults can complete the one-time
repair without intervention.
* Both UPDATEs replaced with WITH ... UPDATE ... FROM CTE + LEFT
JOIN patterns — single scans per source table, linear total work.
* unnest(chunk_hashes) replaces b.hash = ANY(...) — cost is
O(Σ chunk-array lengths), not O(blobs × manifests). No GIN
index needed.
Measured on sandbox with 303 rows of drift: 570 ms in the original
form. New form on 200 rows drift, cache-warm: 15 ms. Second run on
clean data: 10 ms no-op — idempotency preserved.
Content semantics unchanged — same auditor formulas, same idempotence
guarantee, same content-safety guarantees; only algorithmic
complexity + statement_timeout scope changed.
execute once via SQL migration to fix old entries
next: will be catch by manifests_consistency job, if new are found it means new bug discovered
no auto repair to prevent hidding bugs, operator can still invoke repair while waiting a fix
Edouard Vanbelle