Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

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

3 minute read

背景

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    

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

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...