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

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
云数据库 RDS SQL Server,基础系列 2核4GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
关系型数据库 Go PostgreSQL
golang pgx自定义PostgreSQL类型
golang的pgx驱动提供了大约70种PostgreSQL类型支持,但还是有一些类型没有涵盖,本文介绍如何自己编写代码支持特殊的类型。
|
7月前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
4月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
429 0
|
6月前
|
自然语言处理 关系型数据库 数据库
技术经验解读:【转】PostgreSQL的FTI(TSearch)与中文全文索引的实践
技术经验解读:【转】PostgreSQL的FTI(TSearch)与中文全文索引的实践
67 0
|
关系型数据库 C语言 PostgreSQL
PostgreSQL服务端开发学习 -- fmgr.h
fmgr按官方的解释就是Postgres函数管理器和函数调用接口,在使用C语言开发PostgreSQL后端应用时,所以与backend交互时必须遵循fmgr.h中定义的一些规范。
|
7月前
|
分布式计算 DataWorks 关系型数据库
DataWorks产品使用合集之在使用 DataWorks 数据集成同步 PostgreSQL 数据库中的 Geometry 类型数据如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
91 0
|
7月前
|
分布式计算 关系型数据库 大数据
MaxCompute产品使用合集之怎么才可以将 PostgreSQL 中的 geometry 空间类型字段同步到 MaxCompute 或另一个 PostgreSQL 数据库
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
7月前
|
弹性计算 关系型数据库 数据库
开源PostgreSQL在倚天ECS上的最佳优化实践
本文基于倚天ECS硬件平台,以自顶向下的方式从上层应用、到基础软件,再到底层芯片硬件,通过应用与芯片的硬件特性的亲和性分析,实现PostgreSQL与倚天芯片软硬协同的深度优化,充分使能倚天硬件性能,帮助开源PostgreSQL应用实现性能提升。
|
7月前
|
SQL 运维 关系型数据库
基于AnalyticDB PostgreSQL的实时物化视图研发实践
AnalyticDB PostgreSQL版提供了实时物化视图功能,相较于普通(非实时)物化视图,实时物化视图无需手动调用刷新命令,即可实现数据更新时自动同步刷新物化视图。当基表发生变化时,构建在基表上的实时物化视图将会自动更新。AnalyticDB PostgreSQL企业数据智能平台是构建数据智能的全流程平台,提供可视化实时任务开发 + 实时数据洞察,让您轻松平移离线任务,使用SQL和简单配置即可完成整个实时数仓的搭建。
144002 8
|
7月前
|
关系型数据库 Linux Shell
Centos系统上安装PostgreSQL和常用PostgreSQL功能
Centos系统上安装PostgreSQL和常用PostgreSQL功能

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版