用 PostgreSQL 的排他约束(exclude)实现 - 行政区不可跨界 约束, 会议室预定时间不交叉 约束 等.

作者

digoal

日期

2021-02-08

标签

PostgreSQL , 排他 , excluding , GiST

背景

201911/20191128_01.md 《PostgreSQL 约束延判(unique, primary key, foreign key, exclude) - deferrable》

201905/20190527_01.md 《PostgreSQL exclude 约束之 - 绑定一对一的关系》

201712/20171223_02.md 《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》

如果需要支持非range, geometry类型的排他, 需要使用btree_gist插件.

create extension btree_gist;

行政区划分例子

细化到乡镇层, 每个乡镇1条记录, 同时将上层(乡镇所属的县、地区/市、省)补齐到同一条记录中.

约束:

保证乡镇的多边形不跨界
保证县的多边形不跨界
保证地区/市的多边形不跨界
保证省的多边形不跨界

create table t_geo ( id int8 primary key, -- 主键 l1 int, -- 省 ID geo1 geometry, l2 int, -- 地区/市 ID geo2 geometry, l3 int, -- 县 ID geo3 geometry , geo4 geometry , -- 乡镇多边形 exclude using gist (l1 with <> , geo1 with &&) , -- 省不跨界 exclude using gist (l2 with <> , geo2 with &&) , -- 地区/市不跨界 exclude using gist (l3 with <> , geo3 with &&) , -- 县不跨界 exclude using gist (geo4 with &&) -- 每个乡镇一条记录, 乡镇不跨界 );

```
postgres=# insert into t_geo values (1,1,ST_MakePolygon( ST_GeomFromText('LINESTRING(75 29,77 29,77 29, 75 29)')));
INSERT 0 1

-- 不同的l1下, geo1不允许相交
postgres=# insert into t_geo values (2,2,ST_MakePolygon( ST_GeomFromText('LINESTRING(75 29,77 29,77 29, 75 29)')));
ERROR: conflicting key value violates exclusion constraint "t_geo_l1_geo1_excl"
DETAIL: Key (l1, geo1)=(2, 010300000001000000040000000000000000C052400000000000003D4000000000004053400000000000003D4000000000004053400000000000003D400000000000C052400000000000003D40) conflicts with existing key (l1, geo1)=(1, 010300000001000000040000000000000000C052400000000000003D4000000000004053400000000000003D4000000000004053400000000000003D400000000000C052400000000000003D40).
```

PS: 注意这里省,地区/市,县不跨界的前提是, 在不同的ID之间不跨界, 同一个ID里面可能会跨界. (例如同一个l1 id的不同记录, 无法约束必须使用不同的多边形)

-- 同一个l1下, geo1可以跨界(相交) postgres=# insert into t_geo values (3,1,ST_MakePolygon( ST_GeomFromText('LINESTRING(75 29,77 29,77 29, 75 29)'))); INSERT 0 1 postgres=# insert into t_geo values (4,1,ST_MakePolygon( ST_GeomFromText('LINESTRING(75 29,77 29,77 29, 75 29)'))); INSERT 0 1

怎么解决呢? 再加个exclude约束? 暂时不可行, 对应错误如下(解决办法, 把gist索引接口的=, ~=, ~~= 实现一组gist_geometry_ops_2d即可):

```
create table t_geo (
id int8 primary key, -- 主键
l1 int, -- 省 ID
geo1 geometry,
l2 int, -- 地区/市 ID
geo2 geometry,
l3 int, -- 县 ID
geo3 geometry ,
geo4 geometry , -- 乡镇多边形
exclude using gist (l1 with <> , geo1 with &&) , -- 省不跨界
exclude using gist (l2 with <> , geo2 with &&) , -- 地区/市不跨界
exclude using gist (l3 with <> , geo3 with &&) , -- 县不跨界
exclude using gist (geo4 with &&), -- 每个乡镇一条记录, 乡镇不跨界
exclude using btree (l1 with = , geo1 with <>) , -- l1相同时, geo1必须相同
exclude using btree (l2 with = , geo2 with <>) , -- l2相同时, geo2必须相同
exclude using btree (l3 with = , geo3 with <>) -- l3相同时, geo3必须相同
);

ERROR: 42725: operator is not unique: geometry <> geometry
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
LOCATION: op_error, parse_oper.c:720

-- OR

create table t_geo (
id int8 primary key, -- 主键
l1 int, -- 省 ID
geo1 geometry,
l2 int, -- 地区/市 ID
geo2 geometry,
l3 int, -- 县 ID
geo3 geometry ,
geo4 geometry , -- 乡镇多边形
exclude using gist (l1 with <> , geo1 with &&) , -- 省不跨界
exclude using gist (l2 with <> , geo2 with &&) , -- 地区/市不跨界
exclude using gist (l3 with <> , geo3 with &&) , -- 县不跨界
exclude using gist (geo4 with &&), -- 每个乡镇一条记录, 乡镇不跨界
exclude using gist (geo1 with ~=, l1 with <>) , -- geo1相同时, l1必须相同
exclude using gist (geo1 with ~=, l1 with <>) , -- geo2相同时, l2必须相同
exclude using gist (geo1 with ~=, l1 with <>) -- geo3相同时, l3必须相同
);

ERROR: 42809: operator ~=(geometry,geometry) is not commutative
DETAIL: Only commutative operators can be used in exclusion constraints.
LOCATION: ComputeIndexAttrs, indexcmds.c:1861
```

同一条记录里的约束, geo1必须包含geo2, geo2包含geo3, geo3包含geo4:

```
geo1 ~ geo2

geo2 ~ geo3

geo3 ~ geo4
```

create table t_geo ( id int8 primary key, -- 主键 l1 int, -- 省 ID geo1 geometry, l2 int, -- 地区/市 ID geo2 geometry, l3 int, -- 县 ID geo3 geometry , geo4 geometry , -- 乡镇多边形 exclude using gist (l1 with <> , geo1 with &&) , -- 省不跨界 exclude using gist (l2 with <> , geo2 with &&) , -- 地区/市不跨界 exclude using gist (l3 with <> , geo3 with &&) , -- 县不跨界 exclude using gist (geo4 with &&), -- 每个乡镇一条记录, 乡镇不跨界 check (geo1 ~ geo2), check (geo2 ~ geo3), check (geo3 ~ geo4) );

PS: 注意采用每条补齐省,地区/市,县的多边形的表结构设计, 如果要修改省,地区/市,县的多边形时, 需要一次改多条记录. 所以还可使用分离结构如下:

```
create table t_geo_l1 (
id int8 primary key, -- 主键 省 ID
geo1 geometry,
exclude using gist (geo1 with &&) -- 每个乡镇一条记录, 乡镇不跨界
);

create table t_geo_l2 (
id int8 primary key, -- 主键 地区/市 ID
geo2 geometry,
exclude using gist (geo2 with &&) -- 每个乡镇一条记录, 乡镇不跨界
);

create table t_geo_l3 (
id int8 primary key, -- 主键 县 ID
geo3 geometry ,
exclude using gist (geo3 with &&) -- 每个乡镇一条记录, 乡镇不跨界
);

create table t_geo (
id int8 primary key, -- 主键
l1 int references t_geo_l1 (id), -- 省 ID
l2 int references t_geo_l2 (id), -- 地区/市 ID
l3 int references t_geo_l3 (id), -- 县 ID
geo4 geometry , -- 乡镇多边形
exclude using gist (geo4 with &&) -- 每个乡镇一条记录, 乡镇不跨界
);
```

会议室预定例子

参考如上链接.

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

文档

https://www.postgresql.org/docs/13/sql-createtable.html#SQL-CREATETABLE-EXCLUDE

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.