标签
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
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
