如何处理带排它约束的PostgreSQL酒店预订表的更新问题

报错原因 #

你设置的GIST排它约束逻辑为同一房间的任意两条记录时间段不得重叠，当你尝试将id=1的记录修改为2021-01-03至2021-01-10的时间段时，该区间和已存在的id=2（2021-01-02）、id=3（2021-01-03）的记录时间段重叠，因此触发约束拦截。

可行解决方案（结合你按天存储的业务需求） #

方案1：先删冗余旧记录再更新（适合需要保留id=1的场景） #

不需要保留全部原有预订记录，调整后1月1日、1月2日的预订已作废，1月3日的旧记录也会和新的时间段冲突，按以下事务顺序执行即可：

BEGIN;
-- 删除该用户对应房间的旧预订记录
DELETE FROM reservation WHERE guest_id = 1 AND room = 101 AND during <@ '[2021-01-01 00:00:00, 2021-01-03 23:59:00]'::tsrange;
-- 再更新id=1的记录为新的时间段
UPDATE reservation SET from_ts = '2021-01-03 00:00:00', to_ts='2021-01-10 23:59:00', during = '[2021-01-03 00:00:00, 2021-01-10 23:59:00]' WHERE id = 1;
COMMIT;

事务保证操作原子性，不会出现中间状态触发约束，同时可以保留id=1不变。

方案2：调整排它约束规则（适合频繁修改同用户预订的场景） #

如果希望同一个用户调整自己的预订时不会被约束拦截，可以修改约束规则，仅校验不同用户的同一房间时间段重叠：
首先安装btree_gist扩展（支持int类型的GIST索引）：

CREATE EXTENSION IF NOT EXISTS btree_gist;

修改表约束：

ALTER TABLE reservation DROP CONSTRAINT reservation_room_during_excl;
ALTER TABLE reservation ADD EXCLUDE USING GIST (
    room WITH =,
    guest_id WITH <>, -- 仅当guest_id不同时才触发重叠校验
    during WITH &&
);

修改后同一个用户调整自己的预订时间段不会触发约束，你可以先更新id=1的记录，再删除多余的旧记录即可。

方案3：按天拆分新预订记录（最符合你现有设计的推荐方案） #

你备注每天有独立属性，不建议用单条记录存多天的预订，直接按天生成新的预订记录即可：

BEGIN;
-- 删除旧的预订记录
DELETE FROM reservation WHERE guest_id = 1 AND room = 101 AND during <@ '[2021-01-01 00:00:00, 2021-01-03 23:59:00]'::tsrange;
-- 插入2021-01-03到2021-01-10的每日记录，示例如下，其余日期同理
INSERT INTO reservation (id, guest_id, room, from_ts, to_ts, during) VALUES 
(1, 1, 101, '2021-01-03 00:00:00', '2021-01-03 23:59:00', '[2021-01-03 00:00:00, 2021-01-03 23:59:00]'),
(4, 1, 101, '2021-01-04 00:00:00', '2021-01-04 23:59:00', '[2021-01-04 00:00:00, 2021-01-04 23:59:00]');
-- 其余日期插入逻辑省略
COMMIT;

该方案完全契合你按天存储的设计，不会触发排它约束，也方便后续维护每日独立属性。

疑问解答 #

Q：是否需要保留该预订id不变？
A：如果id=1存在其他业务关联（比如订单、支付记录绑定），可以保留id=1对应1月3日的记录，其余新的日期使用新id即可，无需强行将多天预订合并到id=1的单条记录中。

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