patternModerate
Name the first refused row of a bulk SQL update by bisecting with savepoints, not row-by-row replay
Viewed 0 times
savepointbisectbulk updatefirst failing rowpostgres.jstrigger refusal
Problem
A bulk "update ... where id = any($ids)" runs row triggers that enforce business rules. When it fails, the UI must say which record was refused (the first one in the user's order, with the earlier ones applied, so cross-row rules like unique values behave the same). Replaying the rows one by one in savepoints to find it costs n round trips and n single-row writes: 10,000 rows with the refused one last took about 60 s while holding row locks.
Solution
Try the whole block in a savepoint. If it fails with a rule error, split it into halves and try each half in its own savepoint, in order. A half that succeeds is kept (savepoint released). A half that fails is rolled back and split again, until one row is left: that row is the first refused one. If every half succeeds after the whole block failed (a rare interaction), rethrow the original error. This takes about 3n row writes and 2·log2(n) round trips. The same 10,000-row case went from about 60 s to 3.6 s. The whole transaction is still rolled back afterwards, because the action is all-or-nothing.
Gotchas
- Keep the halves in the user's order and keep a succeeding half applied, so cross-row rules (unique values) name the same row as a linear replay would.
- A block that fails at its first physical row aborts at once, so failing attempts are often cheaper than n.
Code Snippets
postgres.js, inside a transaction
async function enBloc(tx, ids, write) {
const attempt = async (part) => { try { await tx.savepoint((sp) => write(sp, part)); return null; } catch (e) { if (!RULE_CODES[e.code]) throw e; return e; } };
const split = async (part, err) => {
if (part.length === 1) throw named(part[0], err);
const mid = Math.ceil(part.length / 2);
for (const half of [part.slice(0, mid), part.slice(mid)]) { const e = await attempt(half); if (e) await split(half, e); }
};
const e = await attempt(ids);
if (!e) return;
await split(ids, e);
throw e;
}Revisions (0)
No revisions yet.