Обсуждение: Why 'infinity' is not in range '[2019-01-02, infinity]'?

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.

On 4/29/19 12:12 PM, PG Doc comments form wrote:
> 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.

'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.


> 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.

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

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

Jonathan

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.

Eugen Konkov <kes-kes@yandex.ru> writes:
> if   you  allow I will suggest to map/convert 'infinity' value to
> 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

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
mechanismsare concerned. 

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


--
Best regards,
Eugen Konkov

On Sat, Oct 26, 2019 at 04:15:29PM +0300, Eugen Konkov wrote:
> 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)

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

> 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
mechanismsare concerned.
 
> 
> errr...   mechanism  of  date  ranges  violates basic rules  for  'Infinite
> (Unbounded) Ranges'?

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  <bruce@momjian.us>        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 +

!    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

On Wed, Nov  6, 2019 at 12:15:17PM +0200, Eugen Konkov wrote:
> !    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?

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

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +

On Wed, Nov  6, 2019 at 08:33:28AM -0500, Bruce Momjian wrote:
> On Wed, Nov  6, 2019 at 12:15:17PM +0200, Eugen Konkov wrote:
> > !    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?
> 
> My mistake.  Thanks for finding that.  Updated patch attached, plus I
> improved the second paragraph.

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

-- 
  Bruce Momjian  <bruce@momjian.us>        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 +