Block with Constraints, Retry Only 40001

Goal

You will reproduce the scene where code that checks and then inserts creates a duplicate signup across two sessions, then clean up the existing duplicates and block them with UNIQUE and INSERT … ON CONFLICT. You will solve booking overlaps with an exclusion constraint (EXCLUDE) and seat swaps with a DEFERRABLE UNIQUE. You will guard rules that span several rows with a SERIALIZABLE retry loop using psycopg 3, and at the end organize where to put each invariant.

Why it matters

Rules that an application checks and then writes break in the gap between the check and the write, and that gap shows up only when load concentrates. A database constraint always holds regardless of the isolation level, and code that joins later cannot bypass it. Rules that cannot be written as constraints are left to SERIALIZABLE, but which errors the retry loop retries and which it passes upward decides correctness. So the grader does not look only at the files you wrote: it checks whether the constraints are in the catalog, inserts counterexample rows itself, and then rolls them back. It calls the retry loop against a separate database that deliberately raises errors.

Environment

PostgreSQL 16 is running inside the Pod. Connect with psql -h 127.0.0.1 -U lab -d labdb; the password is lab (export PGPASSWORD=lab). Keep all working files in /root/svccs/invariants/. Create all tables in the inv schema. Python is ready with python3 and psycopg 3.

Steps

1. Create and load /root/svccs/invariants/schema.sql. Start with DROP SCHEMA IF EXISTS inv CASCADE; CREATE SCHEMA inv;, create the "Tables" below, and insert the rows. In this step, do not add any constraints other than those written in the tables.
2. Create /root/svccs/invariants/signup_naive.sql. Inside a transaction, count, in inv.signups_naive, how many race@example.com there are (\gset), rest with pg_sleep(2), and insert only if it was 0. Run two sessions overlapping to create a duplicate, and write the row count for that email to /root/svccs/invariants/02-race.txt as rows <수> (the word rows followed by the count).
3. Clean up the existing duplicates in inv.members (keep the smallest id) and, under the name members_email_key, add UNIQUE (email) (not deferrable). In /root/svccs/invariants/signup.sql, write a signup as a single statement that uses the psql variable :'email' (without BEGIN/COMMIT) and ON CONFLICT (email) DO NOTHING RETURNING id. Run two sessions overlapping with race@example.com, and in /root/svccs/invariants/03-unique.txt write, one per line, returned_a, returned_b (the number of rows each session got back), and rows (the number of rows for that email).
4. In /root/svccs/invariants/touch.sql, write an upsert in a single statement. Insert it if it does not exist (visits 1), add 1 to the existing visits if it does, and either way return that row with RETURNING id, visits. Against the already existing kim@example.com, when you run signup.sql and touch.sql each (and roll back), write the number of rows you got back to /root/svccs/invariants/04-upsert.txt as do_nothing_rows <수> and do_update_rows <수> (each followed by the count).
5. Create the btree_gist extension, and count the pairs of existing bookings in the same room whose [) ranges overlap. In each overlapping pair, change the one with the larger id to status = 'cancelled' (do not delete), and create an exclusion constraint named bookings_no_overlap that applies only to active (status = 'active') bookings: the same room and overlapping tstzrange(starts_at, ends_at, '[)') must not be allowed. In /root/svccs/invariants/05-exclude.txt, write overlaps_found <센 쌍 수> (the number of pairs you counted), cancelled <취소 상태 행 수> (the number of rows in cancelled state), and sqlstate <겹치는 예약을 넣었을 때 받은 코드> (the code you received when inserting an overlapping booking).
6. In /root/svccs/invariants/swap.sql, write two UPDATEs that, in one transaction, change kim's seat to 14 and lee's seat to 12. Write the SQLSTATE you get back when running it against the current seats_seat_no_key to /root/svccs/invariants/06-swap.txt as immediate_error <코드> (followed by the code), then recreate the constraint of the same name as DEFERRABLE INITIALLY DEFERRED and run swap.sql to actually swap.
7. In /root/svccs/invariants/retry.py, create withdraw(conninfo, wallet_id, amount, max_attempts=8). The rule is exactly the "Withdrawal rule" below. In if __name__ == "__main__": of the same file, reset the two kim family wallets to 100, have 6 threads withdraw 80 at a time alternately from wallets 1 and 2 concurrently (starting together), and then in /root/svccs/invariants/07-retry.json write workers, committed, rejected, retries (total attempts − number of threads), and final_sum (the total of the kim family).
8. In /root/svccs/invariants/map.json, for each of the six invariants below, write {"where": "constraint" | "isolation" | "application", "how": "쓴 도구 한 줄"} (where "how" is a one-line description of the tool you used). The grader also checks whether the ones you wrote as constraint are actually in place in the database now.

Tables

inv.members        id bigserial PK, email text NOT NULL, visits int NOT NULL DEFAULT 1
                   행: kim@example.com, lee@example.com, legacy@example.com, legacy@example.com
inv.signups_naive  id bigserial PK, email text NOT NULL                (행 없음, 끝까지 제약 없음)
inv.bookings       id bigserial PK, room text, starts_at timestamptz, ends_at timestamptz, who text,
                   status text NOT NULL DEFAULT 'active',
                   CONSTRAINT bookings_positive_length CHECK (starts_at < ends_at)
                   행: river 10:00~11:00 kim · river 11:00~12:00 lee · river 11:30~12:30 park
                       · hill 10:00~11:00 choi   (모두 2026-10-01, 시간대 +09)
inv.seats          id int PK, passenger text NOT NULL, seat_no int NOT NULL,
                   CONSTRAINT seats_seat_no_key UNIQUE (seat_no)
                   행: (1, kim, 12) (2, lee, 14) (3, park, 15) (4, choi, 16)
inv.wallets        id int PK, family text, owner text, balance int   (모두 NOT NULL)
                   행: (1, kim, kim-a, 100) (2, kim, kim-b, 100) (3, lee, lee-a, 50) (4, lee, lee-b, 50)

Withdrawal rule

연결은 conninfo 로 새로 연다. 격리 수준은 SERIALIZABLE.
한 트랜잭션: wallet_id 의 family 를 읽고 → 그 family 의 balance 합계를 읽고 →
  합계 < amount 면 아무것도 바꾸지 않고 {"status": "rejected", "attempts": n} 을 돌려준다
  아니면 UPDATE inv.wallets SET balance = balance - amount WHERE id = wallet_id 후 커밋하고
  {"status": "committed", "attempts": n} 을 돌려준다     (n = 이번 호출에서 시도한 횟수)
SQLSTATE 40001·40P01 이면 트랜잭션 전체를 처음부터 다시 한다(짧은 무작위 대기, 합계 1초 이내).
그 밖의 오류는 다시 하지 않고 그대로 올려보낸다. max_attempts 번 모두 실패하면 마지막 오류를 올려보낸다.

The six invariants (step 8)

member_email_unique       이메일 하나에 회원 하나
room_no_overlap           같은 방의 활성 예약 시간이 겹치지 않는다
seat_unique               좌석 하나에 승객 하나(맞바꾸는 동안은 잠깐 깨져도 된다)
booking_positive_length   예약은 끝이 시작보다 뒤다
family_sum_nonnegative    가족 지갑 합계가 음수가 되지 않는다(여러 행에 걸친 조건)
welcome_mail_once         가입 환영 메일(외부 메일 서비스 호출)은 한 번만 보낸다

Notes

• psql passes variables with -v email=값 (with the value after the equals sign) and you use them in the file as :'email'. If you give several like -c "BEGIN" -f 파일 -c "COMMIT" (with the file name in the middle), they run one after another in one session. To see the SQLSTATE in an error, give -v VERBOSITY=verbose.
• To overlap two sessions, start one in the background with &, run the other a moment later, and then wait.
• Common mistakes: keeping only an application check like WHERE NOT EXISTS without UNIQUE, taking ranges as [] and blocking even bookings that merely touch, working around a swap with a temporary number while leaving the constraint as it is, and a retry loop that retries even constraint violations with except Exception or reports failure as success.
• The outputs and the database disappear when the session ends. Keep them elsewhere if you need them.

Create tables without constraints

Create and load /root/svccs/invariants/schema.sql: members, signups_naive, bookings, seats, and wallets in the inv schema, exactly as in the "Tables" of the instructions.

If you start with DROP SCHEMA IF EXISTS inv CASCADE, you get the same state no matter how many times you reload. psql keeps running the next statement even after an error, so give -v ON_ERROR_STOP=1. Write times with the time zone, like '2026-10-01 10:00+09'.

Check-then-insert creates duplicates

Overlap two sessions with /root/svccs/invariants/signup_naive.sql to create a race@example.com duplicate in inv.signups_naive, and write the row count to /root/svccs/invariants/02-race.txt as rows .

Use the value counted with SELECT count(*) AS n … \gset as :n. Both sessions must count before the other commits so that both see 0: start one with & and run the other 0.5 seconds later. If you run them in turn, the second sees 1 and does not insert.

UNIQUE and ON CONFLICT DO NOTHING

Clean up the duplicates in inv.members and add members_email_key UNIQUE (email), then overlap two sessions with /root/svccs/invariants/signup.sql (one statement) and write returned_a, returned_b, and rows to /root/svccs/invariants/03-unique.txt. The grader runs signup.sql twice with a probe email and rolls it back.

A constraint also checks existing rows, so ALTER TABLE fails if the legacy duplicates remain. Delete the larger id of the same email with DELETE … USING. Of the two overlapping sessions, the later one waits for the earlier one's commit and then inserts nothing, and its RETURNING is empty too.

The difference between DO UPDATE and RETURNING

Write an upsert that raises visits as a single statement in /root/svccs/invariants/touch.sql, and write the number of rows that signup.sql and touch.sql return for an existing member to /root/svccs/invariants/04-upsert.txt. The grader runs touch.sql three times with a probe email and looks at the id and visits.

In the SET clause, the existing row is referred to by the table alias (AS m) and the row you tried to insert by EXCLUDED. EXCLUDED.visits is the default 1, so if you add to that, it is 2 no matter how many times you run it. The RETURNING of DO NOTHING does not return the skipped row.

Booking overlap with EXCLUDE

Create btree_gist, count and cancel the existing overlaps, create the bookings_no_overlap exclusion constraint that applies only to active bookings, and write overlaps_found, cancelled, and sqlstate to /root/svccs/invariants/05-exclude.txt. The grader inserts overlapping, adjacent, and cancelled bookings into a probe room and rolls them back.

Check overlap with tstzrange(starts_at, ends_at, '[)') && …. Under '[)', a booking that ends at 11:00 and one that starts at 11:00 do not overlap. If you take it as '[]', the constraint cannot even be added because of the kim and lee bookings. The partial constraint is EXCLUDE … WHERE (status = 'active').

Seat swap and DEFERRABLE

With /root/svccs/invariants/swap.sql, write the error you get from the immediately checked UNIQUE to /root/svccs/invariants/06-swap.txt as immediate_error, then recreate seats_seat_no_key as DEFERRABLE INITIALLY DEFERRED and swap to kim 14 and lee 12. The grader looks at the constraint attributes, tests the swap and a duplicate inside a transaction, and rolls back.

ALTER CONSTRAINT can alter only foreign keys, so use DROP CONSTRAINT and ADD CONSTRAINT together in the same ALTER TABLE. With immediate checking, the moment the first UPDATE finishes there are two 12s and you get 23505. Changing three times through a temporary number (0) would pass, but the constraint is still checked immediately.

A retry loop that retries only 40001

Create withdraw in /root/svccs/invariants/retry.py following the withdrawal rule, and write the result of withdrawing concurrently with 6 threads to /root/svccs/invariants/07-retry.json. The grader calls withdraw against a separate database while deliberately raising 40001, 40P01, and 23514.

Open with psycopg.connect(conninfo, autocommit=True), set conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE, and open a with conn.transaction(): block inside the loop. Branch on the exception's sqlstate: if you retry everything with except Exception, you repeat even constraint violations. The SELECT that reads the sum must also be inside the loop so that it sees fresh values when retrying.

Where to put each invariant

In /root/svccs/invariants/map.json, write where (constraint, isolation, application) and how for each of the six invariants. The grader checks whether the ones written as constraint are in place in the database now and actually reject counterexamples.

CHECK cannot look at rows other than the one being checked, so a sum over several rows cannot be written as a constraint. Even if a transaction is rolled back, an external call that has already gone out does not come back, so that is a rule the database cannot guard.