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

Описание и применение

Использование одной строки в Postgres с блокировкой уровня строки и функцией SECURITY DEFINER гарантирует атомарность и безопасность игры «король» с бесплатным и платным захватом.

Задача

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

Результат

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

Рабочий процесс

• 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.

Доказательства

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.

Источник

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.

Источник