3

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?

  • you would do this using a trigger – Neil McGuigan Aug 7 '15 at 5:11
3

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.

3

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:

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:

1

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. =)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.