LEARN. PRACTICE. GROW.Acquire work without waiting on the same row
Two sessions begin transactions. The first locks ready job 1 using FOR UPDATE SKIP LOCKED; the second obtains job 2. The script confirms different selected rows before ending those transactions. Selection and transition of job 1 to running occur in the first session transaction. This avoids a gap between selection and recording ownership on that local path. There is no broker or autonomous worker in a separate process: sessions follow coordinator-controlled ordering. The example supports discussion of queue-table acquisition, not universal fairness, ordering or consumer-distribution guarantees.
Interpret an empty acquisition
While the second session locks job 2, the first tries selecting that specific job with SKIP LOCKED and receives zero rows. The job exists and is ready in the relevant view, but this attempt cannot lock it. Concluding that the work population is finished would be incorrect. A polling routine needs to distinguish no eligible work from work unavailable at that instant and define bounded retry behavior. Overall queue count and job age help diagnose stalled progress. This experiment measures neither prolonged waiting, starvation nor many-consumer behavior; those properties remain untested.
Expire authority without killing execution
The first acquisition records owner A, epoch 1 and lease_until 10. A takeover attempt at logical time 9 changes zero rows. At logical time 10, its condition permits takeover, changes owner to B and increments epoch to 2. Session A can still execute SELECT afterwards. Losing authority therefore does not mean disappearing, terminating or cancelling ongoing work. Values 9 and 10 are script-chosen integers rather than measured clocks. No scheduler, periodic heartbeat, clock skew or process pause is exercised. In a real solution, define the time source and test delays and renewals without turning expiry alone into a guarantee of physical exclusivity.
Check the generation at the destination
The old heartbeat includes id, running state, owner A and epoch 1 in its predicate. After takeover, it returns zero rows and does not extend B lease. Publication likewise checks state, owner, epoch and logical lease validity. The old candidate remains on disk, but publication update is rejected. The current generation changes one row and stores the selected candidate name and digest. Protection exists because the database checks conditions when changing the record. An earlier check followed by an unconditional write would leave another gap. External services that do not check generation remain outside this guarantee; the script does not cancel their effects.
Reconcile repeated completion
After complete, repeating the same update conditioned on running returns zero rows. That does not automatically mean result failure or success of the new attempt. The script reads the record and confirms generation 2, the expected candidate and a matching hash. Reconciliation distinguishes previously applied completion from lost ownership, cancellation or incompatible state. Another group marks job 2 cancelled and confirms rejection of a transition conditioned on running. No running process is killed in that case. The contract must state what cancellation prevents, what may already have happened and how the consumer response explains observed state.
Deliver a failure matrix
For a fictional APS project, document uncommitted materialization, ready jobs, valid leases, takeover, orphan candidates, confirmed publication and unavailable artifacts. Connect each state to observations, permitted action, owner and reversal criteria. The sixteen local groups demonstrate concrete mechanisms but do not establish a complete distributed implementation. Plan process interruption, storage, identity, cleanup concurrency and HTTP communication exercises on an appropriate target. Include retention costs and business completion criteria. A diagram becomes useful to support when it explains where to obtain evidence and prevents stale or incomplete attempts from being presented as valid results.
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.Generation 2 takes over after logical boundary 10; the old session remains usable, but its heartbeat and publication update zero rows.
Common pitfalls
Lease expiry as process termination, empty SKIP LOCKED as an empty queue, zero updates as success or SQL protection as cancellation of external effects.
Related topics: Queues and concurrency · Export recovery
The generation identifies current authority. The publication destination must check it; expiry and worker names alone do not reject stale work.
Reference: SELECT and SKIP LOCKED · System design patterns; PostgreSQL18 scoped examples; primary guidance consulted 2026-09-30