How to write a constraint concerning a max number of rows in postgresql?

Question

I think this is a pretty common problem.

I've got a table user(id INT ...) and a table photo(id BIGINT, owner INT). owner is a reference on user(id).

I'd like to add a constraint to the table photo that would prevent more than let's say 10 photos to enter the database for each users.

What's the best way of writing this?

Thx!

score 36 · Accepted Answer · answered Nov 16 '09 at 17:46

Quassnoi is right; a trigger would be the best way to achieve this.

Here's the code:

CREATE OR REPLACE FUNCTION enforce_photo_count() RETURNS trigger AS $$
DECLARE
    max_photo_count INTEGER := 10;
    photo_count INTEGER := 0;
    must_check BOOLEAN := false;
BEGIN
    IF TG_OP = 'INSERT' THEN
        must_check := true;
    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF (NEW.owner != OLD.owner) THEN
            must_check := true;
        END IF;
    END IF;

    IF must_check THEN
        -- prevent concurrent inserts from multiple transactions
        LOCK TABLE photos IN EXCLUSIVE MODE;

        SELECT INTO photo_count COUNT(*) 
        FROM photos 
        WHERE owner = NEW.owner;

        IF photo_count >= max_photo_count THEN
            RAISE EXCEPTION 'Cannot insert more than % photos for each user.', max_photo_count;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER enforce_photo_count 
    BEFORE INSERT OR UPDATE ON photos
    FOR EACH ROW EXECUTE PROCEDURE enforce_photo_count();

I included table locking in order to avoid situations where two concurrent tansactions would count photos for a user, see that the current count is 1 below the limit, and then both insert, which would cause you to go 1 over the limit. If that's not a concern for you it would be best to remove the locking as it can become a bottleneck with many inserts/updates.

One more thing: unfortunately, all the IF statements at the beginning of the trigger can't be joined into a single "IF TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND NEW.owner != OLD.owner) THEN ..." because PLPGSQL doesn't support short circuiting. — Aleksander Kmetec, Nov 16 '09 at 17:51
Is this still the best way to do it in 2019, with a recent version of potsgres (11)? I couldn't find a more recent (and satisfying) answer for this common problem — Lotram, Mar 07 '19 at 10:30
The table lock is what got me! Thank you so much for this answer. — cgatian, Mar 16 '23 at 18:18

score 16 · Answer 2 · answered Nov 16 '09 at 17:01

16

You cannot write such a constraint in a table declaration.

There are some workarounds:

Create a trigger that would check the number of photos for each user
Create a photo_order column that would keep the order of photos, make (user_id, photo_order) UNIQUE, and add CHECK(photo_order BETWEEN 1 AND 10)

answered Nov 16 '09 at 17:01

Quassnoi

413,100
91
616
614

2

Would you not then have to enforce uniqueness on photo order? How to do that? – ahnbizcad Nov 08 '14 at 18:21
@ahnbizcad: late to the party, but yes, you would, with a `UNIQUE` constraint mentioned in the post. – Quassnoi May 02 '23 at 17:12

score 3 · Answer 3 · answered Nov 16 '09 at 18:06

One another approach would be to add column "photo_count" to users table, update it with triggers to make it reflect reality, and add check on it to enforce maximum number of photos.

Side benefit from this is that at any given moment we know (without counting) how many photos user has.

On other hand - the approach Quassnoi suggested is also pretty cool, as it gives you ability to reorder the photos in case user would want it.

score 3 · Answer 4 · answered Sep 14 '18 at 08:44

3

A better alternative would be to check the number of rows when you do the insert:

insert into photos(id,owner) 
select 1,2 from dual
where (select count(*) from photos where id=1) < 10

answered Sep 14 '18 at 08:44

flob

116
5

how this will prevent race condition ? – user2274074 Oct 25 '22 at 07:43

score 0 · Answer 5 · answered Jul 25 '21 at 10:55

I answered similar question here:

Cap on number of rows matching a condition in Postgres

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 integer 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 < 10;
    GET DIAGNOSTICS updated = ROW_COUNT;
    IF updated = 0 THEN
        RAISE EXCEPTION 'a user can only have 10 photos';
    END IF;
    RETURN NEW;
END;
$BODY$;

CREATE TRIGGER photo_increment_user_photo_count
    BEFORE INSERT
    ON public.photo
    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 photo_decrement_user_photo_count
    AFTER DELETE
    ON public.photo
    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.

How to write a constraint concerning a max number of rows in postgresql?

5 Answers5

Linked