Обсуждение: empty range
Hello,
default constructor for ranges use lower bound closed '[' and upper bound open ')'. This is correct behavior, but when upper bound is same like lower bound then range is empty. Mathematically is correct again - but in database is lost information about range bounds (lower/upper is NULL). To prevent this sitiuation we must have check if lower and upper argument is same and add some 0.00001s to upper range or use another constructor like tstzrange(now(),now(),'[]') .
Is there chance to change behavior of storing ranges? Its possible store range bounds in internal structure and lower(tstzrange(now(),now())) show not NULL value or change default behavior tstzrange(timestamptz,timestamptz) - if both args are same, then store as '[]', else '[)' and only tstzrange(timestamptz,timestamtz,'[)') and tstzrange(timestamptz,timestamtz,'()') store empty range.
It's only suggestion, i don't now if somebody wants store empty range without bounds.
We must have some checks to prevent storing empty values on every place where can occur this empty range, becouse we don't want lose bound information.
Best regards,
--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava
tel.: +420 591 166 224
fax: +420 596 621 273
mobil: +420 732 589 152
www.linuxbox.cz
mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------
default constructor for ranges use lower bound closed '[' and upper bound open ')'. This is correct behavior, but when upper bound is same like lower bound then range is empty. Mathematically is correct again - but in database is lost information about range bounds (lower/upper is NULL). To prevent this sitiuation we must have check if lower and upper argument is same and add some 0.00001s to upper range or use another constructor like tstzrange(now(),now(),'[]') .
Is there chance to change behavior of storing ranges? Its possible store range bounds in internal structure and lower(tstzrange(now(),now())) show not NULL value or change default behavior tstzrange(timestamptz,timestamptz) - if both args are same, then store as '[]', else '[)' and only tstzrange(timestamptz,timestamtz,'[)') and tstzrange(timestamptz,timestamtz,'()') store empty range.
It's only suggestion, i don't now if somebody wants store empty range without bounds.
We must have some checks to prevent storing empty values on every place where can occur this empty range, becouse we don't want lose bound information.
Best regards,
--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava
tel.: +420 591 166 224
fax: +420 596 621 273
mobil: +420 732 589 152
www.linuxbox.cz
mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------
> It's only suggestion, i don't now if somebody wants store empty range without bounds. I thought about the same while developing the BRIN inclusion operator class. I am not sure how useful empty ranges are in practice, but keeping their bound would only bring more flexibility, and eliminate special cases on most of the range operators. For reference, we allow empty boxes, and none of the geometric operators has to handle them specially.
Emre Hasegeli <emre@hasegeli.com> writes:
>> It's only suggestion, i don't now if somebody wants store empty range without bounds.
> I thought about the same while developing the BRIN inclusion operator
> class. I am not sure how useful empty ranges are in practice, but
> keeping their bound would only bring more flexibility, and eliminate
> special cases on most of the range operators. For reference, we allow
> empty boxes, and none of the geometric operators has to handle them
> specially.
I think it'd just move the special cases somewhere else. Consider
regression=# select int4range(4,4) = int4range(5,5);
?column?
----------
t
(1 row)
How do you preserve that behavior ... or if you don't, how much
damage does that do to the semantics of ranges? Right now there's
a pretty solid set-theoretic basis for understanding what a range is,
ie two ranges are the same if they include the same sets of elements.
It seems like that goes out the window if we don't consider that
all empty ranges are the same.
BTW, I think the main reason for all the bound-normalization pushups
is to try to have a rule that ranges that are set-theoretically equal
will look the same. That also goes out the window if we make
empty ranges look like this.
regards, tom lane
> regression=# select int4range(4,4) = int4range(5,5);
> ?column?
> ----------
> t
> (1 row)
Yes you have right, i don't realize this situation. But what about yust leave empty bit set to True and don't discard the input value - items can be sort and we can see the value - lower(int4range(4,4)) can be 4 without any damage. There is isempty function and i hope nobody check empty ranges with lower(anyrange) IS NULL AND upper(anyrange) IS NULL right?
Another thing that we did not know before was that is better store tstzrange(now(), NULL) instead of tstzrange(now,'infinity') becouse upper_inf is not True in this case and is not True if bounds are closed '[]'. Maybe we did not read documentation properly and we did not know how is range type stored internally and we was confused after discovering this behavior.
SELECT upper_inf(tstzrange(now(),'infinity','[]'));
upper_inf
-----------
f
(1 řádka)
Best regards
David T.
--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava
tel.: +420 591 166 224
fax: +420 596 621 273
mobil: +420 732 589 152
www.linuxbox.cz
mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------
Od: "Tom Lane" <tgl@sss.pgh.pa.us>
Komu: emre@hasegeli.com
Kopie: david.turon@linuxbox.cz, pgsql-hackers@postgresql.org
Datum: 16. 01. 2020 16:21
Předmět: Re: empty range
Emre Hasegeli <emre@hasegeli.com> writes:
>> It's only suggestion, i don't now if somebody wants store empty range without bounds.
> I thought about the same while developing the BRIN inclusion operator
> class. I am not sure how useful empty ranges are in practice, but
> keeping their bound would only bring more flexibility, and eliminate
> special cases on most of the range operators. For reference, we allow
> empty boxes, and none of the geometric operators has to handle them
> specially.
I think it'd just move the special cases somewhere else. Consider
regression=# select int4range(4,4) = int4range(5,5);
?column?
----------
t
(1 row)
How do you preserve that behavior ... or if you don't, how much
damage does that do to the semantics of ranges? Right now there's
a pretty solid set-theoretic basis for understanding what a range is,
ie two ranges are the same if they include the same sets of elements.
It seems like that goes out the window if we don't consider that
all empty ranges are the same.
BTW, I think the main reason for all the bound-normalization pushups
is to try to have a rule that ranges that are set-theoretically equal
will look the same. That also goes out the window if we make
empty ranges look like this.
regards, tom lane
> ?column?
> ----------
> t
> (1 row)
Yes you have right, i don't realize this situation. But what about yust leave empty bit set to True and don't discard the input value - items can be sort and we can see the value - lower(int4range(4,4)) can be 4 without any damage. There is isempty function and i hope nobody check empty ranges with lower(anyrange) IS NULL AND upper(anyrange) IS NULL right?
Another thing that we did not know before was that is better store tstzrange(now(), NULL) instead of tstzrange(now,'infinity') becouse upper_inf is not True in this case and is not True if bounds are closed '[]'. Maybe we did not read documentation properly and we did not know how is range type stored internally and we was confused after discovering this behavior.
SELECT upper_inf(tstzrange(now(),'infinity','[]'));
upper_inf
-----------
f
(1 řádka)
Best regards
David T.
--
-------------------------------------
Ing. David TUROŇ
LinuxBox.cz, s.r.o.
28. rijna 168, 709 01 Ostrava
tel.: +420 591 166 224
fax: +420 596 621 273
mobil: +420 732 589 152
www.linuxbox.cz
mobil servis: +420 737 238 656
email servis: servis@linuxbox.cz
-------------------------------------
Od: "Tom Lane" <tgl@sss.pgh.pa.us>
Komu: emre@hasegeli.com
Kopie: david.turon@linuxbox.cz, pgsql-hackers@postgresql.org
Datum: 16. 01. 2020 16:21
Předmět: Re: empty range
Emre Hasegeli <emre@hasegeli.com> writes:
>> It's only suggestion, i don't now if somebody wants store empty range without bounds.
> I thought about the same while developing the BRIN inclusion operator
> class. I am not sure how useful empty ranges are in practice, but
> keeping their bound would only bring more flexibility, and eliminate
> special cases on most of the range operators. For reference, we allow
> empty boxes, and none of the geometric operators has to handle them
> specially.
I think it'd just move the special cases somewhere else. Consider
regression=# select int4range(4,4) = int4range(5,5);
?column?
----------
t
(1 row)
How do you preserve that behavior ... or if you don't, how much
damage does that do to the semantics of ranges? Right now there's
a pretty solid set-theoretic basis for understanding what a range is,
ie two ranges are the same if they include the same sets of elements.
It seems like that goes out the window if we don't consider that
all empty ranges are the same.
BTW, I think the main reason for all the bound-normalization pushups
is to try to have a rule that ranges that are set-theoretically equal
will look the same. That also goes out the window if we make
empty ranges look like this.
regards, tom lane