The race#
The orders table has status (written by release 2026.11.3) and a new state that the job fills with upper(status). We ran this on PostgreSQL 16.15 with psycopg 3.2. The job reads a batch, then writes each row:
rows = job.execute("SELECT id, status FROM orders WHERE state IS NULL ORDER BY id").fetchall()
# meanwhile, 2026.11.3 runs: UPDATE orders SET status = 'cancelled' WHERE id = 1
for order_id, status in rows:
job.execute("UPDATE orders SET state = %s WHERE id = %s", (status.upper(), order_id))
| id | status | state |
|---|---|---|
| 1 | cancelled | PAID |
| 2 | paid | PAID |
The job wrote what it had read, and the read was stale by the time of the write. Nothing in the second statement refers to the value it is replacing, so PostgreSQL has no reason to object.
We repeated the run with a trigger that sets state whenever status changes. The old release's update fired it, and the row briefly read cancelled, CANCELLED. Then the job's write arrived and set state back to PAID. The trigger fires on writes to status; the job writes only state.
Compute it in the statement#
UPDATE orders SET state = upper(status) WHERE state IS NULL;
We started a transaction that cancelled order 1 and held it open for half a second while this statement ran. The UPDATE waited 502 ms for the row lock, then produced:
| id | status | state |
|---|---|---|
| 1 | cancelled | CANCELLED |
| 2 | paid | PAID |
Under READ COMMITTED, an UPDATE that finds its target row locked waits for the other transaction. If that transaction commits, PostgreSQL re-reads the updated version of the row, checks the WHERE clause against it and computes the SET expressions from it. The transaction isolation chapter describes this behavior. The value comes from the row as it is when the write happens.
Under REPEATABLE READ the same statement fails instead, with SQLSTATE 40001, could not serialize access due to concurrent update. That is also correct, as long as the job retries the batch.
When the value cannot be computed in SQL#
Some conversions need application code: a call to another service or a parser the database lacks. Make the write conditional on the value you read:
UPDATE orders SET state = $1 WHERE id = $2 AND status = $3;
When we cancelled order 1 between the read and the write, this statement updated no row for it, and the job put the id on a retry list. Order 1 kept a null state until the retry, which a reconciliation count reports. A wrong value would look complete. Use IS NOT DISTINCT FROM in place of = if the old column can be null.
The test#
Run the job's batch in a test while a second connection changes the old column of a row in the batch between the job's read and its write. Pass when the row ends with the new value computed from the change, or stays unfilled and is retried. Find every backfill mismatch with IS DISTINCT FROM is the count to run afterwards. More backfill labs are in moving data while two application releases share one database.