Range Types

Updated patch attached.

Changes:

1. Now supports new constructor scheme. If you define a range foorange,
you get the following constructors:
* foorange() -- produces empty foorange
* foorange(S) -- produces singleton range [S]
* foorange(L,B) -- produces range [L,B)
* foorange(L,B,'(]') -- produces range (L,B]

Actually, the two-argument form uses a special "default_flags" that can
be specified at creation time, and that defaults to '[)'.

The way I accomplish this is by generating 4 functions at definition
time -- a little ugly, and I am open to suggestions. I ran into a
problem using the default argument as Robert suggested because
pg_node_tree doesn't have a working input function (intentionally so),
so I couldn't get the built-in range types to work with initdb. The
constructors all essentially point to the same C function, aside from
some indirection that I did to avoid excessive complaining from the
opr_sanity test. Again, suggestions welcome.

2. Documentation has been updated.

3. Now there's support for multiple range types over a single base type,
e.g. two text ranges using different collations.

TODO:

There is still some cleanup to do, e.g. pg_dump. I'd like to get some
feedback to stabilize the user-facing behavior before I put too much
effort into the code cleanup (which shouldn't take long, but I just
don't want to work toward a moving target).

Regards,
Jeff Davis

Another updated patch is attached.

Changes:

* support for send/recv
* significant cleanup and fixes
* test improvements

TODO:

* pg_dump support. This requires outputting collation names and
opclass names (because those are part of the range type definition).
Currently, that's only done for indexes through special functions in
ruleutils.c. Should I define such functions there as well, or is there a
simpler approach? Also, I have to filter out the generated constructor
functions because those are created internally when defining a new range
type.
* some error messages should be improved
* Originally, I wasn't sure whether to define a RangeCoerceExpr
(similar to ArrayCoerceExpr), because the only use I had was for typmod.
But that is necessary for casts as well, so I'll go ahead and do that
and we'll get both casts and typmod for range types.
* I think I should avoid some syscache lookups for some of the generic
range functions. Right now they are done for every invocation, but it
would be pretty simple to avoid lookups when looking up the same range
type as last time.

Review questions:

* Do we like the new constructor behavior from the users' standpoint?
* Right now, the patch accomplishes that behavior by generating
several constructor functions every time a new range type is defined. Is
that acceptable? Is there a better way?

Regards,
Jeff Davis

On Tue, September 13, 2011 10:41, Jeff Davis wrote:

Another updated patch is attached.

Hi,

Below are 2 changes. The first change is an elog saying 'lower' instead of 'upper'.

The second change is less straightforward, but I think it should be changed too:

Rangetypes as it stands uses NULL to indicate INF or -INF:

select int4range(2, NULL);

int4range
------------
[ 2, INF )
(1 row)

but refuses to accept it in the string-form:

select '[ 2 , NULL )'::int4range;
ERROR: NULL range boundaries are not supported
LINE 1: select '[ 2 , NULL )'::int4range;
^

Second part below changes that to accept NULL for INF and -INF
in the string-form construction. (not complete: it still is
case-sensitive).

Thanks,

Erik Rijkers

--- src/backend/utils/adt/rangetypes.c.orig  2011-09-18 12:35:29.000000000 +0200
+++ src/backend/utils/adt/rangetypes.c      2011-09-18 16:03:34.000000000 +0200
@@ -387,7 +387,7 @@
        if (empty)
                elog(ERROR, "range is empty");
        if (upper.infinite)
-               elog(ERROR, "range lower bound is infinite");
+               elog(ERROR, "range upper bound is infinite");

PG_RETURN_DATUM(upper.val);
}
@@ -1579,9 +1579,9 @@
fl = RANGE_EMPTY;

        if (!lb_quoted && strncmp(lb, "NULL", ilen) == 0)
-               elog(ERROR, "NULL range boundaries are not supported");
+               fl |= RANGE_LB_INF;
        if (!ub_quoted && strncmp(ub, "NULL", ilen) == 0)
-               elog(ERROR, "NULL range boundaries are not supported");
+               fl |= RANGE_UB_INF;
        if (!lb_quoted && strncmp(lb, "-INF", ilen) == 0)
                fl |= RANGE_LB_INF;
        if (!ub_quoted && strncmp(ub, "INF", ilen) == 0)

On Sun, 2011-09-18 at 18:08 +0200, Erik Rijkers wrote:

Below are 2 changes. The first change is an elog saying 'lower' instead of 'upper'.

Done, thank you. New patch attached.

Changes:
* documentation fixes
* added document for pg_range catalog
* cleaned up errors, increased error checking
* improved pg_dump

TODO:
* Support casts and typmod.
- This requires adding a RangeCoerceExpr, or possibly
overloading ArrayCoerceExpr somehow. This is likely to
require a lot of boilerplate code and a fairly large diff.
* Cache lookups better to avoid unnecessary SearchSysCache calls.
* I need to find a clean way to get the operator class name in pg_dump.

Rangetypes as it stands uses NULL to indicate INF or -INF:

select int4range(2, NULL);

int4range
------------
[ 2, INF )
(1 row)

but refuses to accept it in the string-form:

select '[ 2 , NULL )'::int4range;
ERROR: NULL range boundaries are not supported
LINE 1: select '[ 2 , NULL )'::int4range;

I think this might require more opinions. There is a trade-off here
between convenience and confusion: accepting NULL is convenient in the
constructors, because it avoids the need to have extra constructors just
for unbounded ranges; but could lead to confusion between NULL and INF
(which are not the same).

In the string form, it doesn't add any convenience to accept NULL; but
as you point out, it seems inconsistent without it.

Thoughts?

Regards,
Jeff Davis

hello

sorry for late assign to discussion.

I don't think so using NULL instead INF is a good idea.

Regards

Pavel Stehule

2011/9/19 Jeff Davis <pgsql@j-davis.com>:

On Mon, Sep 19, 2011 at 1:51 AM, Jeff Davis <pgsql@j-davis.com> wrote:

select '[ 2 , NULL )'::int4range;
ERROR:  NULL range boundaries are not supported
LINE 1: select '[ 2 , NULL )'::int4range;

I think this might require more opinions. There is a trade-off here
between convenience and confusion: accepting NULL is convenient in the
constructors, because it avoids the need to have extra constructors just
for unbounded ranges; but could lead to confusion between NULL and INF
(which are not the same).

I agree with this line of reasoning. I think we will be making pain
for ourselves if we need to invent a bunch more constructors just to
have a way of indicating an unbounded range, but OTOH I don't see any
compelling reason why the type input function needs to accept N-U-L-L.

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

On Sep19, 2011, at 15:33 , Robert Haas wrote:

On Mon, Sep 19, 2011 at 1:51 AM, Jeff Davis <pgsql@j-davis.com> wrote:

select '[ 2 , NULL )'::int4range;
ERROR: NULL range boundaries are not supported
LINE 1: select '[ 2 , NULL )'::int4range;

I think this might require more opinions. There is a trade-off here
between convenience and confusion: accepting NULL is convenient in the
constructors, because it avoids the need to have extra constructors just
for unbounded ranges; but could lead to confusion between NULL and INF
(which are not the same).

I agree with this line of reasoning. I think we will be making pain
for ourselves if we need to invent a bunch more constructors just to
have a way of indicating an unbounded range, but OTOH I don't see any
compelling reason why the type input function needs to accept N-U-L-L.

The one reason I can see in favour of supporting N-U-L-L there is
compatibility with arrays. I've recently had the questionable pleasure
of writing PHP functions to parse and emit our textual representations of
arrays, records, dates and timestamps. After that experience, I feel that
the number of similar-yet-slightly-different textual input output format
for non-primitive types is already excessive, and any further additions
should be modeled after some existing ones.

(And BTW, why in heavens sake, is date and time input and output
asymmetric for some DateStyle settings? Asymmetric like in you need to
send one format, but get back another...)

best regards,
Florian Pflug

On Mon, 2011-09-19 at 17:23 +0200, Florian Pflug wrote:

The one reason I can see in favour of supporting N-U-L-L there is
compatibility with arrays.

But arrays actually do store and produce NULLs; ranges don't.

I've recently had the questionable pleasure
of writing PHP functions to parse and emit our textual representations of
arrays, records, dates and timestamps. After that experience, I feel that
the number of similar-yet-slightly-different textual input output format
for non-primitive types is already excessive, and any further additions
should be modeled after some existing ones.

I'm not clear on how accepting "NULL" would really save effort. With
ranges, the brackets have an actual meaning (inclusivity), and empty
ranges have no brackets at all. So I don't think it's going to be easy
to write one function to parse everything.

What about binary formats?

Regards,
Jeff Davis

Robert Haas <robertmhaas@gmail.com> wrote:

Jeff Davis <pgsql@j-davis.com> wrote:

select '[ 2 , NULL )'::int4range;
ERROR: NULL range boundaries are not supported
LINE 1: select '[ 2 , NULL )'::int4range;

I think this might require more opinions. There is a trade-off
here between convenience and confusion: accepting NULL is
convenient in the constructors, because it avoids the need to
have extra constructors just for unbounded ranges; but could lead
to confusion between NULL and INF (which are not the same).

I agree with this line of reasoning. I think we will be making
pain for ourselves if we need to invent a bunch more constructors
just to have a way of indicating an unbounded range, but OTOH I
don't see any compelling reason why the type input function needs
to accept N-U-L-L.

FWIW, the existing semantics of NULL include not only "UNKNOWN" but
also "NOT APPLICABLE". It seems fairly natural to think of a range
as being unbounded if the boundary limit is "not applicable".

On a practical level, our shop is already effectively doing this.
We have several tables where part of the primary key is "effective
date" and there is a null capable "expiration date" -- with a NULL
meaning that no expiration date has been set. It would be nice to
be able to have a "generated column" function which used these two
dates to build a range for exclusion constraints and such.

-Kevin

On Mon, Sep 19, 2011 at 11:23 AM, Florian Pflug <fgp@phlo.org> wrote:

On Sep19, 2011, at 15:33 , Robert Haas wrote:

On Mon, Sep 19, 2011 at 1:51 AM, Jeff Davis <pgsql@j-davis.com> wrote:

select '[ 2 , NULL )'::int4range;
ERROR:  NULL range boundaries are not supported
LINE 1: select '[ 2 , NULL )'::int4range;

I think this might require more opinions. There is a trade-off here
between convenience and confusion: accepting NULL is convenient in the
constructors, because it avoids the need to have extra constructors just
for unbounded ranges; but could lead to confusion between NULL and INF
(which are not the same).

I agree with this line of reasoning.  I think we will be making pain
for ourselves if we need to invent a bunch more constructors just to
have a way of indicating an unbounded range, but OTOH I don't see any
compelling reason why the type input function needs to accept N-U-L-L.

The one reason I can see in favour of supporting N-U-L-L there is
compatibility with arrays. I've recently had the questionable pleasure
of writing PHP functions to parse and emit our textual representations of
arrays, records, dates and timestamps. After that experience, I feel that
the number of similar-yet-slightly-different textual input output format
for non-primitive types is already excessive, and any further additions
should be modeled after some existing ones.

Well, I'm not violently opposed to accepting NULL to mean an unbounded
range. The semantics of "no bound at all" and "an unknown bound"
(i.e. NULL) are pretty close, as Kevin also points out downthread.
But I think the way Jeff actually did it is OK, too. What I really
care about is that we don't talk ourselves into needing a zillion
constructor functions. Making things work with a single constructor
function seems to me to simplify life quite a bit, and allowing there
seems essential for that.

(I am also vaguely wondering what happens if if you have a text
range.... is (nubile, null) ambiguous?)

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

On Sep19, 2011, at 17:46 , Jeff Davis wrote:

On Mon, 2011-09-19 at 17:23 +0200, Florian Pflug wrote:

The one reason I can see in favour of supporting N-U-L-L there is
compatibility with arrays.

But arrays actually do store and produce NULLs; ranges don't.

Hm, yeah, granted. But OTOH, clients will very likely use NULL, null, nil
or something similar as a bound to represent unbounded ranges. And those
will probably already be mapped to SQL's NULL. So in practice, people will
think of unbounded ranges having the (lower or upper) bound NULL I think.

I've recently had the questionable pleasure
of writing PHP functions to parse and emit our textual representations of
arrays, records, dates and timestamps. After that experience, I feel that
the number of similar-yet-slightly-different textual input output format
for non-primitive types is already excessive, and any further additions
should be modeled after some existing ones.

I'm not clear on how accepting "NULL" would really save effort. With
ranges, the brackets have an actual meaning (inclusivity), and empty
ranges have no brackets at all. So I don't think it's going to be easy
to write one function to parse everything.

No, but more similar the format are the easier it gets to at least factor
the hairy parts of such a parser into a common subroutine. Assume that we
don't support NULL as an alias for INF. What would then be the result of

'[A,NULL)'::textrange?

Presumably, it'd be the same as textrange('A','NULL','[)'). Which think
is a bit surprising, since '[A,NULL]'::text[] produces ARRAY['A',NULL],
*NOT* ARRAY['A','NULL'].

BTW, we currently represent infinity for floating point values as
'Infinity', not 'INF'. Shouldn't we do the same for ranges, i.e. make

int4range(0,NULL,'[)')::text

return

'[0,Infinity)'?

best regards,
Florian Pflug

On Mon, 2011-09-19 at 11:00 -0500, Kevin Grittner wrote:

On a practical level, our shop is already effectively doing this.
We have several tables where part of the primary key is "effective
date" and there is a null capable "expiration date" -- with a NULL
meaning that no expiration date has been set. It would be nice to
be able to have a "generated column" function which used these two
dates to build a range for exclusion constraints and such.

Agreed, that's a good convenience argument for accepting NULL boundaries
in the constructors.

Underneath though, we don't use NULL semantics (because they don't make
sense for ranges -- in fact, avoiding the need to constantly
special-case NULLs is one of the reasons to use range types). So, we
want to avoid confusion where possible.

Regards,
Jeff Davis

On Mon, 2011-09-19 at 18:32 +0200, Florian Pflug wrote:

No, but more similar the format are the easier it gets to at least factor
the hairy parts of such a parser into a common subroutine. Assume that we
don't support NULL as an alias for INF. What would then be the result of

'[A,NULL)'::textrange?

I think that the range input should *parse* NULL in a similar way, but
reject it. So, to make it the range between two definite strings, you'd
do:

'[A,"NULL")'::textrange

which would be equal to textrange('A','NULL','[)'). Without the quotes,
it would detect the NULL, and give an error. Open to suggestion here,
though.

Presumably, it'd be the same as textrange('A','NULL','[)'). Which think
is a bit surprising, since '[A,NULL]'::text[] produces ARRAY['A',NULL],
*NOT* ARRAY['A','NULL'].

BTW, we currently represent infinity for floating point values as
'Infinity', not 'INF'. Shouldn't we do the same for ranges, i.e. make

int4range(0,NULL,'[)')::text

return

'[0,Infinity)'?

I'm open to that, if you think it's an improvement I'll do it (but we
should probably pick one identifiable string and stick with it). What
I'd like to avoid is adding to the NULL/infinity confusion.

Regards,
Jeff Davis

On Mon, 2011-09-19 at 12:26 -0400, Robert Haas wrote:

What I really
care about is that we don't talk ourselves into needing a zillion
constructor functions. Making things work with a single constructor
function seems to me to simplify life quite a bit, and allowing there
seems essential for that.

I think we pretty much all agree on that. However, you did see the note
about the difficulty of using default parameters in built-in functions,
right?

I ultimately ended up with 4 constructors, each with the same name but
0, 1, 2, and 3 parameters. Suggestions welcome.

(I am also vaguely wondering what happens if if you have a text
range.... is (nubile, null) ambiguous?)

There are a few ways to handle that. I would lean toward parsing the
NULL as a special keyword, and then rejecting it (does it matter if it's
upper case?).

Regards,
Jeff Davis

On Sep21, 2011, at 09:23 , Jeff Davis wrote:

On Mon, 2011-09-19 at 18:32 +0200, Florian Pflug wrote:

No, but more similar the format are the easier it gets to at least factor
the hairy parts of such a parser into a common subroutine. Assume that we
don't support NULL as an alias for INF. What would then be the result of

'[A,NULL)'::textrange?

I think that the range input should *parse* NULL in a similar way, but
reject it. So, to make it the range between two definite strings, you'd
do:

'[A,"NULL")'::textrange

which would be equal to textrange('A','NULL','[)'). Without the quotes,
it would detect the NULL, and give an error. Open to suggestion here,
though.

Hm, that seems like a reasonable compromise. As long as range types and
arrays agree on the same basic lexical rules regarding quoting and whitespace
(i.e. that spaces outside of double-quotes are non-significant, that keywords
like NULL and INF/Infinity are case-insensitive, ...) I'm happy I guess.

BTW, we currently represent infinity for floating point values as
'Infinity', not 'INF'. Shouldn't we do the same for ranges, i.e. make

int4range(0,NULL,'[)')::text

return

'[0,Infinity)'?

I'm open to that, if you think it's an improvement I'll do it (but we
should probably pick one identifiable string and stick with it). What
I'd like to avoid is adding to the NULL/infinity confusion.

I've thought about this some more, and came to realize that the question
here really is whether

floatrange(0, 'Infinity'::float, '[)')

and

floatrange(0, NULL, '[)')

are the same thing or not. If they're not, then obviously using "Infinity"
to represent omitted bounds is going to be very confusing. If they are,
then using "Infinity" seems preferable. Maybe boundaries should be restricted
to numeric float values (i.e. +/-Infinity and NaN should be rejected), though
I dunno if the range type infrastructure supports that. Thoughts?

best regards,
Florian Pflug

On Wed, Sep 21, 2011 at 3:29 AM, Jeff Davis <pgsql@j-davis.com> wrote:

On Mon, 2011-09-19 at 12:26 -0400, Robert Haas wrote:

What I really
care about is that we don't talk ourselves into needing a zillion
constructor functions.  Making things work with a single constructor
function seems to me to simplify life quite a bit, and allowing there
seems essential for that.

I think we pretty much all agree on that. However, you did see the note
about the difficulty of using default parameters in built-in functions,
right?

I ultimately ended up with 4 constructors, each with the same name but
0, 1, 2, and 3 parameters. Suggestions welcome.

(I am also vaguely wondering what happens if if you have a text
range.... is (nubile, null) ambiguous?)

There are a few ways to handle that. I would lean toward parsing the
NULL as a special keyword, and then rejecting it (does it matter if it's
upper case?).

Boy, that seems really weird to me. If you're going to do it, it
ought to be case-insensitive, but I think detecting the case only for
the purpose of rejecting it is probably a mistake. I mean, if
(nubile, nutty) is OK, then (nubile, null) and (null, nutty) don't
really seem like they ought to be any different. Otherwise, anyone
who wants to construct these strings programatically is going to need
to escape everything and always write ("cat","dog") or however you do
that, and that seems like an unnecessary imposition.

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

On Sep21, 2011, at 14:00 , Robert Haas wrote:

On Wed, Sep 21, 2011 at 3:29 AM, Jeff Davis <pgsql@j-davis.com> wrote:

On Mon, 2011-09-19 at 12:26 -0400, Robert Haas wrote:

(I am also vaguely wondering what happens if if you have a text
range.... is (nubile, null) ambiguous?)

There are a few ways to handle that. I would lean toward parsing the
NULL as a special keyword, and then rejecting it (does it matter if it's
upper case?).

Boy, that seems really weird to me. If you're going to do it, it
ought to be case-insensitive, but I think detecting the case only for
the purpose of rejecting it is probably a mistake. I mean, if
(nubile, nutty) is OK, then (nubile, null) and (null, nutty) don't
really seem like they ought to be any different.

But that's exactly how arrays behave too. '{null,nutty}' is interpreted
as ARRAY[NULL,'nutty'] while '{nubile,nutty}' is interpreted as
ARRAY['nubile','nutty'].

Otherwise, anyone
who wants to construct these strings programatically is going to need
to escape everything and always write ("cat","dog") or however you do
that, and that seems like an unnecessary imposition.

Unless you fully depart from what arrays you, you'll have to do that anyway
because leading and trailing spaces aren't considered to be significant in
non-quoted elements. In other words, '( cat , dog )' represents
textrange('cat', 'dog', '()'), *not* textrange(' cat ', ' dog ', '()').

Also, as long as we need to recognize at least one special value meaning
a non-existing bound ('INF' or 'Infinity' or whatever), I don't see a way
around the need for quotes in the general case. Well, expect making the
representation of

range(X, NULL, '[)') be '[X)',

the one of

range(NULL, X, '(]') be '(X]'

and the one of

range(NULL, NULL, '()') be '()',

but I'm not sure that's an improvement. And even if it was, you'd still
need to quote X if it contained one of "(",")","[","]" or ",". So most
client would probably still choose to quote unconditionally, instead of
detecting whether it was necessary or not.

best regards,
Florian Pflug

On Wed, Sep 21, 2011 at 8:41 AM, Florian Pflug <fgp@phlo.org> wrote:

Boy, that seems really weird to me.  If you're going to do it, it
ought to be case-insensitive, but I think detecting the case only for
the purpose of rejecting it is probably a mistake.  I mean, if
(nubile, nutty) is OK, then (nubile, null) and (null, nutty) don't
really seem like they ought to be any different.

But that's exactly how arrays behave too. '{null,nutty}' is interpreted
as ARRAY[NULL,'nutty'] while '{nubile,nutty}' is interpreted as
ARRAY['nubile','nutty'].

Oh. Well, never mind then.

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

Florian Pflug <fgp@phlo.org> writes:

On Sep21, 2011, at 14:00 , Robert Haas wrote:

Otherwise, anyone
who wants to construct these strings programatically is going to need
to escape everything and always write ("cat","dog") or however you do
that, and that seems like an unnecessary imposition.

Unless you fully depart from what arrays you, you'll have to do that anyway
because leading and trailing spaces aren't considered to be significant in
non-quoted elements. In other words, '( cat , dog )' represents
textrange('cat', 'dog', '()'), *not* textrange(' cat ', ' dog ', '()').

Keep in mind that the array I/O behavior is widely considered to suck.
When we defined the record I/O behavior, we did not emulate that
whitespace weirdness, nor a number of other weirdnesses. I would argue
that ranges ought to model their I/O behavior on records not arrays,
because that's not as much of a legacy syntax.

Also, as long as we need to recognize at least one special value meaning
a non-existing bound ('INF' or 'Infinity' or whatever), I don't see a way
around the need for quotes in the general case.

Right. In the record case, we used an empty string for NULL, and then
had to insist on quotes for actual empty strings.

regards, tom lane