One throne, one Postgres row: the atomicity of a joke

Description and use

Using a single Postgres row with row‑level locking and a SECURITY DEFINER function guarantees atomicity and security for the throne game with free and paid capture.

Problem

Ensuring a single‑row game state remains consistent under concurrent access while allowing paid protection and free takeover.

Outcome

Single‑row design with row‑level locking and SECURITY DEFINER functions ensures atomicity, prevents race conditions, and cleanly separates free and paid actions.

Workflow

• Create throne_state table with a single row (id=1).
• Define usurp() PL/pgSQL function that locks the row, checks guard and grace periods, updates holder_name, since, guard_until, and usurpations, and returns JSON status.
• Expose usurp() to anonymous users via SECURITY DEFINER; restrict grant_guard() to service_role only.
• Use SELECT … FOR UPDATE to obtain row lock and serialize concurrent coups.
• Return JSON with ok flag and timestamps for UI countdowns.
• Client performs cosmetic guard check for UI but relies on DB for enforcement.
• Handle refused coups gracefully with countdown info.

Evidence

select … for update takes a row lock. Whoever gets it first evaluates the guard against now() and either takes the throne or is refused, and the loser evaluates the same condition against a row that has already changed.

Source

Anyone can read the throne. Nobody can write to it directly. The free action, usurping, is exposed to anonymous callers as a security definer function.

Source