LEARN. PRACTICE. GROW.Define the result before the worker
The laboratory creates source_rows, jobs and export_rows in a temporary PostgreSQL cluster. The first table represents fictional mutable source data; the third retains selected export values. Keeping only ids and rereading the source during file production is insufficient because it can mix values from different moments. The teaching contract associates job_id with id and units pairs. It also defines building, ready, running and complete. Those names are useful only when tied to verifiable conditions. Here ready means materialization committed, while complete includes a reference to a selected candidate and its hash.
Do not expose partial materialization
One session begins a transaction, creates a building job and copies two rows. Before COMMIT, the second session cannot see that job. The script then performs ROLLBACK and confirms removal of both the job and partial rows. This demonstrates the boundary of that local transaction. No process was killed, no power failed and no damaged database was recovered. A real implementation may separate job creation and materialization differently; if it does, the team needs a policy for incomplete jobs. An early completion notification would not automatically be repaired by a later rollback.
Retain values in one view
The second path uses REPEATABLE READ. Its first copy retains ids 1 and 2. Another session changes id 3 units to 999 and adds id 4. The next copy, inside the same transaction, still retains id 3 with 300 and excludes id 4. The job becomes ready before COMMIT. Afterwards, another session observes three materialized rows although the source already has four rows and different values. The export table allows file production without keeping the source transaction open throughout delivery. However, the script enforces no immutability permissions on that table; operational retention and protection guarantees need their own configuration and tests.
Distinguish a candidate from an accepted result
The coordinator writes two local files, one per job generation, using different names and exclusive creation. Both contain the same JSON representation of materialized values. Before updating jobs, artifact remains empty: bytes on disk do not mean a result was accepted. This ordering exposes an orphan candidate without changing the completion decision. The script implements no distributed transaction between database and filesystem. It also performs no object-storage upload or HTTP delivery. The real application must define when it exposes the reference to consumers and how it handles a written file without confirmed publication.
Verify the reference and bytes
After current-generation publication, the teaching consumer reads state, generation, name and digest from the database and computes SHA 256 over the bytes. A controlled file change makes that comparison fail even though jobs remains complete. The script restores only its own fixture to continue testing; this is not advice to repair a real artifact from untrusted data. A recorded hash supports comparison with expected content but does not by itself identify an authorized producer. Manifest, file and permission protection remains necessary. The laboratory includes neither digital signing nor immutable storage.
Prepare recovery and retention
Teaching cleanup queries complete references and deletes only the unreferenced candidate, without concurrency. The accepted file remains. In a later group, the script deliberately removes that file and confirms that database state does not change: complete does not guarantee future availability. A runbook should distinguish absence, hash mismatch, incomplete materialization and orphan candidates. For each, define diagnosis, reconstruction possibility, authorization and consumer impact. Retention and orphan collection must account for work still in progress. This script tests neither concurrent garbage collection, an actual storage outage nor recovery after power loss.
python3 content/labs/design-export/run.py --postgres-prefix /path/to/postgresql-18.6 --output /tmp/dr-export-new.json
# Use a fresh output path; owned cluster and files are temporary.An export retains three rows with values 100,200 and 300 although the source changes the third value to 999 and gains another row.
Common pitfalls
Treating file existence as publication, copying pages from different views, trusting database state alone or confusing rollback with crash recovery.
Related topics: Pagination and snapshots · Local transactions
An export has data, state and an artifact. Recovery must relate all three and identify boundaries that are not atomic.
Reference: Transaction isolation · System design patterns; PostgreSQL18 scoped examples; primary guidance consulted 2026-09-30