Re: non-overlapping ranges constraint?

On Fri, Feb 01, 2002 at 12:17:42PM +0100, Helge Bahmann wrote:
> This solves the probelm at hand but I have severe performance
> problems. Inserts/deletes on the table are rare, as are modifications
> to min and max, but the table is subject to mass-updates touching
> otherdata and it appears that the check constraint is executed even
> if neither min nor max are modified. Basically this turns updating
> into an O(n^2) operation, as neither min<= $2 nor max >= $1
> are particularly selective.

A maybe not elegant but performant solution:

Use a BEFORE INSERT/UPDATE TRIGGER, which checks if min resp. max were
modified and calls your range checking function only if necessary.

> My question is whether there is a more elegant solution; since the
> problem is essentially a geometric one, perhaps people storing
> geometric objects know a generic solution? Additional fact:
> in the "real" problem both min and max are of type timestamp.

Even if there is a more elegant solution, I would suppose to use a
BEFORE TRIGGER and not a CHECK constraint. The `more elegant'
solution, if any, would be based on certain sophisticated
indices. Index-based checks maybe would be more performant than your
simple check method, but no checks at all are even more performant ;-)

--
Holger Krug
hkrug(at)rationalizer(dot)com