Re: WIP: generalized index constraints

On Mon, Jul 6, 2009 at 6:20 PM, Jeff Davis<pgsql(at)j-davis(dot)com> wrote:
> On Mon, 2009-07-06 at 17:02 +0100, Greg Stark wrote:
>> On Mon, Jul 6, 2009 at 4:57 PM, Jeff Davis<pgsql(at)j-davis(dot)com> wrote:
>> >
>> > Exactly, you already know my use case ;) My goal is a "temporal key",
>> > where you can't have overlapping intervals of time, e.g. the constraint
>> > "nobody can be two places at the same time".
>>
>> Incidentally to handle non-overlapping ranges you don't need GIST, you
>> can actually use a plain btree. Since there are no overlapping ranges
>> the ranges have a complete ordering and you can get that by just
>> sorting by either endpoint. To enforce the constraint you only have to
>> compare with the previous and following element in the btree.
>
> What if you have an entire index full of overlapping dead tuples, and a
> few live ones? How would search work?

I should clarify I didn't mean you could implement it in SQL using
Postgres btrees. I just meant that a tree data structure was
sufficient, you don't need the power of GIST. It's probably easier to
implement it in GIST in Postgres since it's there though.

So it would work just like regular btrees, you only consider it a
conflict if there's a live value that conflicts.

--
greg
http://mit.edu/~gsstark/resume.pdf