Geometric Exclusion Constraints

The use case explored in this post is no longer relevant, as the same thing can be achieved with a range type and intersection operator. Nonetheless it is a notable technique.

At the moment I'm not entirely sure these are a good idea, but it's an interesting concept. A need arises from a need to put a unique constraint on a time frame scoped to a foreign key.

Consider modeling a system consisting of a parallelized work queue. A consumer thread can only process one job at a time, though there are potentially many consumers.

create table job 
 (consumer_id integer, started_at timestamptz, finished_at timestamptz)

Since you are storing a historical record, and care about the integrity of your data, you want to ensure that any record of a job does not have a time overlap for a given consumer id. Enter a geometric exclusion constraint.

The model is pretty simple, it's a plane where you have epoch time as an integer dimension (x) and discrete ids as a qualitative dimension (y). All work assignment time periods are thus horizontal lines and violate the constraint if there is overlap.

Below is my ms-paint masterpiece.

Exclusion
CREATE OR REPLACE FUNCTION 
job_box(worker_id int, started_at timestamptz, finished_at timestamptz)
  RETURNS BOX AS $$
    SELECT  BOX(POINT($1, extract(epoch from $2)), 
                POINT($1, extract(epoch from coalesce($3, now()))))
$$ LANGUAGE 'sql' IMMUTABLE;
ALTER TABLE jobs ADD CONSTRAINT one_active_assignment_only 
  EXCLUDE USING GIST (job_box(consumer_id, started_at, finished_at) WITH &&);
Hey no problem! Thanks RhodiumToad.