Limiting the number of rows that can be inserted for a given WHERE clause

Question

In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions?

I have a table event_attendance with columns event_id, user_id, rsvp_date, and confirmed. I want to insert a row into this table for a given user, so that only the first N users can RSVP.

Only if the inserted row is one of the first N rows matching where event_id = $eventId should the confirmed column be set to true.

I'm not sure if this needs a transaction, but I would love to do it in a more lightweight atomic way, using something like insert ... limit ($N - (select count(id) from event_acceptance where event_id = $eventId)) or similar (I really don't know what I'm doing here...).

Also, I need to be able to do an upsert (insert ... on conflict), because the attendance in the real application is a bit more complex than this (confirmed is not a boolean, it is null (not going), 'interested', or 'going', and I need to be able to update this record, subject to the capacity constraints of the event, whether or not there is an existing event_attendance record).

And finally, it would be great if I could do a single update in time O(log N), not time O(N), i.e. use an index to determine what the current total row count is that matches the given where clause, rather than a linear count.

Answer 1

I can think of three ways:

1. Wrap all writes to that table in SERIALIZABLE transactions. (Be prepared to repeat after serialization errors.) Expensive, safe.

2. Have a table event with one row per event and take, in the same transaction with default READ COMMITTED isolation level, an exclusive lock on the event before writing to event_attendance. Probably faster. My favorite.

3. Have a 1:n table with numbered slots, let's call it event_attendance_slots. You would pre-insert N rows per event with an "open" tag. Attendance would be added via UPDATE, setting the slot "closed". Now, standard write locks do the job. Find the first free slot with FOR UPDATE SKIP LOCKED.

WITH my_slot AS (
   SELECT event_id, slot_id
   FROM   event_attendance_slot
   WHERE  event_id = 123
   AND    status = 'open'
   LIMIT  1
   FOR    UPDATE SKIP LOCKED
   )
UPDATE event_attendance_slot s
SET    status = 'closed'
     , col1 = 'foo'
FROM   my_slot m
WHERE (s.event_id, s.slot_id) = (m.event_id, m.slot_id)
;

See:

• Postgres UPDATE ... LIMIT 1