Re: WIP: RangeTypes

On 28 January 2011 20:28, Thom Brown <thom(at)linux(dot)com> wrote:
> On 28 January 2011 07:45, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> Updated patch.
>>
>> Changes:
>>
>>  * Documentation for operators/functions
>>  * a comprehensive set of operators and functions
>>  * BTree opclass
>>  * Hash opclass
>>  * built-in range types:
>>    - PERIOD (timestamp)
>>    - PERIODTZ (timestamptz)
>>    - DATERANGE (date)
>>    - INTRANGE (int4)
>>    - NUMRANGE (numeric)
>>  * added subtype float function to the API, which will be useful for
>>    GiST
>>  * created canonical functions for intrange and daterange, so that:
>>      '[1,5]'::intrange = '[1,6)'::intrange
>>  * added length() function, written in SQL as:
>>      select upper($1) - lower($1)
>>    which uses polymorphic "-" operator to avoid the need to
>>    give the subtype subtract function and return type to the generic
>>    API
>>
>> Open items:
>>
>>  * More documentation work
>>  * Settle any representation/alignment concerns
>>  * Should the new length() function be marked as immutable, stable,
>>    or volatile? It uses the polymorphic "-" operator, and I suppose
>>    someone could define a non-immutable version of that before calling
>>    length(). Then again, it is likely to be inlined anyway, right?
>>  * GiST
>>    - docs
>>    - catalog work
>>    - implementation
>>  * typmod support (optional)
>>
>> This is nearing completion. GiST is by far the most amount of effort
>> remaining that I'm aware of. Comments about the API, naming,
>> representation, interface, funcationality, grammar, etc. are welcome.
>>
>> Regards,
>>        Jeff Davis
>
> Very nice work Jeff!
>
> This is not very graceful:
>
> postgres=#  CREATE TYPE numrange AS RANGE (SUBTYPE=numeric,
>   SUBTYPE_CMP=numeric_cmp);
> ERROR:  duplicate key value violates unique constraint
> "pg_range_rgnsubtype_index"
> DETAIL:  Key (rngsubtype)=(1700) already exists.
>
> Also, if I try the same, but with a different name for the type, I get
> the same error.  Why does that restriction exist?  Can't you have
> types which happen to use the exact same subtype?

Also, how do you remove a range type which coincides with a system
range type. For example:

postgres=# CREATE TYPE numrange AS RANGE (SUBTYPE=interval,
SUBTYPE_CMP=interval_cmp);
CREATE TYPE
postgres=# drop type numrange;
ERROR: cannot drop type numrange because it is required by the database system

Is this because I shouldn't have been able to create this type in the
first place?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935