Michael,
First off, mark me down as one of the people interested in having this ...
I've been hacking a lot of the same functionality using data-push functions
and triggers, and boy howdy, it's messy.
I do think Jim is right, though, in that we may want to look for portions of
the functionality which are achievable in the context of one PostgreSQL
version, unless you're going to be working full-time on this patch. Or
maybe queue it up for next summer's Summer of Code. Not sure what that
portion would be, though.
In real-world calendaring applications, I *certainly* see the need for a
successor function. However, that would require being able to define
timestamps with a variable precision, e.g. TIMESTAMP('5 minutes'). This, by
itself would be a significant effort, yet useful ... maybe that's where to
start?
You're probably going to have to give up on B-Tree indexes for PERIODs, and
look towards GiST. For one thing, I would see UNIQUE in the context of a
PERIOD defined as non-overlapping. e.g.:
Given
UNIQUE (name, period)
Name Period
Joe 2006-06-11 14:00:00->17:35:00
Marsha 2006-06-11 15:00:00->19:00:00
Ralph 2006-06-11 15:15:00->15:30:00
I would want (in a calendaring application) an attempt to insert:
Joe 2006-06-11 17:00:00->19:00:00
... to fail, as well as any attempt to:
UPDATE periods SET name = 'Marsha' WHERE name = 'Ralph';
... although it's possible that UNIQUE is not really the right name for that
kind of constraint, although it serves the same purpose. But I don't think
in this context that a B-Tree would be capable of fulfilling it.
--
Josh Berkus
PostgreSQL @ Sun
San Francisco