PGCon2010 - Final Release III
PGCon 2010
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Jeff Davis |
| Schedule | |
|---|---|
| Day | Talks - 1 - 2010-05-20 |
| Room | DMS 1150 |
| Start time | 13:30 |
| Duration | 01:00 |
| Info | |
| ID | 201 |
| Event type | Lecture |
| Track | Version 9.0 |
| Language used for presentation | English |
Not Just UNIQUE
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.