用 PostgreSQL EXCLUDE 约束防止会议室预订时间重叠
用 PostgreSQL EXCLUDE 约束防止会议室预订时间重叠
问题场景
会议室预订系统常见的一个问题是:用户 A 和用户 B 几乎同时提交同一会议室、相近时间段的预订。如果应用层采用“先查询是否空闲,再插入记录”的方式,两个并发请求可能都查到空闲,然后都执行插入,最终产生时间重叠的预订记录。应用层的校验只能作为体验优化,数据库约束才是最终防线。
PostgreSQL 的排除约束(EXCLUDE)可以表达“在满足某些相等条件的行之间,某个范围不能重叠”。在会议室场景中,就是:同一个 room_id 下,during 时间段不能相交。
设计要点
- 使用
tstzrange表示带时区的时间范围,范围采用半开区间[start, end);这样09:00-10:00与10:00-11:00可以相邻,不会被视为重叠。 - 使用
EXCLUDE USING gist (room_id WITH =, during WITH &&)。其中room_id WITH =需要btree_gist扩展为 GiST 索引提供等值比较能力,during WITH &&表示时间范围相交。 - 插入冲突时,PostgreSQL 返回 SQLSTATE
23P01(exclusion_violation),应用根据状态码返回可读提示,而不是匹配错误文本。
最小可运行示例
以下脚本可以在测试库中执行。请先确认当前数据库允许创建 btree_gist 扩展。
CREATE EXTENSION IF NOT EXISTS btree_gist;
DROP TABLE IF EXISTS meeting_room_booking;
CREATE TABLE meeting_room_booking (
id bigserial PRIMARY KEY,
room_id bigint NOT NULL,
booked_by text NOT NULL,
during tstzrange NOT NULL,
CONSTRAINT booking_during_valid CHECK (
NOT isempty(during)
AND lower_inc(during)
AND NOT upper_inc(during)
AND lower(during) IS NOT NULL
AND upper(during) IS NOT NULL
AND lower(during) < upper(during)
),
CONSTRAINT booking_no_overlap EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);
正常插入一条预订:
INSERT INTO meeting_room_booking (room_id, booked_by, during)
VALUES (
101,
'user_a',
tstzrange(
'2025-03-10 09:00:00+08',
'2025-03-10 10:00:00+08',
'[)'
)
);
再插入一条与上一条重叠的预订,用 PL/pgSQL 捕获冲突并输出友好提示:
DO $$
BEGIN
INSERT INTO meeting_room_booking (room_id, booked_by, during)
VALUES (
101,
'user_b',
tstzrange(
'2025-03-10 09:30:00+08',
'2025-03-10 10:30:00+08',
'[)'
)
);
EXCEPTION
WHEN SQLSTATE '23P01' THEN
RAISE NOTICE '该会议室在所选时段已被预订,请选择其他时间';
END
$$;
由于采用半开区间,下面这条与 09:00-10:00 相邻的预订可以成功:
INSERT INTO meeting_room_booking (room_id, booked_by, during)
VALUES (
101,
'user_c',
tstzrange(
'2025-03-10 10:00:00+08',
'2025-03-10 11:00:00+08',
'[)'
)
);
应用侧捕获异常时,应判断数据库返回的 SQLSTATE,而不是错误消息文本。伪代码如下:
try:
execute(insert_booking_sql, params)
except DatabaseError as e:
if e.pgcode == '23P01':
return '该会议室在该时段已被预订,请选择其他时间'
raise
查询某天剩余可用时段
下面的 SQL 以 30 分钟为粒度,查询会议室 101 在某天 09:00-18:00 内未被预订的槽位。它使用 NOT EXISTS 判断每个候选槽位是否与已有预订相交。
WITH params AS (
SELECT
101::bigint AS room_id,
'2025-03-10 09:00:00+08'::timestamptz AS day_start,
'2025-03-10 18:00:00+08'::timestamptz AS day_end,
interval '30 minutes' AS step
),
slots AS (
SELECT
p.room_id,
gs AS slot_start,
gs + p.step AS slot_end
FROM params p
CROSS JOIN LATERAL generate_series(
p.day_start,
p.day_end - p.step,
p.step
) AS gs
)
SELECT
s.slot_start AT TIME ZONE 'Asia/Shanghai' AS slot_start_local,
s.slot_end AT TIME ZONE 'Asia/Shanghai' AS slot_end_local
FROM slots s
WHERE NOT EXISTS (
SELECT 1
FROM meeting_room_booking b
WHERE b.room_id = s.room_id
AND b.during && tstzrange(s.slot_start, s.slot_end, '[)')
)
ORDER BY s.slot_start;
该查询返回的是离散槽位,不是自动合并后的连续空闲区间。如果业务需要按 15 分钟或 60 分钟展示,可以调整 step。槽位展示粒度越细,越容易保留预订之间的零碎空隙;但它仍然是展示逻辑,最终预订是否冲突由前面的 EXCLUDE 约束保证。
运行与验证步骤
- 将完整 SQL 保存为
booking.sql,使用 psql 连接测试数据库执行:
psql "host=<主机> port=5432 dbname=<数据库> user=<用户名>" -f booking.sql
-
观察第一条插入成功,故意重叠的
DO块输出 NOTICE,相邻的10:00-11:00插入成功。 -
查询表中的记录,确认没有产生重叠预订:
SELECT id, room_id, booked_by, during
FROM meeting_room_booking
ORDER BY room_id, lower(during);
-
执行“剩余可用时段”查询。在示例数据下,
09:00-11:00已被占用,结果应从11:00开始返回可用的 30 分钟槽位。 -
可选用两个数据库会话分别插入相互重叠的预订,确认冲突方收到 SQLSTATE
23P01,从而验证约束确实在数据库层生效。
边界与限制
btree_gist需要在目标数据库中安装,并需要相应权限。托管数据库若限制扩展安装,应先确认实例是否允许启用该扩展。- 时间范围统一使用
timestamptz和[start, end)语义。应用层写入、展示和校验都应保持相同语义,避免把本地时间误当作无时区时间。 - EXCLUDE 约束不能附加类似
WHERE status = 'active'的过滤条件。如果取消的预订需要保留在同一张表中,就不要把已取消记录继续放在受约束的活动预订表里;可以将取消记录迁移到历史表,或删除活动记录。 - 捕获
23P01后,在普通事务中该事务通常已进入失败状态,应用需要回滚后再继续其他操作;在 PL/pgSQL 的 EXCEPTION 块中,冲突语句会被子事务回滚,外层可继续执行。 - 该约束只限制同一
room_id的时间重叠,不同会议室同一时间仍可各自预订。若还需要限制预订必须在营业时间内,可增加 CHECK 约束或在应用层校验。 - 查询剩余时段的 SQL 按固定槽位判断,不能把一个已被部分占用的槽位拆成更细的空闲片段;应根据实际产品需求选择槽位粒度或改用范围运算。
- 点赞
- 收藏
- 关注作者
评论(0)