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.

来源