如何在PostgreSQL中实现适配可变容量的活动报名表行数限制

第一步：设计核心表结构 #

-- 房间表
CREATE TABLE room (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  capacity INT NOT NULL CHECK (capacity > 0),
  -- 其他房间字段：名称、位置等
  name TEXT NOT NULL
);

-- 活动表
CREATE TABLE event (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  room_id INT NOT NULL REFERENCES room(id) ON DELETE CASCADE,
  -- 其他活动字段：名称、开始时间等
  name TEXT NOT NULL,
  start_time TIMESTAMPTZ NOT NULL
);

-- 报名表
CREATE TABLE registration (
  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  event_id INT NOT NULL REFERENCES event(id) ON DELETE CASCADE,
  student_id INT NOT NULL, -- 关联你的学生表id
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- 避免同一学生重复报同一个活动
  UNIQUE(event_id, student_id)
);

第二步：创建容量校验触发器函数 #

不需要手动维护registrations计数，所有校验逻辑在数据库层原子执行，避免并发超售：

CREATE OR REPLACE FUNCTION check_event_capacity()
RETURNS TRIGGER AS $$
DECLARE
  current_reg_count INT;
  max_capacity INT;
BEGIN
  -- 加行锁避免并发插入导致的超售
  PERFORM 1 FROM event WHERE id = NEW.event_id FOR UPDATE;
  
  -- 获取当前活动关联房间的最大容量
  SELECT r.capacity INTO max_capacity
  FROM event e
  JOIN room r ON e.room_id = r.id
  WHERE e.id = NEW.event_id;
  
  -- 获取当前活动已报名人数
  SELECT COUNT(*) INTO current_reg_count
  FROM registration
  WHERE event_id = NEW.event_id;
  
  -- 校验是否超容
  IF current_reg_count >= max_capacity THEN
    RAISE EXCEPTION '活动报名人数已达上限，无法报名';
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

第三步：绑定触发器到报名表 #

CREATE TRIGGER trigger_registration_capacity_check
BEFORE INSERT ON registration
FOR EACH ROW EXECUTE FUNCTION check_event_capacity();

可选：房间容量下调校验 #

如果需要在房间容量下调时，避免已报名人数超过新容量，可以额外加房间表的更新触发器：

CREATE OR REPLACE FUNCTION check_room_capacity_update()
RETURNS TRIGGER AS $$
DECLARE
  over_capacity_event INT;
BEGIN
  -- 检查是否有绑定该房间的活动报名人数超过新容量
  SELECT e.id INTO over_capacity_event
  FROM event e
  JOIN registration r ON e.id = r.event_id
  WHERE e.room_id = NEW.id
  GROUP BY e.id
  HAVING COUNT(r.id) > NEW.capacity
  LIMIT 1;
  
  IF over_capacity_event IS NOT NULL THEN
    RAISE EXCEPTION '房间容量下调后，活动%的已报名人数超过新容量，无法修改', over_capacity_event;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_room_capacity_check
BEFORE UPDATE OF capacity ON room
FOR EACH ROW EXECUTE FUNCTION check_room_capacity_update();

如果不想维护触发器，也可以在插入报名时用原子化的SQL语句直接校验，全程不需要客户端传计数，同样可以避免超售：

WITH event_capacity AS (
  SELECT r.capacity AS max_cap, COUNT(reg.id) AS current_reg
  FROM event e
  JOIN room r ON e.room_id = r.id
  LEFT JOIN registration reg ON e.id = reg.event_id
  WHERE e.id = $1 -- 代入要报名的活动ID
  GROUP BY e.id, r.capacity
)
INSERT INTO registration (event_id, student_id)
SELECT $1, $2 -- 代入活动ID、学生ID
FROM event_capacity
WHERE current_reg < max_cap
RETURNING id;

执行后如果返回空，就说明报名失败（超容），否则返回报名记录ID。 #

两种方案都完全适配Supabase，直接在Supabase的SQL控制台执行对应代码即可生效，所有逻辑都在Postgres服务端执行，完全避免客户端校验的并发问题和数据不一致问题。

内容的提问来源于stack exchange，提问作者dnik