Cap on number of rows matching a condition in Postgres

Say I have a db schema like this

user_id text
photo_id text
date_created timestamp

and I want to enforce a constraint that says "a user can only have 30 photos". A naive way to implement this would be to do

SELECT COUNT(*) FROM user_photos WHERE user_id='usr_123'

compare the result to 30, if it's lower, then do the insert. Naive because if you can trigger multiple requests in a short amount of time, you can win a race and end up writing more than 30 records.

Is there a way to enforce this constraint in the database? eg create some kind of index that says "only 30 rows can be inserted", or something? Is there a way to have a count column that increments separately for each userId, and you can limit its range to 0-29?

If you used a transaction, would that lock the rows you read?

Similar to what @Eelke and @Kassandry suggested. But you don't need a counter or trigger.

CREATE TABLE t1 (
  user_id text NOT NULL  -- should probably be integer
, photo_nr int NOT NULL
, photo_id text NOT NULL
, date_created timestamptz NOT NULL DEFAULT now()
, CONSTRAINT t1_pkey PRIMARY KEY (user_id, photo_nr)
, CONSTRAINT max_30_photos CHECK (photo_nr BETWEEN 1 AND 30)
);

The key question is how to get the next free slot cheaply and safely. I suggest:

SELECT photo_nr
   FROM   generate_series (1,30) photo_nr
   EXCEPT (SELECT photo_nr FROM t1 WHERE user_id = 'usr123') -- your user_id here
   ORDER  BY 1
   LIMIT  1;

Details:

• Select rows where value of second column is not present in first column

This returns the next free photo_nr for the given user. Base your INSERT statement on it:

INSERT INTO t1 (user_id, photo_nr, photo_id)
SELECT 'usr123', photo_nr, 'my_new_photo_id'  -- provide values here
FROM  (
   SELECT photo_nr
   FROM   generate_series (1,30) photo_nr
   EXCEPT (SELECT photo_nr FROM t1 WHERE user_id = 'usr123')  -- given user_id
   ORDER  BY 1
   LIMIT  1
   ) sub;

If there is no free slot, nothing is inserted (as reported by the command tag).

This is not safe against concurrent requests. The next free photo_nr is the same for all concurrent transactions. It can lead to unique violations for concurrent INSERT. But it's typically cheaper to just catch that error and retry in those very rare cases instead of locking the whole table (you cannot lock rows that don't exist yet in Postgres) or locking all rows for the given user exclusively.

You could wrap the INSERT in a PL/pgSQL function to retry on a unique violations. Code example:

• Is SELECT or INSERT in a function prone to race conditions?

If that's a problem, a cheap alternative is to lock the parent row in the users table to rule out any race condition. See:

• How to atomically replace a subset of table data

StackExchange Database Administrators Q#109305, answer score: 6