fix(people): portable dirty-mark upsert (prod 500 on person mutations, 0 suggestions) #768
Loading…
Reference in a new issue
No description provided.
Delete branch "fix/dirty-mark-pg-merge"
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?
Production regression from #760: FaceRecognitionDirtyMarkService.markDirty used an ANSI MERGE with an aliased target in the WHEN MATCHED SET clause — H2 (CI) accepts it, PostgreSQL rejects it: 'column d of relation face_recognition_dirty does not exist'. Consequences on prod since #760/#761: every person mutation that marks dirty 500-ed (remove-from-person, candidate accept, merge, rename, occurrence confirm/move — user-reported remove 500), face_recognition_dirty stayed empty (0 rows), the reconcile scheduler never re-armed recognition → 0 suggestions.
Also not portable: a single-statement INSERT ... ON CONFLICT DO UPDATE (H2 in the regression suite rejects DO UPDATE — verified). Fix: two portable statements (UPDATE, then INSERT when nothing changed) — identical semantics on PG and H2, serialized per owner by the caller's edit transaction.
Verified: prod SQL smoke test + targeted H2 test classes green.
markDirty used an ANSI MERGE with an aliased target in the WHEN MATCHED SET clause (d.marked_at): H2 accepts it, PostgreSQL rejects it ('column d of relation face_recognition_dirty does not exist'). Every person mutation that marks dirty 500-ed on production since #760 — candidate accepts, occurrence remove/confirm, merge/rename/dissolve — leaving face_recognition_dirty empty, so the reconcile scheduler never re-armed recognition (0 suggestions) and remove-from-person 500-ed. A single-statement ON CONFLICT DO UPDATE is also not portable (H2 in the test suite rejects DO UPDATE). Replace with two portable statements: UPDATE first, INSERT when nothing changed — serialized per owner by the caller's edit transaction, identical semantics on both engines.