A few weeks ago I wrote about preventing double bookings in Postgres with advisory locks, and recorded a short video where I test it live with two concurrent requests:
Readers reviewed the code and found real problems. This post lists three of them, what each one looked like, and the fix. If you build booking or checkout flows, you may have the same bugs.
Bug 1: the trigger ran with the wrong privileges
My first snippet was a plain trigger function. A trigger function runs with the privileges of the user doing the insert. With Row Level Security on, that user may only see their own rows, so count(*) could miss other people's bookings and the check would pass when it should not.
Fix: run the function as its owner, with a fixed search_path and qualified table names.
sql
CREATE OR REPLACE FUNCTION public.check_booking_availability()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
-- ...…