会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束

作者

digoal

日期

2017-12-23

标签

PostgreSQL , tsrange , 范围 , exclude using , 排他约束 , btree_gist , 会议室预定 , 时间重叠 , 空间重叠

背景

PostgreSQL 范围、数组、空间类型(range, array, geometry)，都有交叉属性，例如时间范围：7点到9点，8点到9点，这两个内容是有重叠部分的。例如数组类型：[1,2,3]和[2,4,5]是有交叉部分的。例如空间类型也有交叉的属性。

那么在设计时，实际上业务上会有这样的约束，不允许对象有相交。

例如会议室预定系统，不允许两个人预定的会议室时间交叉，否则就有可能一个会议室在某个时间段被多人共享了，业务上是不允许的。

那么如何做到这样的约束呢？

PostgreSQL 提供了exclude约束，可以实现这个需求。

exclude 约束的语法

```
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
```

exclude 约束常用的操作符

范围、数组、空间类型的相交操作符如下：

postgres=# \do && List of operators Schema | Name | Left arg type | Right arg type | Result type | Description ------------+------+---------------+----------------+-------------+---------------------------------- pg_catalog | && | anyarray | anyarray | boolean | overlaps pg_catalog | && | anyrange | anyrange | boolean | overlaps pg_catalog | && | box | box | boolean | overlaps pg_catalog | && | circle | circle | boolean | overlaps pg_catalog | && | inet | inet | boolean | overlaps (is subnet or supernet) pg_catalog | && | polygon | polygon | boolean | overlaps pg_catalog | && | tinterval | tinterval | boolean | overlaps pg_catalog | && | tsquery | tsquery | tsquery | AND-concatenate public | && | integer[] | integer[] | boolean | overlaps (9 rows)

会议室预定系统的例子

1、创建btree_gist插件.

postgres=# create extension btree_gist; CREATE EXTENSION

2、创建会议室预定表

postgres=# create table t_meeting ( roomid int, -- 会议室ID who int, -- 谁定了这个会议室 ts tsrange, -- 时间范围 desc text, -- 会议内容描述 exclude using gist (roomid with = , ts with &&) -- 排他约束，同一个会议室，不允许有时间范围交叉的记录 ); CREATE TABLE

扩展CASE，通常会议室预定可能还有审核过程，那么也就是说用户提交请求的时候，允许时间重叠，但是对于审核后的数据，不允许重叠，假设审核后的状态为1. 比如我们只想针对状态为1的会议室时间设定不冲突，可以使用predict条件的exclude约束。 例子：

postgres=# create table t_meeting ( roomid int, -- 会议室ID who int, -- 谁定了这个会议室 status char(1) not null, -- 状态，1表示确定已定，0表示审核中。 ts tsrange, -- 时间范围 ds text, -- 会议内容描述 exclude using gist (roomid with = , ts with &&) where (status='1') -- 排他约束，同一个会议室，不允许有时间范围交叉的记录 ); CREATE TABLE

扩展阅读，PK约束，仅当某个值等于true时唯一，其他值不需要唯一。

postgres=# create table a (id int, status boolean, exclude using btree (id with =) where (status=true)); CREATE TABLE postgres=# insert into a values (1,true); INSERT 0 1 postgres=# insert into a values (1,true); psql: ERROR: conflicting key value violates exclusion constraint "a_id_excl" DETAIL: Key (id)=(1) conflicts with existing key (id)=(1). postgres=# insert into a values (2,true); INSERT 0 1 postgres=# insert into a values (2,false); INSERT 0 1 postgres=# insert into a values (2,false); INSERT 0 1

postgres=# insert into t_meeting values (1,123,'0', $$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$, 'PostgreSQL conference'); INSERT 0 1 postgres=# insert into t_meeting values (1,123,'0', $$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$, 'PostgreSQL conference'); INSERT 0 1 postgres=# insert into t_meeting values (1,123,'1', $$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$, 'PostgreSQL conference'); INSERT 0 1 postgres=# insert into t_meeting values (1,123,'1', $$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$, 'PostgreSQL conference'); ERROR: conflicting key value violates exclusion constraint "t_meeting_roomid_ts_excl" DETAIL: Key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")) conflicts with existing key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")).

3、预定会议室，如果同一个会议室输入的时间不允许预定（有交叉），则自动报错。实现强约束。

```
postgres=# insert into t_meeting values (1, 1, $$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$);
INSERT 0 1

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$);
ERROR: conflicting key value violates exclusion constraint "t_meeting_roomid_ts_excl"
DETAIL: Key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")) conflicts with existing key (roomid, ts)=(1, ["2017-01-01 07:00:00","2017-01-01 08:00:00")).

postgres=# insert into t_meeting values (2,1,$$['2017-01-01 07:00:00', '2017-01-01 08:00:00')$$);
INSERT 0 1

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 09:00:00', '2017-01-01 10:00:00')$$);
INSERT 0 1

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 09:00:00', '2017-01-01 11:00:00')$$);
ERROR: conflicting key value violates exclusion constraint "t_meeting_roomid_ts_excl"
DETAIL: Key (roomid, ts)=(1, ["2017-01-01 09:00:00","2017-01-01 11:00:00")) conflicts with existing key (roomid, ts)=(1, ["2017-01-01 09:00:00","2017-01-01 10:00:00")).

postgres=# insert into t_meeting values (1,1,$$['2017-01-01 08:00:00', '2017-01-01 09:00:00')$$);
INSERT 0 1
```

postgres=# select * from t_meeting order by roomid, ts; roomid | who | ts --------+-----+----------------------------------------------- 1 | 1 | ["2017-01-01 07:00:00","2017-01-01 08:00:00") 1 | 1 | ["2017-01-01 08:00:00","2017-01-01 09:00:00") 1 | 1 | ["2017-01-01 09:00:00","2017-01-01 10:00:00") 2 | 1 | ["2017-01-01 07:00:00","2017-01-01 08:00:00") (4 rows)

4、查询某个时间段还有哪些会议室能预定

会议室ID表，假设有50个会议室。

```
create table t_room (roomid int primary key);

insert into t_room select generate_series(1,50);
```

假设用户要预定 某一天：7点到9点的会议室，这样操作即可：

```
select roomid from t_room
except
select roomid from t_meeting where ts && $$['2017-01-01 07:00:00', '2017-01-01 09:00:00')$$;
roomid

 14    
  3    
  4    
 16    
 42    
 50    
 19    
 13    
 40    
 46    
 18    
 34    
 39    
  7    
 35    
 43    
 23    
 36    
 29    
 30    
 28    
  8    
 24    
 32    
 10    
 33    
  9    
 45    
 22    
 49    
 48    
 38    
 37    
  5    
 12    
 31    
 11    
 27    
 20    
 44    
 41    
  6    
 21    
 15    
 47    
 17    
 26    
 25
复制

(48 rows)

postgres=# explain (analyze,verbose,timing,costs,buffers) select roomid from t_room
except
select roomid from t_meeting where ts && $$['2017-01-01 07:00:00', '2017-01-01 09:00:00')$$;
QUERY PLAN

HashSetOp Except (cost=0.00..77.28 rows=2550 width=8) (actual time=0.074..0.085 rows=48 loops=1)
Output: "SELECT 1".roomid, (0)
Buffers: shared hit=3
-> Append (cost=0.00..70.88 rows=2562 width=8) (actual time=0.013..0.058 rows=53 loops=1)
Buffers: shared hit=3
-> Subquery Scan on "SELECT 1" (cost=0.00..61.00 rows=2550 width=8) (actual time=0.012..0.029 rows=50 loops=1)
Output: "SELECT 1".roomid, 0
Buffers: shared hit=1
-> Seq Scan on public.t_room (cost=0.00..35.50 rows=2550 width=4) (actual time=0.010..0.016 rows=50 loops=1)
Output: t_room.roomid
Buffers: shared hit=1
-> Subquery Scan on "SELECT 2" (cost=1.44..9.88 rows=12 width=8) (actual time=0.018..0.019 rows=3 loops=1)
Output: "SELECT 2".roomid, 1
Buffers: shared hit=2
-> Bitmap Heap Scan on public.t_meeting (cost=1.44..9.76 rows=12 width=4) (actual time=0.018..0.018 rows=3 loops=1)
Output: t_meeting.roomid
Recheck Cond: (t_meeting.ts && '["2017-01-01 07:00:00","2017-01-01 09:00:00")'::tsrange)
Heap Blocks: exact=1
Buffers: shared hit=2
-> Bitmap Index Scan on t_meeting_roomid_ts_excl (cost=0.00..1.44 rows=12 width=0) (actual time=0.010..0.010 rows=4 loops=1)
Index Cond: (t_meeting.ts && '["2017-01-01 07:00:00","2017-01-01 09:00:00")'::tsrange)
Buffers: shared hit=1
Planning time: 0.123 ms
Execution time: 0.172 ms
(24 rows)
```

速度杠杠的。开发也方便了。

小结

使用PostgreSQL，时间范围类型、exclude约束，很好的帮助业务系统实现会议室预定的强约束。

使用except语法，很方便的找到需要预定的时间段还有那些会议室是空闲的。

开不开心，解放开发人员的大脑。

参考

https://www.postgresql.org/docs/10/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.