Postgres advisory locks stop duplicate rows from double page loads

Repro: build a page that on load runs a plain SELECT to check for today's row, then INSERTs one if missing, as two separate statements outside a transaction. Open the page, then navigate away to an edit screen and back to it (or just open it in two tabs, or let React strict-mode double-fire the effect). Both requests read "no row yet" before either commits, so you get two rows for the same participant/day instead of one being reused.

Fix that held up under repeat testing: put the check-then-insert in one transaction, take a Postgres advisory lock scoped to the natural key at the very top of it, then do the insert as `INSERT INTO t (...) SELECT ... WHERE NOT EXISTS (SELECT 1 FROM t WHERE participant_id = $1 AND day = $2)`. `pg_advisory_xact_lock(key)` takes a transaction-scoped lock that serializes any other transaction asking for the same key, and it releases itself automatically on commit or rollback — no manual unlock, no schema migration, no unique index required.

Advisory locks are keyed by integers you choose yourself, not by a value already in a column, so you hash the natural key into that space: something like `pg_advisory_xact_lock(hashtext(participant_id::text || day::text))` folds it to one bigint. Hash collisions between unrelated keys are possible in theory — two different participant/day pairs landing on the same 64-bit hash — but the space is big enough that it doesn't show up in practice for this kind of workload. If that risk is unacceptable, use the two-int form of the function with two smaller, independently-chosen values instead of hashing to one bigint.

Why not just add a unique constraint on (participant_id, day)? That also fixes the duplicate-row outcome, but you then have to catch the unique-violation exception in application code and decide what to do with the second request. The advisory-lock-plus-NOT-EXISTS version makes the second request simply see the row and skip the insert, so the calling code has one code path instead of a success path and an exception path. Worth it specifically when the endpoint is hit by page loads and back-navigation rather than by a form the user consciously resubmits.

Checkable on any Postgres instance: open two `psql` sessions, `BEGIN`, call `pg_advisory_xact_lock(1)` in both, and watch the second block until the first session commits or rolls back.