Implementing Mutual Exclusion Constraints with Exclude

Exclude constraint is a PostgreSQL extension that can implement more advanced and sophisticated database constraints.

Introduction

#

Data integrity is extremely important, but data integrity guaranteed by applications isn’t always reliable: humans make mistakes, programs have bugs. If data integrity can be enforced through database constraints, that would be ideal: backend programmers don’t need to worry about subtle errors caused by race conditions, and data analysts can be confident in data quality without needing validation and cleaning.

Relational databases typically provide PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK constraints, but not all business constraints can be expressed with these few constraint types. Some constraints are slightly more complex, such as ensuring IP network segments in an IP range table don’t overlap, ensuring the same conference room doesn’t have overlapping reservation times, ensuring geographical boundaries of different cities in an administrative division table don’t overlap. Traditionally implementing such guarantees is quite difficult: for instance, UNIQUE constraints cannot express this semantic, while CHECK with stored procedures or triggers can implement such checks but are quite tricky. PostgreSQL’s EXCLUDE constraint can elegantly solve this class of problems.

Exclude Constraint Syntax

#

 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 } ]

The EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified columns or expressions using the specified operators, not all comparisons will return TRUE. If all specified operators test for equality, this is equivalent to a UNIQUE constraint, although a normal unique constraint would be faster. However, exclusion constraints can specify more general constraints than simple equality. For example, you can use the && operator to specify a constraint requiring no two rows in the table contain overlapping circles (see Section 8.8).

Exclusion constraints are implemented using an index, so each specified operator must be associated with an appropriate operator class (see Section 11.9) for the index access method index_method. The operators are required to be commutative. Each exclude_element can optionally specify an operator class or ordering options, which are fully described in ???.

The access method must support amgettuple (see Chapter 61), which currently means GIN cannot be used. Although allowed, using B-tree or hash indexes in an exclusion constraint makes no sense, as they cannot do better than a normal unique index. Therefore in practice, the access method will always be GiST or SP-GiST.

The predicate allows you to specify an exclusion constraint on a subset of the table. Internally this creates a partial index. Note that surrounding parentheses are required for this.

Use Case: Conference Room Reservation

#

Suppose we want to design a conference room reservation system and ensure at the database level that no conflicting room reservations occur: that is, for the same conference room, we don’t allow two reservation records with overlapping time ranges to exist simultaneously. The database table can be designed like this:

-- Built-in PostgreSQL extension, adds GIST index operator support for common types
CREATE EXTENSION btree_gist;

-- Conference room reservation table
CREATE TABLE meeting_room
(
    id      SERIAL PRIMARY KEY,
    user_id INTEGER,
    room_id INTEGER,
    range   tsrange,
    EXCLUDE USING GIST(room_id WITH = , range WITH &&)
);

Here EXCLUDE USING GIST(room_id WITH = , range WITH &&) specifies an exclusion constraint: not allowing multiple records where room_id is equal and range overlaps.

-- User 1 reserves room 101, from 10 AM to 6 PM
INSERT INTO meeting_room(user_id, room_id, range) 
VALUES (1,101, tsrange('2019-01-01 10:00', '2019-01-01 18:00'));

-- User 2 also tries to reserve room 101, from 4 PM to 6 PM
INSERT INTO meeting_room(user_id, room_id, range) 
VALUES (2,101, tsrange('2019-01-01 16:00', '2019-01-01 18:00'));

-- User 2's reservation errors, violating the exclusion constraint
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")).

The EXCLUDE constraint automatically creates a corresponding GIST index:

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

Use Case: Ensuring IP Network Segments Don’t Overlap

#

Some constraints are quite complex, such as ensuring IP ranges in a table don’t overlap, similarly ensuring geographical boundaries of different cities in an administrative division table don’t overlap. Traditionally implementing such guarantees is quite difficult: for instance, UNIQUE constraints cannot express this semantic, while CHECK with stored procedures or triggers can implement such checks but are quite tricky. PostgreSQL’s EXCLUDE constraint can elegantly solve this problem. Modify our geoips table:

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 
);

Here EXCLUDE USING gist (ips WITH &&) means the ips field doesn’t allow overlapping ranges, i.e., newly inserted fields cannot overlap with any existing ranges (where && returns true). DEFERRABLE INITIALLY DEFERRED means check constraints on all rows when the statement ends. Creating this constraint automatically creates a GIST index on the ips field, so manual creation is not needed.