Re: Range Types, discrete and/or continuous

On Mon, Oct 25, 2010 at 8:13 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> On Mon, 2010-10-25 at 18:03 -0400, Robert Haas wrote:
>> Hmm.  Do you have some concrete examples of cases where a range type
>> might want to do some representational optimization?
>
> Let's say for instance you want to keep an timestamp range in 16 bytes.
> You could have an 8-byte timestamp, a 7-byte integer that represents the
> offset from that timestamp in microseconds, and one byte for flags (e.g.
> NULL or infinite boundaries, etc.). I'm not sure that you can make that
> representation work in a generic way.

See, that gets complicated, because now you're restricting the range
of values that can be expressed by the range type to something less
than the natural range of the data type. I am not sure the value of
supporting that is sufficient to justify the amount of extra code that
will be required to make it work. I'd say for a first version, nail
down the representation. Perhaps in a future version you could have
compress/uncompress methods sort of like GIST, but for a first cut it
seems highly desirable to be able to say something like:

CREATE TYPE int_range AS RANGE (BASETYPE = int);

I hear you complaining that we don't know the values you've called
dtype, cmpfunc, addfunc, and subfunc. It seems pretty reasonable to
extract cmpfunc, if unspecified, from the default btree opclass for
the data type. For the rest, I'm inclined to propose that we support
something like:

ALTER TYPE timestamptz
ADD INTERFACE increment timestamp_pl_interval(timestamptz, interval),
ADD INTERFACE decrement timestamp_mi_interval(timestamptz, interval);

or

ALTER TYPE integer
ADD INTERFACE increment int4pl (integer, integer),
ADD INTERFACE decrement int4mi (integer, integer),
ADD VALUE addative_unit 1::integer,
ADD VALUE addative_identity 0::integer;

IIRC, Window functions need this information too, so there's value in
associating it with the base type, even if we want to allow users to
override it when creating ranges.

> It's not critical, and perhaps not even desirable. But it crossed my
> mind because alignment might make a 17-byte type look like 24 bytes,
> which seems pretty wasteful to me.

There's no requirement that you set typalign='d'; it's just that if
you don't the entries may not be aligned. But it may be that that's a
small price to pay for shrinking the on-disk footprint.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company