跳过正文
  1. PostgreSQL大法师/

用 Exclude 实现互斥约束

·1713 字·4 分钟· ·
PostgreSQL PG开发 SQL
冯若航
作者
冯若航
Pigsty 创始人, @Vonng
目录

Exclude约束是一个PostgreSQL扩展,它可以实现一些更高级,更巧妙的的数据库约束。


前言
#

数据完整性是极其重要的,但由应用保证的数据完整性并不总是那么靠谱:人会犯傻,程序会出错。如果能通过数据库约束来强制数据完整性那是再好不过了:后端程序员不用再担心竞态条件导致的微妙错误,数据分析师也可以对数据质量充满信心,不需要验证与清洗。

关系型数据库通常会提供PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK约束,然而并不是所有的业务约束都可以用这几种约束表达。一些约束会稍微复杂一些,例如确保IP网段表中的IP范围不发生重叠,确保同一个会议室不会出现预定时间重叠,确保地理区划表中各个城市的边界不会重叠。传统上要实现这种保证是相当困难的:譬如UNIQUE约束就无法表达这种语义,CHECK与存储过程或者触发器虽然可以实现这种检查,但也相当tricky。PostgreSQL提供的EXCLUDE约束可以优雅地解决这一类问题。


Eclude约束的语法
#

 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子句定一个排除约束,它保证如果任意两行在指定列或表达式上使用指定操作符进行比较,不是所有的比较都将会返回TRUE。如果所有指定的操作符都测试相等,这就等价于一个UNIQUE约束,尽管一个普通的唯一约束将更快。不过,排除约束能够指定比简单相等更通用的约束。例如,你可以使用&&操作符指定一个约束,要求表中没有两行包含相互覆盖的圆(见 Section 8.8)。

排除约束使用一个索引实现,这样每一个指定的操作符必须与用于索引访问方法index_method的一个适当的操作符类(见Section 11.9)相关联。操作符被要求是交换的。每一个exclude_element可以选择性地指定一个操作符类或者顺序选项,这些在???中有完整描述。

访问方法必须支持amgettuple(见Chapter 61),目前这意味着GIN无法使用。尽管允许,但是在一个排除约束中使用 B-树或哈希索引没有意义,因为它无法做得比一个普通唯一索引更出色。因此在实践中访问方法将总是GiST或SP-GiST。

predicate允许你在该表的一个子集上指定一个排除约束。在内部这会创建一个部分索引。注意在为此周围的圆括号是必须的。


应用案例:会议室预定
#

假设我们想要设计一个会议室预定系统,并希望在数据库层面确保不会有冲突的会议室预定出现:即,对于同一个会议室,不允许同时存在两条预定时间范围上存在重叠的记录。那么数据库表可以这样设计:

-- PostgreSQL自带扩展,为普通类型添加GIST索引运算符支持
CREATE EXTENSION btree_gist;

-- 会议室预定表
CREATE TABLE meeting_room
(
    id      SERIAL PRIMARY KEY,
    user_id INTEGER,
    room_id INTEGER,
    range   tsrange,
    EXCLUDE USING GIST(room_id WITH = , range WITH &&)
);

这里EXCLUDE USING GIST(room_id WITH = , range WITH &&)指明了一个排它约束:不允许存在room_id相等,且range相互重叠的多条记录。

-- 用户1预定了101号房间,从早上10点到下午6点
INSERT INTO meeting_room(user_id, room_id, range) 
VALUES (1,101, tsrange('2019-01-01 10:00', '2019-01-01 18:00'));

-- 用户2也尝试预定101号房间,下午4点到下午6点
INSERT INTO meeting_room(user_id, room_id, range) 
VALUES (2,101, tsrange('2019-01-01 16:00', '2019-01-01 18:00'));

-- 用户2的预定报错,违背了排它约束
ERROR:  conflicting key value violates exclusion constraint "meeting_room_room_id_range_excl"
DETAIL:  Key (room_id, range)=(101, ["2019-01-01 16:00:00","2019-01-01 18:00:00")) conflicts with existing key (room_id, range)=(101, ["2019-01-01 10:00:00","2019-01-01 18:00:00")).

这里的EXCLUDE约束会自动创建一个相应的GIST索引:

"meeting_room_room_id_range_excl" EXCLUDE USING gist (room_id WITH =, range WITH &&)

应用案例:确保IP网段不重复
#

有一些约束是相当复杂的,例如确保表中的IP范围不发生重叠,类似的,确保地理区划表中各个城市的边界不会重叠。传统上要实现这种保证是相当困难的:譬如UNIQUE约束就无法表达这种语义,CHECK与存储过程或者触发器虽然可以实现这种检查,但也相当tricky。PostgreSQL提供的EXCLUDE约束可以优雅地解决这个问题。修改我们的geoips表:

create table geoips
(
  ips          inetrange,
  geo          geometry(Point),
  country_code text,
  region_code  text,
  city_name    text,
  ad_code      text,
  postal_code  text,
  EXCLUDE USING gist (ips WITH &&) DEFERRABLE INITIALLY DEFERRED 
);

​ 这里EXCLUDE USING gist (ips WITH &&) 的意思就是ips字段上不允许出现范围重叠,即新插入的字段不能与任何现存范围重叠(&&为真)。而DEFERRABLE INITIALLY IMMEDIATE 表示在语句结束时再检查所有行上的约束。创建该约束会自动在ips字段上创建GIST索引,因此无需手工创建了。

相关文章

Distinct On 去除重复数据
·837 字·2 分钟
PostgreSQL PG开发 SQL
使用Distinct On扩展字句快速找出分组内具有最大最小值的记录
函数易变性等级分类
·1180 字·3 分钟
PostgreSQL PG开发 函数
PgSQL中的函数默认有三种易变性等级,合理使用可以显著改善性能。
GO与PG实现缓存同步
·2389 字·5 分钟
PostgreSQL PG开发 触发器
巧妙运用Pg的Notify功能,可以方便地通知应用元数据变更,实现基于触发器的逻辑复制。
用触发器审计数据变化
·658 字·2 分钟
PostgreSQL PG开发 触发器
有时候,我们希望记录一些重要的元数据变更,以便事后审计之用。PostgreSQL的触发器就可以很方便地自动解决这一需求。
SQL实现ItemCF推荐系统
·3292 字·7 分钟
PostgreSQL PG开发 推荐系统
用PostgreSQL 5分钟实现一个最简单ItemCF推荐系统
UUID性质原理与应用
·3431 字·7 分钟
PostgreSQL PG开发 架构
UUID性质原理与应用,以及如何利用PostgreSQL的存储过程操作UUID。