Resume an Interrupted Batch
In one line
A checkpoint is not a percentage shown on the screen but a promise that states which part of the approved list has been committed together with the business change and the audit.
Why this was needed
The alien dessert festival was canceled because of a downpour. The customer asks you to cancel 40 unpaid orders. In the previous lesson, you built a change that either fully succeeds or fully rolls back. This time, the owner approved it with "It is fine to commit 10 at a time, and if a problem occurs at the back, keep the 10 that are already done." That one sentence changes the transaction boundary. It was not split arbitrarily by a developer for performance.
If you lock for a long time all at once, it becomes hard for other owners to process orders. Conversely, if you commit row by row, then even if a problem arises at the fifth row of a chunk, the earlier four rows cannot be rolled back. The right size is not decided by DB speed alone. You first ask whether the customer can understand a partial completion, and whether you can give a consistent explanation wherever it stops. The 10 in this lab is a teaching choice and not a recommended value for every production system.
How it works
1. Separate the approved list from the execution result
When registering, you store the job_id, the tenant, each order's id, revision, and qty, and the chunk_size. The approved list is normalized in ID order and does not change during execution. Re-registering with the same job ID and only the order of the list changed is the same request. Attaching a different customer, quantity, or chunk size to the same ID is a conflict. If you quietly overwrite, the old progress ends up pointing at the new list.
What if you build the list by searching the current pending orders again during the run? If a new order comes in after the first chunk finishes, an order that did not exist at first can get mixed into the cancellation targets. If an order is deleted, the meaning of OFFSET changes too. So here we do not use the Nth row of a WHERE result but the next_index of the frozen approved array. Even if the data changes from 40 rows to 39, you do not rewrite the approval itself to 39.
2. Lock the job row and pick the next chunk
If two workers read next_index=10 at the same time, both can grab the second chunk. If you read the corresponding row of jobs with SELECT FOR UPDATE and own it until the transaction ends, you can serialize the choice of the next section for the same job. PostgreSQL's row lock is released when the transaction ends, and it is not a global lock that blocks all ordinary reads. See the official row locking documentation.
In this design, two workers do not process different chunks of the same job at the same time. The two workers are there to check taking over after a failure and preventing duplicate selection. If you need greater parallel throughput, you need a separate design with per-chunk leases, expiry, and ownership generations. Do not explain that the current code has such guarantees.
3. Bind three records inside one chunk
If the chunk is orders 11–20, the changes below are one transaction.
| What is stored | What is confirmed | Problem if committed separately |
|---|---|---|
| orders | Change the approved-version pending to cancelled and raise the version | Only the orders change, and the resume position stays as it was |
| job_audit | Sequence number, ID, before and after versions, quantity | There is only an audit, and the actual orders are unchanged |
| jobs.next_index | 20, the start of the next section | Orders that were not executed are mistaken for completed |
You put the customer, ID, version, quantity, and state all in the UPDATE condition. RETURNING gives you the new values of the rows that actually changed. SQL itself does not raise an error when no row matches, so the program has to turn that into a Conflict and roll back the whole chunk. Read together the return value and the affected row count in the UPDATE official explanation.
Low-level Python functions also use transactions, but when they are called inside the outer run_chunk, they must not commit the whole thing early. psycopg's nested transaction context works with SAVEPOINT. This lesson uses the approach of explicitly opening the outer transaction boundary on an autocommit=True connection. Check against the psycopg transaction guide where the outermost context is in your own code.
4. Doubt the progress before resuming
You must not trust everything just because next_index is 20. The audit's sequence numbers, IDs, before and after versions, and quantities must exactly match the first 20 of the approved array. The fact that there are 20 rows alone does not tell you whether another ID has slipped in. This lab rejects progress with no audit, progress that points into the middle of a chunk, and progress that goes past the list length. It does not "repair" a damaged record automatically by changing more orders.
If, after the first chunk is committed, order 15 of the second chunk is modified by another owner, you roll back everything including the changes to orders 11–14 of the second chunk. The record of the first chunk and the later change to order 15 are kept. Reading the new revision and recreating the approval on the spot is not a retry but a new business decision. You have to stop, explain the remaining scope, and get a new approval.
What it looks like in the field
What is returned if you call again after losing the response
Suppose the chunk commit succeeded but the client terminated before receiving the result. If you resume the same job, it processes the next chunk instead of replaying the answer of the chunk that just finished. This is because the contract of this API is not "request to run chunk N" but "advance the next incomplete chunk of this job". So you must not add up only the returned processed and use it as evidence of completion of the whole job. You read the whole history from the DB's audit and checkpoint.
max_chunks is the number of chunk calls that one drain call will attempt. It is not the same as each call's SQL time limit or the total elapsed time. If another worker has finished most of it, my call can also receive a completion response with an empty processed ID list. On a normal completion it stops immediately, and it does not unconditionally retry a conflict or a connection error. Do not forget the error classification you learned in the earlier lesson.
Explain completion and the current state to the customer separately
Even if an order completed in the past is modified or deleted again later, the fact of its completion at that time does not disappear. The report splits the approved IDs, the committed IDs, and the unprocessed IDs, and splits the current state of the committed IDs into matching, drifted, and missing. You must not remove a missing just because you found a different order with the same quantity. If you read the progress and the current rows in a single SELECT, you can reconcile them under Read Committed's per-statement snapshot. See the isolation levels official documentation.
An FDE has to turn the customer's business conditions into implementation invariants and, when something fails, explain up to what was committed. The Palantir FDE job posting that we checked emphasizes understanding customer problems and implementing real solutions. This fictional case is the author's design for practicing that skill, and it does not mean that the posting requires PostgreSQL or this implementation.
What you will do in the next lab
You start with a 40-row approval and 10-row chunks and make it work for other customers, non-contiguous IDs, and a short last chunk. You terminate a real client at four points — after the order change, after the audit, after the checkpoint, and after the commit — and check the records with a new connection. Two processes continue the same job, and you use independent SQL to reconcile whether each exact approved ID was processed only once.
State the premises clearly too. Normal order writes increment the revision, and the committed approval and audit are immutable. A permission design that blocks even malicious direct modification by the lab DB account is separate. Because only the client is terminated while the server stays alive, this does not verify server power failure durability, external payment refunds, or even unlimited throughput.