The Reopened Festival and a Risky Undo

Goal

The festival that was canceled is going ahead again. You restore only the orders whose state right after the cancellation has been maintained, limit the waiting, and, even if the response is lost, resume with the compensation receipt.

Why it matters

If you overwrite with a whole backup, you can erase other owners' changes made after the cancellation. A compensation is also a change that has its own approval and record. First study the earlier approved-version lab and Python exceptions and SQL transactions. The expected time is 110 minutes. Before it expires, extend with +time. Files disappear when the session ends. Keep the code you need separately.

Environment and common contract

The deliverable is /root/compensation/worker.py. PostgreSQL 16, psycopg 3.2.3, and Python 3 are in the image, and no internet installation is needed. You can write to /root as the postgres user, and no user switch or extra capability is needed.

The checker prepares the tables below and the fictional original change in a separate temporary schema of the local labdb, and cleans up only the schema it created. Your submitted functions use the search_path of the connection they are given. Do not hardcode the schema, order IDs, customers, or the DSN, and do not change the original data in public. Pass SQL values as parameters.

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));
CREATE TABLE undo_records(undo_id text PRIMARY KEY,
 original_id text NOT NULL UNIQUE REFERENCES changes(change_id),reason text NOT NULL,
 tenant text NOT NULL,targets jsonb NOT NULL);
CREATE TABLE undo_audit(undo_id text REFERENCES undo_records(undo_id),id integer NOT NULL,
 previous_revision integer NOT NULL,new_revision integer NOT NULL,qty integer NOT NULL,
 PRIMARY KEY(undo_id,id));

The identifiers undo_id, original_id, and tenant are exact str values of 1–64 ASCII letters, digits, underscores, and hyphens. reason is an exact str of 1–200 characters and does not allow leading or trailing whitespace or code points 0–31 and 127. The request is not corrected automatically. A target is an exact dict that has only id, revision, and qty, where id is an int 1–2147483647, the expected revision is an int 0–2147483646, and qty is an int 1–1000. bool is rejected everywhere. targets is an exact list of 1–16 items with distinct IDs, normalized into new lists and dicts in ID order. plan is an exact dict that has only original_id, tenant, and targets. An input error passed in directly is a ValueError before any write.

lock_ms is an exact int 50–500, and statement_ms is an exact int that is greater than lock_ms and at most 2000. The limits apply to each lock attempt and each SQL statement, and they are not a guarantee of the elapsed time of the whole batch. Inserting the compensation receipt can also wait, so compensate applies the budget of its own transaction from its first DB operation.

The borrowed con is autocommit=True and Read Committed, and there is no transaction at the start of an external call. No function closes the borrowed connection, and after success or failure none leaves an open transaction or a settings change. The internal nested calls of restore_one and restore_batch preserve the outer transaction. Do not wrap compensate in another outer transaction. Only undo_file owns a new connection. The contract is that records are immutable once committed and that normal order writes increment the revision. It does not control the write permissions of production programs that do not keep this premise.

Steps

1. Make the compensation request explicit — implement a Conflict that subclasses Exception and request(undo_id,original_id,reason). Validate the identifier and reason contract below and return a new dict with three keys. A format error is a ValueError, and you do not quietly fix the reason or the IDs.
2. Reconcile the original receipt and the audit — load_plan(con,original_id) reads the original changes and audit and returns an original_id, tenant, and targets dict. targets is in ID order and holds the expected values right after the cancellation, which are the original approval revision plus 1. A missing original record, a mismatch of audit ID, before and after versions, or quantity, an invalid record, or exceeding the integer upper bound after the compensation is a Conflict. The original approval revision allows only 0–2147483645. It does not modify the original record or orders, and modifying the returned value does not affect the original record either.
3. Restore only when it is in the state right after the cancellation — restore_one(con,tenant,target) validates the input and puts id, tenant, expected revision, qty, and the cancelled state in the UPDATE condition. If they match, it changes it to pending, raises the revision by 1, and returns an id, revision, qty, and state dict. If it is missing or has changed, it is a Conflict. It handles things in its own transaction, but in an internal call it does not commit the outer transaction early. It writes neither an audit nor a receipt.
4. Limit the waiting and protect the whole batch — restore_batch(con,plan,lock_ms=100,statement_ms=800) validates the plan and budget contracts below before writing. It applies the limits inside an outer transaction, restores everything in ID order, and returns the list of restore_one results. On any error it rolls back everything including earlier rows and propagates the original error. After success or failure it preserves the borrowed connection's lock_timeout and statement_timeout at their original values. It does not change the original record or the control group.
5. Commit the compensation record and the business change together — compensate(con,undo_id,original_id,reason,lock_ms=100,statement_ms=800,fault=None) applies the limits in one transaction after validating the request and the budget, and reads the original compensation plan. It inserts the compensation ID, original ID, reason, customer, and expected targets into undo_records, calls fault(after-restore) after the batch restore, inserts each target's revision right after the cancellation, new revision, and qty into undo_audit and then calls fault(after-audit), calls fault(after-commit) after the real COMMIT, and returns True. If the ID, original ID, reason, and plan are the same, it returns False with no change and does not call the hook either. Compensating the same original change again with different contents under the same compensation ID, or under a different compensation ID, is a Conflict. It calls the hook only when one exists. An error before the commit is a full rollback, and an error after it means the committed state is preserved and the original error is propagated. It does not modify the original changes and audit.
6. Read the time of the compensation and the present separately — inspect_undo(con,undo_id) returns None if it does not exist, and otherwise an undo_id, original_id, reason, tenant, and targets dict. targets is a copy of the expected values at the time of the compensation. reconcile_undo(con,undo_id) is a Conflict if there is no receipt, and otherwise looks up the current orders of those IDs in a single SELECT and returns ID-ordered lists for matching, drifted, and missing. If the customer, qty, pending, and revision=the receipt's expectation+1 all match, it is matching; if the ID is missing, it is missing; everything else is drifted. Both functions only read.
7. Resume a compensation whose response was lost — undo_file(dsn,undo_id,original_id,reason,lock_ms=100,statement_ms=800,fault=None) owns a connection with psycopg.connect(dsn,autocommit=True,connect_timeout=2), calls compensate, and returns the same result. It closes the connection on both success and failure and does not hide errors. The check covers a real client termination and a race between two processes with the same or different compensation IDs.
8. Retry only a lock error a fixed number of times — retry_undo(action,attempts,pause) allows only an exact int attempts of 1–3, and an error is a ValueError before the first call. It calls action() immediately and returns normal results, including True and False, as they are. It retries only psycopg.errors.LockNotAvailable, and the last failure propagates the same error. It calls pause(the attempt number that just failed) only when there are attempts left. The total is attempts calls including the first, and pause is called at most attempts-1 times. A statement cancellation, a Conflict, an input or connection error, and a pause error are propagated with no extra call. The action in the real check calls undo_file with the same IDs.

Notes

• Self-diagnosis: python3 -B /opt/lab/fixtures/compensation/check.py 8 /root/compensation/worker.py. If you change 8 to the current step, it checks up to that step. The limit for one grading is 30 seconds.
• The DSN you are given is a trusted, one-time local DB setting. Do not put in real customer data or an external DB. Authentication, per-customer permissions, and external payment and shipment cancellation are separate.
• On the existing lab image with fsync and full_page_writes turned off, the DB server is kept alive and only the client is terminated. It does not verify a server power failure, disk corruption, or production backup durability.

Make the compensation request explicit

Implement a Conflict that subclasses Exception and request(undo_id,original_id,reason). Validate the identifier and reason contract below and return a new dict with three keys. A format error is a ValueError, and you do not quietly fix the reason or the IDs.

Check the ASCII rule for the identifiers, and the length and control characters for the reason, separately.

Reconcile the original receipt and the audit

load_plan(con,original_id) reads the original changes and audit and returns an original_id, tenant, and targets dict. targets is in ID order and holds the expected values right after the cancellation, which are the original approval revision plus 1. A missing original record, a mismatch of audit ID, before and after versions, or quantity, an invalid record, or exceeding the integer upper bound after the compensation is a Conflict. The original approval revision allows only 0–2147483645. It does not modify the original record or orders, and modifying the returned value does not affect the original record either.

Do not guess which cancellation something is the result of just from the observation that it is currently cancelled. Compare the whole original audit list with the normalized approval.

Restore only when it is in the state right after the cancellation

restore_one(con,tenant,target) validates the input and puts id, tenant, expected revision, qty, and the cancelled state in the UPDATE condition. If they match, it changes it to pending, raises the revision by 1, and returns an id, revision, qty, and state dict. If it is missing or has changed, it is a Conflict. It handles things in its own transaction, but in an internal call it does not commit the outer transaction early. It writes neither an audit nor a receipt.

If you lower it to a past version, an old approval looks valid again. Confirm the new version with RETURNING.

Limit the waiting and protect the whole batch

restore_batch(con,plan,lock_ms=100,statement_ms=800) validates the plan and budget contracts below before writing. It applies the limits inside an outer transaction, restores everything in ID order, and returns the list of restore_one results. On any error it rolls back everything including earlier rows and propagates the original error. After success or failure it preserves the borrowed connection's lock_timeout and statement_timeout at their original values. It does not change the original record or the control group.

Use set_config at transaction scope. The lock limit, the statement limit, and the elapsed time of the whole batch are not the same thing.

Commit the compensation record and the business change together

compensate(con,undo_id,original_id,reason,lock_ms=100,statement_ms=800,fault=None) applies the limits in one transaction after validating the request and the budget, and reads the original compensation plan. It inserts the compensation ID, original ID, reason, customer, and expected targets into undo_records, calls fault(after-restore) after the batch restore, inserts each target's revision right after the cancellation, new revision, and qty into undo_audit and then calls fault(after-audit), calls fault(after-commit) after the real COMMIT, and returns True. If the ID, original ID, reason, and plan are the same, it returns False with no change and does not call the hook either. Compensating the same original change again with different contents under the same compensation ID, or under a different compensation ID, is a Conflict. It calls the hook only when one exists. An error before the commit is a full rollback, and an error after it means the committed state is preserved and the original error is propagated. It does not modify the original changes and audit.

A compensation where only the receipt remains, and a compensation that erased the original record, are failures. Distinguish the meaning of the two unique key collisions.

Read the time of the compensation and the present separately

inspect_undo(con,undo_id) returns None if it does not exist, and otherwise an undo_id, original_id, reason, tenant, and targets dict. targets is a copy of the expected values at the time of the compensation. reconcile_undo(con,undo_id) is a Conflict if there is no receipt, and otherwise looks up the current orders of those IDs in a single SELECT and returns ID-ordered lists for matching, drifted, and missing. If the customer, qty, pending, and revision=the receipt's expectation+1 all match, it is matching; if the ID is missing, it is missing; everything else is drifted. Both functions only read.

Do not fill the absence of the original ID with the same values of a new order. Nor do you fix the past receipt because of a present change.

Resume a compensation whose response was lost

undo_file(dsn,undo_id,original_id,reason,lock_ms=100,statement_ms=800,fault=None) owns a connection with psycopg.connect(dsn,autocommit=True,connect_timeout=2), calls compensate, and returns the same result. It closes the connection on both success and failure and does not hide errors. The check covers a real client termination and a race between two processes with the same or different compensation IDs.

A compensation the DB committed and the response the caller received are different. For a retry, use the same compensation number and contents.

Retry only a lock error a fixed number of times

retry_undo(action,attempts,pause) allows only an exact int attempts of 1–3, and an error is a ValueError before the first call. It calls action() immediately and returns normal results, including True and False, as they are. It retries only psycopg.errors.LockNotAvailable, and the last failure propagates the same error. It calls pause(the attempt number that just failed) only when there are attempts left. The total is attempts calls including the first, and pause is called at most attempts-1 times. A statement cancellation, a Conflict, an input or connection error, and a pause error are propagated with no extra call. The action in the real check calls undo_file with the same IDs.

It is a contract on the number of attempts, not a limit on the total elapsed time. If you see a different version after the lock is released, do not automatically create a new approval.