skip to content

A Django recipe site books cooking-class kitchen stations; how do you stop two active bookings for one station overlapping in time with ExclusionConstraint?

level: seniorimportance: should knowfreq 30%

answer

  1. unique cannot express overlap
  2. a range column and operators
  3. GiST needs btree_gist for equality
  4. database error versus validation error
  5. validation alone still races

basics

~10 s

Add an ExclusionConstraint to Meta.constraints over a DateTimeRangeField with RangeOperators.OVERLAPS and the station with RangeOperators.EQUAL, conditioned on active bookings. PostgreSQL then rejects any overlapping insert or update with IntegrityError, even under concurrent requests.

solid answer

~30 s

Checking for overlaps in Python and then saving races: two requests both see a free slot and both insert. A `UniqueConstraint` cannot help, because it only compares for equality. Model the slot as a `DateTimeRangeField` and add `ExclusionConstraint(name=..., expressions=[('timespan', RangeOperators.OVERLAPS), ('station', RangeOperators.EQUAL)], condition=Q(cancelled=False))`. PostgreSQL enforces it with a GiST index by default, and equality on the plain `station_id` column needs the `btree_gist` extension, added with `BtreeGistExtension()` before the constraint's migration. Conflicting writes raise `IntegrityError`; `full_clean()` and `ModelForm` also run the constraint's `validate()`, which gives a friendly `ValidationError` but is only a pre-check, so still handle the `IntegrityError`.

code

python · 27 lines
python
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateTimeRangeField, RangeOperators
from django.db import models
from django.db.models import Q


class Station(models.Model):
    label = models.CharField(max_length=40)


class Booking(models.Model):
    station = models.ForeignKey(Station, on_delete=models.CASCADE)
    timespan = DateTimeRangeField()
    cancelled = models.BooleanField(default=False)

    class Meta:
        constraints = [
            ExclusionConstraint(
                name='booking_no_station_overlap',
                expressions=[
                    ('timespan', RangeOperators.OVERLAPS),
                    ('station', RangeOperators.EQUAL),
                ],
                condition=Q(cancelled=False),
                violation_error_message='That station is already booked for this time.',
            ),
        ]

go deeper

for a junior

Know that overlapping time slots cannot be prevented by a unique constraint, and that PostgreSQL offers exclusion constraints for it.

for a middle

Explain the expressions tuples, RangeOperators, the condition argument and why btree_gist is needed for the station column.

for a senior

Show the race in check-then-insert, handle IntegrityError alongside ValidationError, and plan the migration against existing overlapping data.

for a principal

Argue for pushing invariants into the database over application locks, and accept the PostgreSQL coupling that comes with it.

## The problem a unique constraint cannot solve A cooking-class booking holds a kitchen station for a time slot. The rule is 'no two active bookings for the same station may overlap'. Two naive answers fail: - **Check, then insert** in the view: query for overlapping bookings, and save if there are none. Two concurrent requests can both run the check before either inserts, and both succeed. - **`UniqueConstraint`** on station and start time: it only rejects equal values, so 18:00-20:00 and 19:00-21:00 pass. An **exclusion constraint** generalises uniqueness: no two rows may satisfy a set of operator comparisons at the same time. With 'station equal' and 'time range overlaps', the database itself rejects the second booking, whatever the concurrency. ## Declaring it in Django `ExclusionConstraint` lives in `django.contrib.postgres.constraints` and goes into `Meta.constraints`. All its arguments are keyword-only: | Argument | Meaning | |---|---| | `name` | required, the constraint name | | `expressions` | 2-tuples of (field or expression, operator); `RangeOperators` names the operators | | `index_type` | `'GiST'` (default), `'SPGiST'`, or `'Hash'` since Django 6.1 | | `condition` | a `Q` restricting the rows, such as active bookings only | | `deferrable` | `Deferrable.DEFERRED` or `Deferrable.IMMEDIATE` | | `include` | non-key columns for a covering index | | `violation_error_code`, `violation_error_message` | used in the `ValidationError` from model validation; the code option arrived in 5.0 | `RangeOperators.OVERLAPS` is `&&` and `RangeOperators.EQUAL` is `=`. Only commutative operators are allowed. ## The extension it needs GiST handles range overlap natively, but not equality on an ordinary integer column like `station_id`. The `btree_gist` extension adds that, so the migration history needs `BtreeGistExtension()` from `django.contrib.postgres.operations` before the migration that adds the constraint. Without it, creating the constraint fails in PostgreSQL. ## What happens on a conflict 1. **Database write.** An `INSERT` or `UPDATE` that would overlap an active booking raises `django.db.IntegrityError`. This is the guarantee, and it holds under any concurrency. 2. **Model validation.** `Model.full_clean()` calls `validate_constraints()`, and `ExclusionConstraint.validate()` runs a query for conflicting rows, raising `ValidationError` with your message and code. `ModelForm` calls this during form validation, so users see a normal form error. 3. **The gap between them.** Validation is a separate `SELECT` before the write, so two requests can both pass it; `save()` does not call `full_clean()` either. Catch `IntegrityError` around the save and turn it into the same user-facing message. ## Range bounds and two-column designs `DateTimeRangeField` defaults to `'[)'` bounds for values given as tuples: start included, end excluded, so an 18:00-19:00 booking and a 19:00-20:00 booking do not overlap. If the model stores `starts_at` and `ends_at` as separate columns, Django ships no ready-made range function: define a small `Func` subclass calling PostgreSQL's `TSTZRANGE`, and pass the columns plus `RangeBoundary()` as the expression in `expressions`. ## Forms and the admin A `DateTimeRangeField` gets a form field of the same name from `django.contrib.postgres.forms`, rendered with `RangeWidget` as two date-time inputs, one for each bound. It validates that the start does not come after the end before the constraint is ever consulted. When `ModelForm` validation runs the exclusion constraint and finds a conflict, the error is not attached to any one field, so it appears among the form's non-field errors with the `violation_error_message` you configured. Use that same text when translating a caught `IntegrityError`, so that a user who lost the race sees exactly the message a user who failed validation sees. Including the constraint name in the log line for the `IntegrityError` makes these conflicts easy to count later. ## Operational notes - Adding the constraint to an existing table fails if overlapping rows already exist; clean the data in an earlier migration. - A deferred exclusion constraint is checked at commit rather than per statement, which helps when a transaction moves bookings around, at a performance cost. - Cancelled bookings are excluded by `condition`, so cancelling frees the slot without deleting history. - Changing the rule later, for example to require a cleaning gap between classes, means removing and re-adding the constraint in a migration, and the new rule is checked against every existing row at that moment. - Store the bounds as timezone-aware datetimes (the default, since `USE_TZ` is `True`), so that evening classes and daylight-saving changes compare correctly in the `tstzrange` column. - Keep the rule in one place: once the database enforces it, delete any older Python overlap checks rather than maintaining two definitions that can drift apart.

  • Why does the ExclusionConstraint migration fail with an error about the operator class for the integer column?
    The constraint is built on a GiST index, and GiST has no built-in support for plain equality on an integer column such as `station_id`. The `btree_gist` extension provides it. Add `BtreeGistExtension()` in an earlier migration, or have an administrator create the extension if the role lacks privileges.
  • If ModelForm already reports the overlap, why catch IntegrityError as well?
    Form validation runs `ExclusionConstraint.validate()`, a separate `SELECT` before the `INSERT`. Two requests can both pass it and then both insert; PostgreSQL rejects the second with `IntegrityError`. Code paths that call `save()` or `create()` directly never run validation at all.

saying these in an interview costs you the question

  • A UniqueConstraint on station and start time prevents overlapping bookings.
  • full_clean() makes the overlap check safe under concurrent requests.
  • save() runs the ExclusionConstraint validation before writing.
  • ExclusionConstraint works on GiST with no extension for integer equality.
  • A booking ending at 19:00 always conflicts with one starting at 19:00.