Cap on number of rows matching a condition in Postgres

Question

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?

Answer 1

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

Answer 2

Your program could manually number the records for each user then you could put a unique constraint on the combination of the user_id and the recordnumber (or make it the primary key) and put a check constraint on recordnumber cheking it is between 1 and 30.

Answer 3

You could do something like this:

Example table:

CREATE TABLE t1 (user_id text, photo_id text, photo_limit_count integer NOT NULL, date_created timestamptz);

Add a check constraint:

ALTER TABLE t1 ADD CONSTRAINT photo_limit_count_chk CHECK (photo_limit_count > 0 AND photo_limit_count <= 30);

Create a unique index, to make sure that there can't accidentally be duplicates for the user_id and number of photos:

CREATE UNIQUE INDEX t1_user_id_photo_limit_count ON t1(user_id,photo_limit_count);

Create a trigger to auto increment the photo_limit_count for you:

CREATE OR REPLACE FUNCTION trig_inc_photo_limit() RETURNS TRIGGER AS $BODY$
    DECLARE
        row_count integer;
    BEGIN
        SELECT count(*) INTO row_count FROM t1 WHERE user_id = NEW.user_id;
        IF row_count = 0 THEN
            NEW.photo_limit_count = 1;
            RETURN NEW;
        ELSE
            NEW.photo_limit_count = row_count + 1;
            RETURN NEW;
        END IF;
    END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER t1_inc_photo_limit_trigger BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE trig_inc_photo_limit();

Do an insert:

 INSERT INTO t1 (user_id, photo_id, date_created) VALUES ('a','1',now());

 postgres@[local]:5432:postgres:=# INSERT INTO t1 (user_id, photo_id, date_created) VALUES ('a','1',now());
 INSERT 0 1
 Time: 34.559 ms
 postgres@[local]:5432:postgres:=#

Insert 30 more times:

postgres@[local]:5432:postgres:=# INSERT INTO t1 (user_id, photo_id, date_created) VALUES ('a','1',now());
ERROR:  new row for relation "t1" violates check constraint "photo_limit_count_chk"
DETAIL:  Failing row contains (a, 1, 31, 2015-08-06 22:52:10.594288-07).
Time: 0.178 ms
postgres@[local]:5432:postgres:=# SELECT count(*) FROM t1 WHERE user_id = 'a';
 count 
-------
  30
(1 row)

Time: 0.225 ms
postgres@[local]:5432:postgres:=#

Doing the same thing again for b, 31 times:

postgres@[local]:5432:postgres:=# INSERT INTO t1 (user_id, photo_id, date_created) VALUES ('b','1',now());
ERROR:  new row for relation "t1" violates check constraint "photo_limit_count_chk"
DETAIL:  Failing row contains (b, 1, 31, 2015-08-06 22:52:10.594288-07).
Time: 0.322 ms
postgres@[local]:5432:postgres:=# SELECT user_id, count(*) FROM t1 GROUP BY user_id HAVING count(*) = 30;
 user_id | count 
---------+-------
 b       |    30
 a       |    30
(2 rows)

Time: 0.419 ms
postgres@[local]:5432:postgres:=#

Hope that helps with your problem. =)

Answer 4

We can save the number of user photos in the user table or a table like user_statistics and use triggers to perform atomic increment and decrement that locks one row (user row) and is safe against concurrent requests:

CREATE TABLE public.user_statistics
(
    user_id text NOT NULL,
    photo_count smallint NOT NULL DEFAULT 0,
    CONSTRAINT user_statistics_pkey PRIMARY KEY (user_id),
    CONSTRAINT user_statistics_user_id_fkey FOREIGN KEY (user_id)
        REFERENCES public.user (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
)

CREATE FUNCTION public.increment_user_photo_count()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    updated integer;
BEGIN
    UPDATE
        user_statistics
    SET
        photo_count = photo_count + 1
    WHERE
        user_statistics.user_id = NEW.user_id AND user_statistics.photo_count < 30;
    GET DIAGNOSTICS updated = ROW_COUNT;
    IF updated = 0 THEN
        RAISE EXCEPTION 'a user can only have 30 photos';
    END IF;
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER user_photos_increment_user_photo_count
    BEFORE INSERT
    ON public.user_photos
    FOR EACH ROW
    EXECUTE PROCEDURE public.increment_user_photo_count();

CREATE FUNCTION public.decrement_user_photo_count()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    UPDATE
        user_statistics
    SET
        photo_count = photo_count - 1
    WHERE
        user_statistics.user_id = OLD.user_id;
    RETURN NULL;
    -- result is ignored since this is an AFTER trigger
END;
$BODY$;

CREATE TRIGGER user_photos_decrement_user_photo_count
    AFTER DELETE
    ON public.user_photos
    FOR EACH ROW
    EXECUTE PROCEDURE public.decrement_user_photo_count();

Instead of triggers we can update the photo_count like above in a transaction at application side and throw exception (rollback) for the increment if no rows affected by the update.