Re: non-overlapping ranges constraint?

From: Holger Krug <hkrug(at)rationalizer(dot)com>
To: Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: non-overlapping ranges constraint?
Date: 2002-02-01 12:40:50
Message-ID: 20020201134050.A1245@dev12.rationalizer.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Picrate 2002-02-01 13:24:41 trigger-procedure without plpgsql
Previous Message Einar Karttunen 2002-02-01 12:37:51 Re: Name limit to 31 chars