Re: Proposal - temporal contrib module

On Thu, 2009-10-29 at 10:54 +0200, Heikki Linnakangas wrote:
> I presume you're going to need some backend support and possibly new
> syntax for some of the operations, right? That seems more urgent to
> discuss than the possible inclusion into contrib.

There are various areas that need work inside the backend:

* Semantics
1. Allow temporal keys -- this is accomplished via operator exclusion
constraints, which I hope can be committed in the next commit fest.
2. Allow postgres to understand types like PERIOD, so that it can
find important operators like "@>", "&&", "<<".

* Syntax Sugar
1. Temporal keys
2. Temporal FKs
3. Temporal Join
4. Creating a simple audit log
5. Possible PERIOD constructor syntax sugar?

* Performance
1. Modified merge join

And I believe the rest can be done using the existing type
infrastructure, i.e. as a contrib module. I think it makes a lot of
sense to discuss and develop the backend changes and PERIOD data type in
parallel.

> Hmm. Infinity feels like a better match. The behavior of length and set
> operations falls out of that naturally. For example, length of a period
> with an infinite beginning or end is infinite. For set operations, for
> example the intersection of [123, infinity] and [100, 160] would be
> [123, 160].

I agree. If TSQL-2 addresses NULL semantics clearly enough, we might
want to allow it. I think it will just cause confusion, however.

> I'd stick to your current definition that a period is a contiguous set
> of time. A non-contiguous set consists of multiple contiguous periods,
> so it can be represented as multiple rows in a table.

I think there is a lot of value in non-contiguous sets, but PERIOD is a
good start.

> It should also be kept in mind that although this class of problems are
> generally thought of as temporal issues, IOW dealing with time, the same
> approach works with ranges of integers or any other datatype with a
> well-defined sort order.

Agreed.

> It would be nice if the temporal data type
> would allow that too.

If I understand what you're saying, you're alluding to a type where you
can do things like:
RANGE(timestamptz)
which would be equivalent to a PERIOD.

Typmod almost provides enough flexibility, but it can't store a full
OID, so we'd need to get creative. There are probably some other issues
here as well, because the current type system isn't really designed for
this kind of thing. Do you have any ideas or guidance here?

Regards,
Jeff Davis