Re: Range Types, discrete and/or continuous

2010/10/26 Robert Haas <robertmhaas(at)gmail(dot)com>:
> 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.

Yeah, window functions will require 'how to add or subtract value' of
ORDER BY expression data type to search window frame boundary when we
want to support RANGE BETWEEN frame option. I tried to retrieve the
information by hard-coding '+'/'-' to get it from pg_oper in the
previous patch, but actually we found out we need more general way
like above. This will help it. But I don't have blue-print of catalog
format for it yet, between knn gist and range types.

Regards,

--
Hitoshi Harada