The Festival Cancel Button and Orders Already Changed

Goal

After you got approval to cancel festival orders, another owner changed the data. Cancel only the approved customer, ID, version, and quantity, preserve everything when a conflict occurs midway, and when a response is lost, confirm the facts from the committed record.

Why it matters

Data keeps moving even while you read the approval document. Even if the number "two rows changed" is right, it is a failure if you canceled a different order. You implement the change condition, the outer transaction, the receipt, and the current-state reconciliation separately, and verify them with two real DB connections and a client termination. You need the basics of PostgreSQL, SQL UPDATE, and Python exception handling.

The expected time is 100 minutes. Before it expires, press +time to extend the session. Files disappear after the session ends. Keep the code you need somewhere else.

Environment and data contract

The deliverable is /root/revision/worker.py. Python 3, psycopg 3.2.3, and PostgreSQL 16 are prepared in the image, so no internet installation is needed. The container runs as the postgres user, and the image is prepared so that it can write to /root. Do not switch users or add capabilities.

The checker creates a separate temporary schema and the three tables below inside the lab labdb, and cleans up only the schema it created. Do not directly change the global public tables or the original lab data. Your submitted functions use the search_path of the con they are given as it is, and do not hardcode the schema, the DSN, or order values.

CREATE TABLE orders(id integer PRIMARY KEY, tenant text NOT NULL,
 qty integer NOT NULL CHECK(qty BETWEEN 1 AND 1000),
 state text NOT NULL CHECK(state IN ('pending','paid','cancelled')),
 revision integer NOT NULL CHECK(revision>=0));
CREATE TABLE changes(change_id text PRIMARY KEY, tenant text NOT NULL, targets jsonb NOT NULL);
CREATE TABLE audit(change_id text REFERENCES changes(change_id), id integer NOT NULL,
 previous_revision integer NOT NULL, new_revision integer NOT NULL, qty integer NOT NULL,
 PRIMARY KEY(change_id,id));

The identifiers tenant and change_id are exact str values of 1–64 ASCII letters, digits, underscores, and hyphens. Format and range errors are rejected with ValueError before any write. SQL values are passed as parameters, not assembled as strings. A normal change to an order increments the revision, and changes and audit are a contract of not being modified after they are confirmed. This is not a permission system that controls even the writes of other programs that ignore this rule.

The con lent from outside is autocommit=True and Read Committed, and there is no open transaction at the start of a call. Your functions do not close the borrowed connection and leave no open transaction after success or failure. When internal functions call each other in a nested way, they preserve the outer transaction. Only change_file opens a new connection of its own. DB connection errors and unknown exceptions are propagated, not hidden as success.

Steps

1. Normalize the approval targets — in worker.py, implement a Conflict that subclasses Exception and validate_targets(targets). targets is an exact list of 1–16 items. Each item is an exact dict that has only id, revision, and qty. id is an int 1–2147483647, revision is an int 0–2147483646, and qty is an int 1–1000, and all of them reject bool. Duplicate IDs and format or range errors are ValueError. Return a new list in ascending ID order with new dicts, and do not change the input.
2. Read the current state by customer and explicit IDs — preview(con,tenant,ids) takes the identifier tenant described above and ids, an exact list. ids must contain 1–16 distinct exact int IDs in 1–2147483647, and an error is ValueError. In a single SELECT on orders, read the rows matching that customer, the explicit IDs, and the pending state, and return a list of id, revision, and qty dicts in ID order. If even one target is missing, or belongs to another customer or another state, it is a Conflict. The returned data also follows the contract of validate_targets, and it does not change the DB.
3. Cancel only one row of the approved version — cancel_one(con,tenant,target) validates the identifiers and the single approval item, and in the UPDATE it compares id, tenant, revision, qty, and pending at the same time. If they match, it changes state=cancelled and revision=revision+1 and returns an id, revision, qty, and state dict. If no row is returned, it is a Conflict, and it does not change any other row. A standalone call completes in one transaction, and inside the batch function below it must not commit the outer transaction early.
4. If there is a conflict midway, roll back the earlier changes too — cancel_many(con,tenant,targets) validates all inputs first, cancels each target in ID order, and returns a list of the cancel_one results. It handles the whole thing in one outer transaction, and if even one fails, it rolls back everything including the rows it changed earlier and then propagates the original error. This function does not create an audit or a receipt. It preserves the input and the control group.
5. Commit the approval receipt, the change, and the audit together — audited_cancel(con,change_id,tenant,targets,fault=None) validates the two identifiers and the approval set. In one transaction, it inserts the change_id, the tenant, and the normalized targets into changes, cancels the whole batch, and then calls fault('after-update'). It inserts each target's id, original revision, new revision=original+1, and qty as audit rows with the same change_id, calls fault('after-audit'), and after the whole COMMIT calls fault('after-commit') and returns True. If the existing change_id has the same tenant and normalized targets, it returns False with no change, and different contents are a Conflict. It does not call fault on a duplicate return. A concurrent identical request is also applied only once. It calls fault only when one is given; an error before the commit is a full rollback, and an error after the commit means the committed state is preserved and the original error is propagated.
6. Look up the approval contents confirmed at that time — inspect_change(con,change_id) validates the identifier and returns None if the receipt is not in changes, and otherwise returns a change_id, tenant, and targets dict. targets is the content at the approval time as stored, and later changes to orders or modifications of the returned value do not change the stored receipt. This function does not read the current orders state overwritten with the approval contents.
7. Check the same targets instead of the same count — reconcile(con,change_id) is a Conflict if there is no receipt. It looks up the receipt's IDs in a single SELECT on the current orders and returns an ID list for three keys, matching, drifted, and missing. If a row does not exist, it is missing; if the tenant is the customer of that time and revision=approved+1, qty=the approved quantity, and state=cancelled all match, it is matching; everything else is drifted. Each list is in ID order and does not include IDs that are not in the receipt. It only reads and does not modify current rows, the audit, or the receipt.
8. Resume a change whose response was lost, with the same ID — change_file(dsn,change_id,tenant,targets,fault=None) opens its own connection with psycopg.connect(dsn,autocommit=True,connect_timeout=2), returns the result of audited_cancel, and closes the connection on both success and failure. The DSN is a trusted, one-time local DB connection setting passed by the checker. The final check reconnects after a real client process is killed before and after the commit, sends concurrent requests for the same change, and re-evaluates the condition after a real row lock wait. It does not terminate the DB server itself.

Notes

• Self-diagnosis: python3 -B /opt/lab/fixtures/revision/check.py 8 /root/revision/worker.py. If you change 8 to the current step, it checks up to that step. The limit for one grading is 30 seconds, and it does not modify the submitted file.
• Apply only the explicit targets and preserve the control group. Do not automatically correct the version of a failed approval to the current value. A receipt that is None and a row that is currently missing are different states.
• This lab assumes an authorized owner calls it against a local test DB. Do not put in real customer information or an external DB. Login, per-customer permissions, and the production audit retention policy are separate.
• In the existing DB image, fsync and full_page_writes are turned off. This verifies the termination of a client process attached to a live server, and it is not a guarantee about a server power cut, disk corruption, or production backup restore.

Normalize the approval targets

In worker.py, implement a Conflict that subclasses Exception and validate_targets(targets). targets is an exact list of 1–16 items. Each item is an exact dict that has only id, revision, and qty. id is an int 1–2147483647, revision is an int 0–2147483646, and qty is an int 1–1000, and all of them reject bool. Duplicate IDs and format or range errors are ValueError. Return a new list in ascending ID order with new dicts, and do not change the input.

Even the inner dicts of the original list and the returned list must be separate. Do not treat an empty approval as a success with no effect.

Read the current state by customer and explicit IDs

preview(con,tenant,ids) takes the identifier tenant described above and ids, an exact list. ids must contain 1–16 distinct exact int IDs in 1–2147483647, and an error is ValueError. In a single SELECT on orders, read the rows matching that customer, the explicit IDs, and the pending state, and return a list of id, revision, and qty dicts in ID order. If even one target is missing, or belongs to another customer or another state, it is a Conflict. The returned data also follows the contract of validate_targets, and it does not change the DB.

Compare the explicit set of targets, not the total pending count. Do not slip in a new order that did not exist at approval time.

Cancel only one row of the approved version

cancel_one(con,tenant,target) validates the identifiers and the single approval item, and in the UPDATE it compares id, tenant, revision, qty, and pending at the same time. If they match, it changes state=cancelled and revision=revision+1 and returns an id, revision, qty, and state dict. If no row is returned, it is a Conflict, and it does not change any other row. A standalone call completes in one transaction, and inside the batch function below it must not commit the outer transaction early.

A SELECT in front of an unconditional UPDATE alone does not prevent the race. Use RETURNING to confirm the row that was actually changed.

If there is a conflict midway, roll back the earlier changes too

cancel_many(con,tenant,targets) validates all inputs first, cancels each target in ID order, and returns a list of the cancel_one results. It handles the whole thing in one outer transaction, and if even one fails, it rolls back everything including the rows it changed earlier and then propagates the original error. This function does not create an audit or a receipt. It preserves the input and the control group.

The success of a single-row function is not the commit of the whole batch. Check the relationship between the nested transaction context and the savepoint.

Commit the approval receipt, the change, and the audit together

audited_cancel(con,change_id,tenant,targets,fault=None) validates the two identifiers and the approval set. In one transaction, it inserts the change_id, the tenant, and the normalized targets into changes, cancels the whole batch, and then calls fault('after-update'). It inserts each target's id, original revision, new revision=original+1, and qty as audit rows with the same change_id, calls fault('after-audit'), and after the whole COMMIT calls fault('after-commit') and returns True. If the existing change_id has the same tenant and normalized targets, it returns False with no change, and different contents are a Conflict. It does not call fault on a duplicate return. A concurrent identical request is also applied only once. It calls fault only when one is given; an error before the commit is a full rollback, and an error after the commit means the committed state is preserved and the original error is propagated.

Coordinate concurrent requests with the receipt's unique key, but also compare the contents for the colliding key. If you commit only the receipt, it becomes a success record with no actual change.

Look up the approval contents confirmed at that time

inspect_change(con,change_id) validates the identifier and returns None if the receipt is not in changes, and otherwise returns a change_id, tenant, and targets dict. targets is the content at the approval time as stored, and later changes to orders or modifications of the returned value do not change the stored receipt. This function does not read the current orders state overwritten with the approval contents.

The current state and the confirmed record of the past are answers to different questions. Do not mix the business state into the receipt lookup.

Check the same targets instead of the same count

reconcile(con,change_id) is a Conflict if there is no receipt. It looks up the receipt's IDs in a single SELECT on the current orders and returns an ID list for three keys, matching, drifted, and missing. If a row does not exist, it is missing; if the tenant is the customer of that time and revision=approved+1, qty=the approved quantity, and state=cancelled all match, it is matching; everything else is drifted. Each list is in ID order and does not include IDs that are not in the receipt. It only reads and does not modify current rows, the audit, or the receipt.

Even with the same quantity, a higher revision means a later change. Do not offset by count the case where the original ID disappeared and a new ID appeared.

Resume a change whose response was lost, with the same ID

change_file(dsn,change_id,tenant,targets,fault=None) opens its own connection with psycopg.connect(dsn,autocommit=True,connect_timeout=2), returns the result of audited_cancel, and closes the connection on both success and failure. The DSN is a trusted, one-time local DB connection setting passed by the checker. The final check reconnects after a real client process is killed before and after the commit, sends concurrent requests for the same change, and re-evaluates the condition after a real row lock wait. It does not terminate the DB server itself.

When the response is lost after the commit, use the receipt to decide on a retry. Distinguish a function that borrows a connection from a function that owns one.