PGCon2010 - Final Release III

PGCon 2010
The PostgreSQL Conference

Jeff Davis
Day Talks - 1 - 2010-05-20
Room DMS 1150
Start time 13:30
Duration 01:00
ID 201
Event type Lecture
Track Version 9.0
Language used for presentation English


Exclusion Constraints

UNIQUE is no longer unique among constraints. I authored "Exclusion Constraints" for PostgreSQL 8.5: a more general constraint mechanism that can enforce constraints such as "non-overlapping" as well as unique; and can enforce constraints on GiST or hash indexes as well as BTree. See why other constraint mechanisms are unsuitable for common business requirements -- like handling schedule conflicts -- and how the problems are solved by using Exclusion Constraints.

Exclusion Constraints are a more general constraint enforcement mechanism than UNIQUE; new in PostgreSQL 8.5. The constraints specify the conditions under which two tuples conflict, and concurrent updates are resolved with the same semantics as UNIQUE.

The existing UNIQUE constraints are a special case of Exclusion Constraints in which the two tuples conflict if all columns in the constraint are equal. Exclusion Constraints allow other operators to be specified. For instance, a reservation system may require that two tuples conflict if the room numbers are equal and the reservation periods overlap (as part of the demonstration, I make use of a user-defined PERIOD data type). Any operator can be specified as long as it is binary, boolean, commutative, and there's an operator class for the required index search (which is used to check for conflicts, much like the existing UNIQUE constraint mechanism).

Exclusion Constraints are important because they are easy, scalable, flexible and general to many different business needs. See why alternatives and workarounds all have serious problems and limitations, and how they are solved by using Exclusion Constraints.