How to declare a range overlaping constraint in PosgreSQL database?

Question

Let's say we are having a table with this definition:

range (
  id bigint primary key,
  colourId int references colour(id),
  smellId int references smell(id),
  from bigint,
  to bigint
)

This table is actually a reduced view over enormously big table:

item (
  id bigint primary key,
  colourId int references colour(id),
  smellId int references smell(id),
  CONSTRAINT item_colour_smell_unique UNIQUE (colour, smell, id)
)

I would like to translate item_colour_smell_unique constraint in the range table. It should watch overlaps of ranges [from, to] while taking acount of colourId and smellId column values.

Answer 1

This doesn't fully give you an answer but it sounds like you might want to make use of a trigger.

Answer 2

There's no standard "overlapping" constraint. You will have to build your own from some triggers. There has been discussion of this for 8.5 though.

You might find the "seg" module useful too. See the manuals - Appendix F. Additional Supplied Modules

Answer 3

Note that any trigger-based solution is inherently unsafe from race conditions, e.g. when two concurrent transactions insert a row with conflicting ranges, neither of them will see the other conflicting row, due to the "isolation" ACID property (only commited data can be seen).

Some solutions:

• Use procedures with explicit locking of the table to force serialization of inserts.
• Split the [from, to] range into [from, from+1, ..., to-1, to] and insert a row for each. This way you can use a simple UNIQUE INDEX on the "range" table.

PostgreSQL developer Jeff Davis has been writing about this lately and will implement range conflict constraints in PostgreSQL 8.5