Thread: Some feedback on range types

Some feedback on range types

From
Scott Bailey
Date:
I'm testing range types and I've come up with a couple of curiosities.

1) I'll start off easy. In the wild, discrete ranges tend to be
closed-closed [] while continuous ranges tend to be closed-open [). For
instance, on Tuesday stock traded at [28.34, 32.18] or Bob was employed
[2009-06-01, 2012-04-15] or Sally lived [1934, 2001]. But these ranges
are all converted to [). So Sally's tombstone ends up reading
[1934-2002). Not a huge deal, but it is difficult for users to change
this behavior.

2) Typemod doesn't work for subtypes. So say I'm working on a
stock-trading app and I want to create a numeric range with a base type
of numeric(8,2) and a granularity of 0.01.

    CREATE TYPE num_range AS RANGE (SUBTYPE = numeric(8,2));
    SELECT num_range(0.2, 2/3.0);
    -->  [0.2,0.66666666666666666667)

3) Continuing with the above example, I make a canonical function then
hack it in to the system catalog to temporarily get around the
chicken/egg problem mentioned earlier.

    CREATE OR REPLACE FUNCTION num_range_canonical(num_range)
    RETURNS num_range AS
    $$
        SELECT num_range(
            (CASE WHEN lower_inc($1) THEN lower($1)
            ELSE lower($1) + 0.01 END)::numeric(8,2),
            (CASE WHEN upper_inc($1) THEN upper($1)
            ELSE upper($1) - 0.01 END)::numeric(8,2),
            '[]');
    $$ LANGUAGE 'sql' IMMUTABLE STRICT;
However, the built in range types are automatically canonicalized while
a user created one is not, even with the canonical function set on the
type. Not a huge problem, but not an expected behavior either.

4) No editing in place. This is a problem when trying to create
functions that will work with anyrange. Some missing functionality was
the ability to do set difference when the first range extends on both
sides of the second. The function range_minus throws an exception in
that situation. So I set about to add the functions range_ldiff and
range_rdiff to pull out the left or right piece in this situation.
Because users can add any number of range types it would be very to
create a new instance of the correct type. It would be much easier to
just edit the upper or lower bounds of one of the input parameters. But
that doesn't seem to be supported.


Re: Some feedback on range types

From
Jeff Davis
Date:
On Wed, 2012-07-18 at 14:33 -0700, Scott Bailey wrote:
> I'm testing range types and I've come up with a couple of curiosities.
>
> 1) I'll start off easy. In the wild, discrete ranges tend to be
> closed-closed [] while continuous ranges tend to be closed-open [). For
> instance, on Tuesday stock traded at [28.34, 32.18] or Bob was employed
> [2009-06-01, 2012-04-15] or Sally lived [1934, 2001]. But these ranges
> are all converted to [). So Sally's tombstone ends up reading
> [1934-2002). Not a huge deal, but it is difficult for users to change
> this behavior.

I don't really have a good answer for this. We could supply alternate
output functions that allow you to specify how a discrete range is
displayed.

> 2) Typemod doesn't work for subtypes. So say I'm working on a
> stock-trading app and I want to create a numeric range with a base type
> of numeric(8,2) and a granularity of 0.01.
>
>     CREATE TYPE num_range AS RANGE (SUBTYPE = numeric(8,2));
>     SELECT num_range(0.2, 2/3.0);
>     -->  [0.2,0.66666666666666666667)

Will fix. I haven't gotten around to it yet; it's actually quite a bit
of code (unless I'm missing something).

> 3) Continuing with the above example, I make a canonical function then
> hack it in to the system catalog to temporarily get around the
> chicken/egg problem mentioned earlier.
>
>     CREATE OR REPLACE FUNCTION num_range_canonical(num_range)
>     RETURNS num_range AS
>     $$
>         SELECT num_range(
>             (CASE WHEN lower_inc($1) THEN lower($1)
>             ELSE lower($1) + 0.01 END)::numeric(8,2),
>             (CASE WHEN upper_inc($1) THEN upper($1)
>             ELSE upper($1) - 0.01 END)::numeric(8,2),
>             '[]');
>     $$ LANGUAGE 'sql' IMMUTABLE STRICT;
> However, the built in range types are automatically canonicalized while
> a user created one is not, even with the canonical function set on the
> type. Not a huge problem, but not an expected behavior either.

I assume that this isn't a problem when defining it in C using the
method mentioned in the other thread.

> 4) No editing in place. This is a problem when trying to create
> functions that will work with anyrange. Some missing functionality was
> the ability to do set difference when the first range extends on both
> sides of the second. The function range_minus throws an exception in
> that situation. So I set about to add the functions range_ldiff and
> range_rdiff to pull out the left or right piece in this situation.
> Because users can add any number of range types it would be very to
> create a new instance of the correct type. It would be much easier to
> just edit the upper or lower bounds of one of the input parameters. But
> that doesn't seem to be supported.

If I understand the problem correctly, it's a little more clear to solve
it with the C API. Specifically, the range_get_typcache() and
make_range() functions. You can see a similar pattern use in many of the
generic range functions defined in rangetypes.c, like range_union().

I agree it would be nice to make it easier to define new range type
functions with other PLs and not be so reliant on C.

I like the idea of having functions that return a range of the same type
but with some modification. Not quite update-in-place as you suggest,
but accomplishes the same thing.

Regards,
    Jeff Davis