How can I model a limit of number of Rows in a Table in Postgres

Question

i am currently learning how to work with databases in a full stack web development context. I want to model a scenario where students can enroll into a variety of events which have a certain capacity. I already looked into this thread:

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

but couldn't quite apply it to my scenario, since it should be possible to have different capacities which can also change when the room is updated for example(the capacity references the capacity column of a room table)

Is there an elegant way to model this?

My current solution is to have a column for capacity and one for registrations that gets in-/decremented on every insert/delete. This crude check also happens client side atm, which i am aware of being a bad practice for several reasons and i am absolutely not happy with it, which is why i created this post.

I think one could at least model this server side with Postgres functions, but since i am not familiar with those yet, i wanted to ask first, whether there is a generally better way of modelling such a thing. I am using a Supabase instance for my project.

Thanks in advance everyone!

score 0 · Accepted Answer · answered Oct 20 '21 at 19:55

There are a lot of ways to accomplish this, but if you're just trying to make PostgreSQL insure that you never overbook an event you can write a TRIGGER for INSERT and UPDATE on your registration table.

I tested this on my Supabase instance, because there's nothing more annoying than getting an expert answer from StackOverflow that doesn't work!

CREATE TABLE person (id INTEGER UNIQUE PRIMARY KEY, firstname TEXT, lastname TEXT);
CREATE TABLE event (id INTEGER UNIQUE PRIMARY KEY, event_name TEXT, capacity INTEGER);
CREATE TABLE registration (id INTEGER UNIQUE PRIMARY KEY, event_id INTEGER , person_id INTEGER);

INSERT INTO person (id, firstname, lastname) VALUES (1, 'Bob','Barker'),(2, 'Jamie','Oliver'),(3, 'Gary ','Busey');

INSERT INTO event (id, event_name, capacity) VALUES (1, 'Fireman''s Ball', 2);

CREATE OR REPLACE FUNCTION check_registration_capacity()
  RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT count(*) FROM registration as r1 WHERE r1.event_id = NEW.event_id) > 
     ((SELECT capacity FROM event WHERE event.id = NEW.event_id) - 1)
  THEN
    RAISE EXCEPTION 'Event is full';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER verify_registration_capacity 
BEFORE INSERT OR UPDATE ON registration 
FOR EACH ROW EXECUTE PROCEDURE check_registration_capacity();

-- register Bob for the Fireman's Ball
INSERT INTO registration (id, event_id, person_id) VALUES (1, 1, 1); 
-- register Jamie for the Fireman's Ball
INSERT INTO registration (id, event_id, person_id) VALUES (2, 1, 2);
-- sorry, Gary, the next line throws an 'Event is full' error
INSERT INTO registration (id, event_id, person_id) VALUES (3, 1, 3);

Hi, sorry for the late response! This concept worked perfectly, thank you! I had to make some adjustments, since the capacity shall be bound to the room that the event takes place in in my scenario, so i had to join that in and it works! Why is the trigger also listening to updates to the table though? and why does it need to be executed on each row? that i didnt quite understand, any hint is appreciated :) — dnik, Oct 26 '21 at 09:30
I had the trigger listen on updates because what happens if you update a registration record and change the event_id value? Say a person registers for event 1 and changes their mind and you change the record to event 2, but that update puts event 2 over the limit? You can avoid that in your client code, but just wanted to be safe. "FOR EACH ROW" actually only fires once for a single record, I think it's there for more complicate transaction-based stuff that operates on multiple rows at once. In this case, it's just a simple trigger on a single row at a time though. — Mark Burggraf, Oct 26 '21 at 19:03
Thank you, i think i grasped that concept! In addition to that I had to specify the function as a security definer, since RLS is now configured to only allow access to one's own entries in the table. To prevent potential abuse i set the search path to public and marked it as stable. Is this a bad idea? Thank you! — dnik, Nov 18 '21 at 01:20
You've already taken steps to protect yourself by setting the search path, so I think you should be fine. You can research the security implications of using security definer -- I've seen there are ways to exploit these kind of functions but you should be relatively safe. — Mark Burggraf, Nov 19 '21 at 17:02

How can I model a limit of number of Rows in a Table in Postgres

1 Answers1