PgSQL Exclude约束

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

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索引,因此无需手工创建了。