4. Construct a compatible data change and its recovery boundary
Further ordinary case facts now matter. One non-partitioned PostgreSQL 18 table has an immutable row ID and non-null Unicode delivery_address D. Old applications read and replace D as a whole. The new form edits the first display line L and remaining display text T; it does not infer postal structure.
Other address-writing triggers, replicated writers that bypass local triggers and external side effects of these row updates are absent in this bounded construction. If inspection cannot establish those premises, the mechanism below is not qualified.
SYSE.34 constructs the correspondence. Split D at its first line feed, LF. With no LF, L = D and T = null; otherwise T contains everything after the first LF. Preserve all characters, including further line feeds. An empty tail after a trailing LF is not null. The inverse is D = L when T is null, otherwise D = L + LF + T; L must contain no LF.
Thus “12 Oak St” has an absent tail, while the same text followed by LF has an empty tail. Both reconstruct exactly. Normalization, postal parsing or a new value that cannot pass this inverse crosses an explicit information-loss boundary.
During coexistence, D remains the single write authority. Old applications write D. The new adapter validates L/T, joins them and writes D, not independent derived columns. A row-level BEFORE INSERT OR UPDATE trigger derives L/T from NEW.delivery_address and returns the row. PostgreSQL 18 trigger semantics supply synchronous same-row execution in the triggering transaction.
The authorized operator briefly fences new address writes and drains in-flight writers for schema/trigger installation. Before writers resume, actual roles must be unable to bypass the trigger or independently write derived columns. Inspect broad table grants, inherited rights, ownership and superuser access under PostgreSQL 18 GRANT; a column revocation does not cancel a table grant.
Backfill uses bounded Read Committed transactions over stable ID ranges. For each named row it performs the equivalent of UPDATE address SET delivery_address = delivery_address WHERE id = the_named_id. The trigger derives from the current row being updated, never from an earlier cached D/L/T tuple. PostgreSQL 18 Read Committed supplies the waiting-updater behavior for this stable-ID construction.
Advance the range position only after commit. An aborted transaction rolls back its changes and leaves the range unfinished. A lost acknowledgement permits replay from current values only because no other update side effect makes that repeat unsafe.
| Constructed history | Preserved result |
|---|---|
| Backfill derives A, then an old writer commits B. | The trigger derives B with the old write; neither reader prefers stale A. |
| An old writer holds the row, changes A to B and commits while backfill waits. | Backfill operates on current B rather than restoring an earlier observation. |
| Backfill commits A, acknowledgement is lost, an old writer commits B, then the range is replayed. | Replay derives B; a failed first transaction would retain neither its changes nor an advanced position. |
| The new form submits L = PO Box 7 and T = North Depot. | The adapter writes their exact joined D; the trigger reconstructs the same pair. |
Do not enable L/T-dependent readers before the scoped backfill and correspondence check establish their required population. Continuing writes retain the invariant. This release keeps D, the trigger and both reader contracts; an old executable can return without undoing legitimate new-form writes, subject to its other compatibility conditions.
Contraction is later work. Fence and drain all old writers and compatibility adapters, select the new write authority and validate its consumers before removing the old contract. If writes have lost data or compatibility required by the old contract, the old binary alone cannot restore that contract.
The separate PostgreSQL 18 point-in-time recovery Method requires an appropriate base backup and continuous required WAL archive, not a logical dump substituted into that mechanism. Restore the whole cluster into an appropriately isolated recovery arrangement, inspect the result and compare achieved loss/time with the authorized limits. Later business events require their own reconciliation. An archive gap blocks that fallback, not an independent non-data-changing artifact test.
Before using this data-change mechanism, exercise the schema, roles, concurrency, load and recovery arrangement in their deployed conditions.