Check-Then-Write Breaks; Constraints Hold

In one line

If an application "checks and then writes" an invariant, it breaks in the gap between the check and the write. If the database holds it as a constraint, that gap does not exist. Leave to SERIALIZABLE only the multi-row rules that a constraint cannot express, and the retry loop that goes with it must retry only 40001.

Why this was needed

If you write "sign this email up if it does not exist" as a SELECT followed by an INSERT, two requests both see "does not exist" at the same time and both insert. A booking time-overlap check has the same shape. Isolation levels and write skew are covered by the transaction lab of the "Database Concepts" course, and row locks by the "PostgreSQL Advanced — Transactions, Indexes, JSONB, Partitions" course. This module looks at the next question: what do you need to know to hand the rule itself over to the database? The design that uses UNIQUE and ON CONFLICT for an idempotency key is covered by the "Production Backend API Capstone," so here we extend it to what exactly that statement returns and to rules that UNIQUE cannot express.

How it works

UNIQUE and ON CONFLICT. According to the INSERT documentation, ON CONFLICT DO NOTHING does nothing instead of inserting, and DO UPDATE guarantees that either an INSERT or an UPDATE happens atomically, even under high concurrency. The trap is RETURNING. The documentation says it returns only rows that were actually inserted or updated. A row skipped by DO NOTHING does not come out, so "an empty result = it already existed," and if you need the id of the existing row, you have to use DO UPDATE. In the SET clause, the existing row is referred to by the table name (alias), and the row you tried to insert by EXCLUDED. If you mix the two up and write EXCLUDED.visits + 1, the visit count does not grow and is the same value every time. The constraints documentation says that by default two NULLs are not considered equal, so even with a UNIQUE, rows containing NULL can be duplicated, and that you can change this with NULLS NOT DISTINCT.

Overlap with EXCLUDE. "Booking times of the same room do not overlap" is not equality but an overlap relation, so it cannot be written with UNIQUE. An exclusion constraint is a rule that, when two rows are compared with the specified operators, at least one comparison must be false or NULL, and adding one automatically creates an index of that kind. The range types documentation says that [ and ] are used for inclusive bounds and ( and ) for exclusive bounds, and that the two-argument constructor makes the canonical form [), which includes the lower bound and excludes the upper. Bookings of 10–11 and 11–12 do not overlap under [), but if you take them as [] they overlap at the single point 11. To use the = of a scalar such as a room number inside GiST, you need btree_gist, and only then can you bundle both into one constraint, EXCLUDE USING gist (room WITH =, 시간범위 WITH &&) (where the time range is the second column). In CREATE TABLE, adding WHERE (predicate) lets you apply the constraint to only part of the table, such as bookings that were not cancelled (internally a partial index is created).

Changes that break briefly in the middle of a statement use DEFERRABLE. The compatibility section of the same document says that PostgreSQL checks a non-deferrable UNIQUE immediately each time it inserts or updates a row, while the standard says to check at the end of the statement. This is why the first UPDATE that swaps the seats of two passengers immediately gets 23505. DEFERRABLE INITIALLY DEFERRED checks only at the end of the transaction. Only UNIQUE, PRIMARY KEY, EXCLUDE, and foreign keys accept it; NOT NULL and CHECK are not deferred. The ALTER CONSTRAINT of ALTER TABLE can currently alter only foreign keys, so a UNIQUE has to be dropped and created again. And a deferrable constraint cannot be the arbiter of ON CONFLICT: if you put it on the signup email, the upsert breaks.

Multi-row rules use isolation and retries. The constraints documentation states firmly that CHECK does not support referring to rows other than the one being checked. "The total of a family's wallets must not go negative" runs into this. Such a rule is guarded by SERIALIZABLE, and the failure always comes with SQLSTATE 40001. The serialization failure handling section explains that you must redo the whole transaction, including the logic that decides which SQL to issue, which is why PostgreSQL does not provide automatic retry. What to redo is 40001 and deadlocks (40P01), and it says to be more careful with 23505 and 23P01, which may be permanent errors. psycopg 3 gives the code through the exception's sqlstate, and the error codes appendix recommends branching on the code, not on the message text.

Invariant	Where to put it	Tool
One member per email	Constraint	UNIQUE + ON CONFLICT
Times of the same room do not overlap	Constraint	EXCLUDE + btree_gist + [)
One passenger per seat	Constraint	DEFERRABLE UNIQUE
A sum condition across several rows	Isolation	SERIALIZABLE + 40001 retry
Send an external mail only once	Application	Idempotency key, outbox

What it looks like in the field

A constraint also checks existing rows. The ALTER TABLE documentation says that adding a constraint normally scans the table to check whether all rows satisfy the condition. So putting one UNIQUE on a production table starts with "how do we clean up the duplicates that are already in?" For records that cannot be deleted, like bookings, do not delete them but turn them into a cancelled state, and then put the constraint only on the active rows. The most common accident in a retry loop is code that retries every error with except Exception. A constraint violation gives the same result a hundred times over, and there is even code that, when the attempts run out, swallows the failure and reports success.

What you will do in the next lab

You will reproduce a duplicate signup with two sessions, then clean up the existing duplicates and block it with UNIQUE and ON CONFLICT. On the bookings table you will find and cancel existing overlaps and add an exclusion constraint, and untangle the seat swap with DEFERRABLE. With psycopg 3 you will write a retry loop that retries only 40001, confirm that the loop stops properly when the grader deliberately raises errors, and then organize in a table where to put each invariant.