Subject: Why 'infinity' is not in range '[2019-01-02, infinity]'?

From: PG Doc comments form <[email-masked]>
Date: Mon, 29 Apr 2019 16:12:04 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/rangetypes.html
Description:

Hi.

May I read this:

But [today,infinity] means something different from [today,infinity) — the
latter excludes the special timestamp value infinity.

as 

But [today,infinity] means something different from [today,infinity) — the
**first includes** the special timestamp value infinity.

But previous paragraph says:

But note that these infinite values are never values of the range's element
type, and can never be part of the range. (So there is no such thing as an
inclusive infinite bound — if you try to write one, it will automatically be
converted to an exclusive bound.)

if 'infinity' can not be the part of a range this will mean:

[today,infinity] means same as [today,infinity)

errr... which one is correct? Please fix documentation.

PS. The problem I am faced into:
I am implementing be-temporal interface. I have current period. It can be
[2019-01-01,2019-02-01),
[2019-02-01,2019-03-01) and [2019-03-01,infinity) for the current month.

When I setup current_period to one of those I will get Orders at that
period. The Orders have with 'last_bill_date' field which will show the date
of Invoice for that Order.
For new Order we can set 'infinity' into 'last_bill_date' which will mean
that we should create Invoice.
Now to select all Order for which we should create Invoice we can write:

select * from "order" o where o.last_bill_date +interval o.bill_interval <@
current_period();

This seems obvious that infinity is part of [value, infinity) ( or
[value,infinity] ) range. (in other words the 'infinity' is always the part
of range with infinite inclusive or exclusive bound.

This is just view from my point, there can be things that I am not aware of.

---

From: "Jonathan S. Katz" <[email-masked]>
Date: Mon, 29 Apr 2019 13:14:25 -0400

> errr... which one is correct? Please fix documentation.

'infinity' in that case is a special type of date/timestamp:

https://www.postgresql.org/docs/current/datatype-datetime.html#id-1.5.7.13.19.8

which is what that paragraph is referring to. It's discussing
"infinity-like" objects you may see that are different than how range
types treat infinity.

I re-read the language, I would not advocate for making any changes.

> I am implementing be-temporal interface. I have current period. It can be

You should drop the 'infinity' and just let it be NULL, i.e.

daterange('2019-03-01', NULL);

Jonathan

---

From: Eugen Konkov <[email-masked]>
Date: Mon, 29 Apr 2019 21:22:16 +0300

1.
Also I found next ambiguous part:

    select upper_inf(  '["2018-08-14","Infinity")'::daterange );

Thanks  jstag  from  IRC for explanation that unbound and infinite are
different essence.

Thus, on the page
https://www.postgresql.org/docs/11/functions-range.html

lower_inf(anyrange)     boolean         is the lower bound infinite?    lower_inf('(,)'::daterange)     true
upper_inf(anyrange)     boolean         is the upper bound infinite?    upper_inf('(,)'::daterange)     true

should be spelled:

lower_inf(anyrange)     boolean         is the lower bound unbound?    lower_inf('(,)'::daterange)     true
upper_inf(anyrange)     boolean         is the upper bound unbound?    upper_inf('(,)'::daterange)     true

should not?

2.
I  do  not  know, it where are any sense to distinguish:
[ 2019-01-01, infinity ) and [ 2019-01-01, )

and because: https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPES-INFINITE

 This is equivalent to considering that the lower bound is “minus infinity”, or the upper bound is “plus infinity”, respectively

and because of next statement does not work:

 select '[2019-01-02,"infinity"]'::daterange @> 'infinity'::date;

if   you  allow I will suggest to map/convert 'infinity' value to
unbound range, for datatypes which defines 'infinity' value.
so these two become same:

    [ 2019-01-01, infinity ) and [ 2019-01-01, )

It seems more consistent in compare to current behavior.

---

From: Tom Lane <[email-masked]>
Date: Mon, 29 Apr 2019 17:30:55 -0400

> unbound range, for datatypes which defines 'infinity' value.

That was intentionally rejected in the original range types design,
and even if we thought that decision was wrong, it's too late to
change it now.

There's probably some merit in having the documentation avoid the
use of "infinity" when it really means "unbounded", but I'm not
sure we can avoid it altogether without being obscure.

			regards, tom lane

---

From: Eugen Konkov <[email-masked]>
Date: Sat, 26 Oct 2019 16:15:29 +0300

Today I got next ambiguous:

select tstzrange( 'infinity', null );
  tstzrange  
-------------
 [infinity,)
(1 row)

[DOC](https://www.postgresql.org/docs/12/rangetypes.html) stated:
>if the upper bound of the range is omitted, then all points greater than the lower bound are included in the range.
>This is equivalent to considering that the upper bound is “plus infinity”, respectively.

Thus I can write, can not?

select tstzrange( 'infinity', 'infinity' );
  tstzrange  
-------------
 empty
(1 row)

But thus it is not not equivalent.

>But note that these infinite values are never values of the range's element type, and can never be part of the range
Thus if 'infinite values are never values of the range' then

“infinity” can not be just another value of any range type
This conclusion contradicts next doc paragraph:
>Also, some element types have a notion of “infinity”, but that is just another value so far as the range type mechanisms are concerned.

errr...   mechanism  of  date  ranges  violates basic rules  for  'Infinite
(Unbounded) Ranges'?

-- 
Best regards,
Eugen Konkov

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 5 Nov 2019 23:21:11 -0500

> select tstzrange( 'infinity', null );

Well, that makes sense since your start/stop are the same, and there are
no values greater than the infinity you specified.

> errr...   mechanism  of  date  ranges  violates basic rules  for  'Infinite

Uh, yeah, those paragraphs need help.  You are right that the concept of
infinity in ranges is differnt than the range element type's possible
values of infinity, if it supports it, and the docs are unclear on that.

I have made an attempt at rewriting the paragraphs to clarify the
missing-boundry infinity from the possible range element type's
infinity, and I think I romoved the contradition, and clarified the
description.  Patch attached.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/rangetypes.sgml b/doc/src/sgml/rangetypes.sgml
new file mode 100644
index 3a034d9..c1c210e
*** a/doc/src/sgml/rangetypes.sgml
--- b/doc/src/sgml/rangetypes.sgml
*************** SELECT isempty(numrange(1, 5));
*** 131,159 ****
    <title>Infinite (Unbounded) Ranges</title>
  
    <para>
!    The lower bound of a range can be omitted, meaning that all points less
!    than the upper bound are included in the range. Likewise, if the upper
!    bound of the range is omitted, then all points greater than the lower bound
!    are included in the range. If both lower and upper bounds are omitted, all
!    values of the element type are considered to be in the range.
!   </para>
! 
!   <para>
!    This is equivalent to considering that the lower bound is <quote>minus
!    infinity</quote>, or the upper bound is <quote>plus infinity</quote>,
!    respectively.  But note that these infinite values are never values of
!    the range's element type, and can never be part of the range.  (So there
!    is no such thing as an inclusive infinite bound &mdash; if you try to
!    write one, it will automatically be converted to an exclusive bound.)
    </para>
  
    <para>
!    Also, some element types have a notion of <quote>infinity</quote>, but that
!    is just another value so far as the range type mechanisms are concerned.
!    For example, in timestamp ranges, <literal>[today,]</literal> means the same
!    thing as <literal>[today,)</literal>.  But <literal>[today,infinity]</literal> means
!    something different from <literal>[today,infinity)</literal> &mdash; the latter
!    excludes the special <type>timestamp</type> value <literal>infinity</literal>.
    </para>
  
    <para>
--- 131,156 ----
    <title>Infinite (Unbounded) Ranges</title>
  
    <para>
!    The lower bound of a range can be omitted, meaning that all
!    values less than the upper bound are included in the range, e.g.,
!    <literal>(,3]</literal>. Likewise, if the upper bound of the
!    range is omitted, then all values greater than the lower bound are
!    included in the range. If both lower and upper bounds are omitted,
!    all values of the element type are considered to be in the range.
!    Specifying a missing bound as exclusive is automatically converted
!    to inclusive, e.g., <literal>[,]</literal> is automatically converted
!    to <literal>(,)</literal>.  You can think of these missing values as
!    +/-infinity, but they are special range type values and are considerd
!    to be beyond any range element type's +/-infinity values.
    </para>
  
    <para>
!    Element types that have a notion of <quote>infinity</quote>
!    can use it as explicit bound values.  For example, for timestamp
!    ranges, <literal>[today,infinity)</literal> excludes the special
!    <type>timestamp</type> value <literal>infinity</literal>, while
!    <literal>[today,)</literal>, <literal>[today,]</literal>, and
!    <literal>[today,infinity]</literal> include it.
    </para>
  
    <para>

---

From: Eugen Konkov <[email-masked]>
Date: Wed, 6 Nov 2019 12:15:17 +0200

!    Specifying a missing bound as exclusive is automatically converted
!    to inclusive, e.g., <literal>[,]</literal> is automatically converted
!    to <literal>(,)</literal>

Misspell?

You say:
   exclusive is automatically converted  to inclusive
But then:
   [,] is automatically converted to (,)

which one is correct?

-- 
Best regards,
Eugen Konkov

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 6 Nov 2019 08:33:28 -0500

>    [,] is automatically converted to (,)

My mistake.  Thanks for finding that.  Updated patch attached, plus I
improved the second paragraph.

index 3a034d9..b75fb3a
  
    <para>
  
    <para>
  
    <para>
--- 131,156 ----
  
    <para>
!    The lower bound of a range can be omitted, meaning that all
!    values less than the upper bound are included in the range, e.g.,
!    <literal>(,3]</literal>. Likewise, if the upper bound of the range
!    is omitted, then all values greater than the lower bound are included
!    in the range. If both lower and upper bounds are omitted, all values
!    of the element type are considered to be in the range.  Specifying a
!    missing bound as inclusive is automatically converted to exclusive,
!    e.g., <literal>[,]</literal> is converted to <literal>(,)</literal>.
!    You can think of these missing values as +/-infinity, but they are
!    special range type values and are considered to be beyond any range
!    element type's +/-infinity values.
    </para>
  
    <para>
!    Element types that have the notion of <quote>infinity</quote> can
!    use them as explicit bound values.  For example, with timestamp
!    ranges, <literal>[today,infinity)</literal> excludes the special
!    <type>timestamp</type> value <literal>infinity</literal>,
!    while <literal>[today,infinity]</literal> include it, as does
!    <literal>[today,)</literal> and <literal>[today,]</literal>.
    </para>
  
    <para>

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 27 Dec 2019 14:33:59 -0500

> improved the second paragraph.

Patch applied back through 9.4.  Thank you for the feedback.