数据库实验手册系列:6 预定会议室、划分管辖区
digoal约 9 分钟文章开始前推荐2个学习环境:
1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》
2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》
标签
PostgreSQL , PolarDB , 数据库 , 教学
背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
《amd64 image》
ARM机器使用以下docker image:
《arm64 image》
业务场景1 介绍: 预定会议室
会议室预定系统最关键的几个点:
1、查询: 按位置、会议室大小、会议室设备(是否有投屏、电话会议、视频会议...)、时间段查询符合条件的会议室.
2、预定: 并写入已订纪录.
3、强约束: 防止同一个会议室的同一个时间片出现被多人预定的情况.
实现和对照
设计存储会议室元数据的表
create table tbl_mroom (
id int primary key,
pos int, -- 位置信息, 为了简化实验过程, 位置信息使用int, 不影响实验结果
rs int -- 资源信息, 为了简化实验过程, 资源信息使用int, 不影响实验结果
);
写入测试数据
insert into tbl_mroom select generate_series(1,1000), random()*1000, random()*10;
传统方法 设计和实验
存储已订会议室纪录表
create table tbl_mroom_log (
id serial primary key, -- 主键
mid int REFERENCES tbl_mroom(id), -- 会议室ID
tsb timestamp, -- 会议开始时间
tse timestamp, -- 会议结束时间
uid int -- 预定人ID
);
预定过程:
开启事务
begin;
查询符合条件的目标会议室ID, 随便造一个条件即可, 不影响实验结果.
select id from tbl_mroom where pos < 300 and rs=5;
锁定整张预定纪录表(连读请求都会被阻塞).
lock table tbl_mroom_log in ACCESS EXCLUSIVE mode nowait;
查询没有时间冲突的会议室
-- 假设用户想订ts1到ts2范围的会议室.
-- 满足时间无重叠可预定, 如果会议室压根没有被定过(不在tbl_mroom_log这个表里)也可以被预定.
select t1.id from tbl_mroom t1 where t1.pos < 300 and t1.rs=5 -- 符合条件的会议室
and not exists ( -- 排除有时间交叉的记录
select 1 from tbl_mroom_log t2 where t1.id=t2.mid and tsb>=ts1 and tse<=ts2
);
写入预定纪录
-- 用户从可预定的会议室选中一个, 写入预定的时间戳.
insert into tbl_mroom_log (mid,tsb,tse,uid) values (?, ts1, ts2, ?);
释放整表锁定
end; -- 结束事务时自动释放.
PolarDB|PG新方法1 设计和实验
使用时间范围类型+该类型的排他约束.
创建插件btree_gist
create extension btree_gist ;
存储已订会议室纪录表, 这里和传统方法不一样, 使用时间范围类型+该类型的排他约束.
create table tbl_mroom_log (
id serial primary key, -- 主键
mid int references tbl_mroom(id), -- 会议室ID
tsr tsrange, -- 时间范围类型, 表示会议开始和结束时间
uid int, -- 预定人ID
exclude using gist (mid with = , tsr with &&) -- 排他约束，同一个会议室，不允许有时间范围交叉的记录
); postgres=# \d tbl_mroom_log
Table "public.tbl_mroom_log"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-------------------------------------------
id | integer | | not null | nextval('tbl_mroom_log_id_seq'::regclass)
mid | integer | | |
tsr | tsrange | | |
uid | integer | | |
Indexes:
"tbl_mroom_log_pkey" PRIMARY KEY, btree (id)
"tbl_mroom_log_mid_tsr_excl" EXCLUDE USING gist (mid WITH =, tsr WITH &&)
Foreign-key constraints:
"tbl_mroom_log_mid_fkey" FOREIGN KEY (mid) REFERENCES tbl_mroom(id)
预定过程:
不需要开启事务来锁表, 因为有排他约束保障不可能出现预定的时间交叉问题.
查询符合条件的目标会议室ID, 随便造一个条件即可, 不影响实验结果.
select id from tbl_mroom where pos < 300 and rs=5;
查询没有时间冲突的会议室
-- 假设用户想订ts1到ts2范围的会议室.
-- 满足时间无重叠可预定, 如果会议室压根没有被定过(不在tbl_mroom_log这个表里)也可以被预定.
select t1.id from tbl_mroom t1 where t1.pos < 300 and t1.rs=5 -- 符合条件的会议室
and not exists ( -- 使用tsrange类型的操作符 && 来排除有时间交叉的记录
select 1 from tbl_mroom_log t2 where t1.id=t2.mid and t2.tsr && tsrange(ts1,ts2)
);
写入预定纪录
-- 用户从可预定的会议室选中一个, 写入预定的时间戳.
insert into tbl_mroom_log (mid,tsr,uid) values (?, tsrange(ts1, ts2), ?);
如果发生冲突会报错, 例如:
insert into tbl_mroom_log (mid,tsr,uid) values (1, tsrange('2023-08-26 09:00:00','2023-08-26 10:30:00'), 1);
下面这条插入会报错:
insert into tbl_mroom_log (mid,tsr,uid) values (1, tsrange('2023-08-26 08:30:00','2023-08-26 09:30:00'), 1);
ERROR: conflicting key value violates exclusion constraint "tbl_mroom_log_mid_tsr_excl"
DETAIL: Key (mid, tsr)=(1, ["2023-08-26 08:30:00","2023-08-26 09:30:00")) conflicts with existing key (mid, tsr)=(1, ["2023-08-26 09:00:00","2023-08-26 10:30:00")).
对照
使用传统方法为了实现强约束, 防止同一间会议室被多人预定重叠时间片, 必须先锁表, 堵塞其他人查询, 然后再判断是否有合适会议室, 然后写入纪录, 最后释放锁(必须等事务结束才会释放). 是读堵塞, 并发和效率非常低.
使用PolarDB|PG的时间范围类型, 排他约束, 轻松解决了强约束问题. 在写入时会使用索引保障强约束, 防止同一间会议室被多人预定重叠时间片.
业务场景2 介绍: 划分管辖区
除了时间范围有交叉, 空间也存在交叉. 例如在根据地域划分责任范围时, 不能存在交叉. 如国界、省界、市界等. 同一行政级的多边形不能有overlay的情况.
实现和对照
传统方法 设计和实验
和预定会议室一样, 需要锁全表(连读请求都会被阻塞), 然后检查是否有overlay, 没有再写入, 最后释放排他锁.
这里就不举例了.
PolarDB|PG新方法1 设计和实验
使用GIS类型的排他约束特性.
首先创建2个插件:
create extension IF NOT EXISTS postgis;
create extension IF NOT EXISTS btree_gist;
由于空间的相交判断有2种情况, 1种是判断2个多边形的bounding box是否橡胶, 这个会放大相交的概率.
如下:
-- 判断2个bounding box是否overlay
-- true, 2个多边形的bounding box有重叠.
select
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)'))
&&
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0.1,0.9 1,0 1,0 0.1)'))
;
另一种是判断2个多边形是否相交, 注意相邻也算橡胶, 例如一个长方形沿对角线分成的2个三角形, 这2个三角形共用一条边, 也算相交.
如下:
-- 判断2个非bounding box是否overlay
-- true, 有一条边重叠.
select
ST_Intersects(
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)'))
,
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 1,0 1,0 0)'))
); -- 判断2个非bounding box是否overlay
-- false, 完全不重叠
select
ST_Intersects(
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)'))
,
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0.1,0.9 1,0 1,0 0.1)'))
);
这个例子要求多边形不相交, 比较难办, 因为&&是判定bounding box是否相交的, 实际需要的是多边形是否实际相交.
所以需要自定义1个operator, 使用ST_Intersects来解决这个问题.
如下:
create operator ## (PROCEDURE = st_intersects, LEFTARG = geometry, rightarg = geometry, COMMUTATOR = ##); ALTER OPERATOR FAMILY gist_geometry_ops_2d USING gist add operator 15 ## (geometry, geometry) ;
-- 判断2个非bounding box是否overlay
-- true, 有一条边重叠.
select
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)'))
##
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 1,0 1,0 0)'))
;
-- 判断2个非bounding box是否overlay
-- false, 完全不重叠
select
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)'))
##
ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0.1,0.9 1,0 1,0 0.1)'))
;
创建表
drop table tbl_loc;
create table tbl_loc (
id serial primary key, -- 主键
level int, -- 地域级别
loc_geo geometry, -- 地域多边形
exclude using gist (level with = , loc_geo with ##) -- 排他约束，同一个级别，不允许有空间范围交叉的记录
); postgres=# \d+ tbl_loc
Table "public.tbl_loc"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
---------+----------+-----------+----------+-------------------------------------+---------+-------------+--------------+-------------
id | integer | | not null | nextval('tbl_loc_id_seq'::regclass) | plain | | |
level | integer | | | | plain | | |
loc_geo | geometry | | | | main | | |
Indexes:
"tbl_loc_pkey" PRIMARY KEY, btree (id)
"tbl_loc_level_loc_geo_excl" EXCLUDE USING gist (level WITH =, loc_geo WITH ##)
Access method: heap
写入数据, BUG出现, 实际并没有判断这个排他约束, 下面记录都可以被正常插入.
insert into tbl_loc (level, loc_geo) values (1, ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)')));
insert into tbl_loc (level, loc_geo) values (1, ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)'))); drop table tbl_loc;
create table tbl_loc (
id serial primary key, -- 主键
level int, -- 地域级别
loc_geo geometry, -- 地域多边形
exclude using gist (loc_geo with ##) -- 排他约束，同一个级别，不允许有空间范围交叉的记录
);
insert into tbl_loc (level, loc_geo) values (1, ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)')));
insert into tbl_loc (level, loc_geo) values (1, ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 0,1 1,0 0)')));
insert into tbl_loc (level, loc_geo) values (1, ST_MakePolygon( ST_GeomFromText('LINESTRING(0 0,1 1,0 1,0 0)')));
留个作业, 解决一下这个bug.
对照
知识点
范围类型: https://www.postgresql.org/docs/16/rangetypes.html
GIS 地理信息类型: https://postgis.net/docs/manual-3.4/geometry.html
排它约束: https://www.postgresql.org/docs/16/rangetypes.html#RANGETYPES-CONSTRAINT
bounding box: https://postgis.net/docs/manual-3.4/geometry_overlaps.html
operator: https://www.postgresql.org/docs/14/sql-createoperator.html
思考
如果一开始结构设计时使用了2个字段来存储范围, 而不是tsrange类型的字段, 应该如何实现排它约束?
除了范围有排他性, 还有什么排他性场景? 数值相等、空间重叠?
排他性的要求是不是等价互换的? 例如 "a 排他符 b" 等价于 "b 排他符 a".
GIS类型排它约束为什么是bound box的排他, 而不是实际的多边形边界?
参考
《用 PostgreSQL 的排他约束(exclude)实现 - 行政区不可跨界 约束, 会议室预定时间不交叉 约束 等.》
《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》
https://www.cybertec-postgresql.com/en/exclusion-constraints-in-postgresql-and-a-tricky-problem/
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: