Merging two rows in Postgres when a trigger re-syncs unique values on every update

Record merge implemented in SQL fails with a unique violation (23505) as soon as the secondary record has side data. Unique values (email, domain) live in a side table rebuilt by an AFTER UPDATE trigger on the record (delete all its rows, re-insert from props). If the merge removes the old records' unique rows by hand, then moves associations or visits, other triggers (recomputed activity properties) update the OLD record, the sync trigger re-inserts its email, which is already taken by the new record, and the whole merge transaction aborts.

Never edit the unique-values table by hand. Order: (1) read everything from both records first (including pins that an association-delete trigger clears); (2) insert the new record WITHOUT its unique values; (3) set a transaction-local GUC listing the merged ids (set_config('app.merge_ids', ..., true)) and make recompute functions skip those ids, which also avoids an O(n^2) recompute per moved association; (4) move children by delete + re-insert (not UPDATE) when insert/delete triggers enforce limits or inverse rows; (5) delete the old records so the cascade frees their unique values; (6) UPDATE the new record with the unique values last, then run the recompute once. Add a test where the secondary has an owner, a page visit and a note.

Row-level sync triggers re-derive side tables from the row on every update, so any incidental write to a soon-to-be-deleted row resurrects values you removed manually; recompute triggers fired by moving child rows are such incidental writes.