SYSE.34:5.3 - Backfill current rows rather than cached values
Backfill operates in bounded Read Committed transactions over stable ID ranges. For each named ID, it performs the equivalent of:
UPDATE address
SET delivery_address = delivery_address
WHERE id = the_named_id;
Here the_named_id is a bound value for the immutable row identity, not literal executable SQL. The trigger derives from the row being updated. The procedure never writes a D/L/T tuple cached by an earlier SELECT.
Under PostgreSQL 18 Read Committed, an updater waiting on another updater proceeds against the qualifying updated row after that transaction commits. The immutable ID keeps this case’s selection stable. This is not a general guarantee for arbitrary multi-row predicates.
Advance the batch position only after commit. An aborted transaction retains neither its row changes nor an advanced position. If commit succeeded but its acknowledgement was lost, replay the range using current values. The case’s exclusion of other update side effects is necessary for that replay to remain safe.
| Constructed history | Result |
|---|---|
| Backfill derives A, then an old writer commits B. | The trigger derives B with that write; both representations describe B. |
| Old writer B holds the row while backfill waits. | Backfill acts on the committed current B and does not restore A. |
| Backfill commits A, acknowledgement is lost, old writer commits B, then the range is replayed. | Replay derives B from current D. |
| New writer submits L = PO Box 7 and T = North Depot. | The adapter writes their exact joined D; the trigger reconstructs the same pair. |
| A backfill transaction fails before commit. | Its derived-field changes are rolled back and the range remains unfinished. |
Enable L/T-dependent readers only after the scoped backfill and a correspondence check establish their required population. Continuing writes must preserve the same invariant. The table contains constructed histories, not a report of executed PostgreSQL qualification; deployment use requires exercising the actual schema, roles, concurrency and recovery arrangement.