作者
digoal
日期
2023-08-27
标签
PostgreSQL , PolarDB , 数据库 , 教学
背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker 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)实现 - 行政区不可跨界 约束, 会议室预定时间不交叉 约束 等.》
