unnesting multirange data types

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

I would like to decompose the returned multirange into its individual
ranges, similarly to how I would "unnest" an array:

+1 for adding such a feature, but I suppose it's too late for v14.

AFAICS, "unnest(anymultirange) returns setof anyrange" could coexist
alongside the existing variants of unnest(), so I don't see any
fundamental stumbling block to having it.

regards, tom lane

On 6/9/21 3:25 PM, Tom Lane wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

I would like to decompose the returned multirange into its individual
ranges, similarly to how I would "unnest" an array:

+1 for adding such a feature, but I suppose it's too late for v14.

Well, the case I would make for v14 is that, as of right now, the onus
is on the driver writers / application developers to be able to unpack
the multiranges.

Maybe it's not terrible as of this moment -- I haven't tried testing it
that far yet -- but it may make it a bit more challenging to work with
these types outside of Postgres. I recall a similar issue when initially
trying to integrate range types into my apps back in the v9.2 days, and
I ended up writing some grotty code to handle it. Yes, I worked around
it, but I preferably wouldn't have had to.

An "unnest" at least lets us bridge the gap a bit, i.e. if you really
need to introspect a multirange type, you have a way of getting it into
a familiar format.

I haven't tried manipulating a multirange in a PL like Python, maybe
some exploration there would unveil more or less pain, or if it could be
iterated over in PL/pgSQL (I'm suspecting no).

That all said, for writing queries within Postgres, the multiranges make
a lot of operations way easier. I do think a missing "unnest" function
does straddle the line of "omission" and "new feature," so I can
understand if it does not make it into v14.

AFAICS, "unnest(anymultirange) returns setof anyrange" could coexist
alongside the existing variants of unnest(), so I don't see any
fundamental stumbling block to having it.

Cool. I was initially throwing out "unnest" as the name as it mirrors
what we currently have with arrays, and seems to be doing something
similar. Open to other names, but this was the one that I was drawn to.
"multirange" is an "ordered array of ranges" after all.

Thanks,

Jonathan

On 6/9/21 3:44 PM, Jonathan S. Katz wrote:

On 6/9/21 3:25 PM, Tom Lane wrote:

"Jonathan S. Katz" <jkatz@postgresql.org> writes:

I would like to decompose the returned multirange into its individual
ranges, similarly to how I would "unnest" an array:

+1 for adding such a feature, but I suppose it's too late for v14.

Well, the case I would make for v14 is that, as of right now, the onus
is on the driver writers / application developers to be able to unpack
the multiranges.

I haven't tried manipulating a multirange in a PL like Python, maybe
some exploration there would unveil more or less pain, or if it could be
iterated over in PL/pgSQL (I'm suspecting no).

I did a couple more tests around this.

As suspected, in PL/pgSQL, there is no way to unpack or iterate over a
multirange type.

In PL/Python, both range types and multirange types are treated as
strings. From there, you can at least ultimately parse and manipulate it
into your preferred Python types, but this goes back to my earlier point
about putting the onus on the developer to do so.

Thanks,

Jonathan

On 2021-Jun-09, Jonathan S. Katz wrote:

I did a couple more tests around this.

As suspected, in PL/pgSQL, there is no way to unpack or iterate over a
multirange type.

Uh. This is disappointing; the need for some way to unnest or unpack a
multirange was mentioned multiple times in the range_agg thread. I had
assumed that there was some way to cast the multirange to a range array,
or somehow convert it, but apparently that doesn't work.

If the supporting pieces are mostly there, then I opine we should add
something.

--
�lvaro Herrera 39�49'30"S 73�17'W
"Hay dos momentos en la vida de un hombre en los que no deber�a
especular: cuando puede permit�rselo y cuando no puede" (Mark Twain)

On 6/9/21 4:56 PM, Alvaro Herrera wrote:

On 2021-Jun-09, Jonathan S. Katz wrote:

I did a couple more tests around this.

As suspected, in PL/pgSQL, there is no way to unpack or iterate over a
multirange type.

Uh. This is disappointing; the need for some way to unnest or unpack a
multirange was mentioned multiple times in the range_agg thread. I had
assumed that there was some way to cast the multirange to a range array,
or somehow convert it, but apparently that doesn't work.

Just to be pedantic with examples:

SELECT datemultirange(
daterange(current_date, current_date + 2),
daterange(current_date + 5, current_date + 7))::daterange[];

ERROR: cannot cast type datemultirange to daterange[]
LINE 1: ...2), daterange(current_date + 5, current_date + 7))::daterang...

IF there was an array to cast it into an array, we could then use the
array looping construct in PL/pgSQL, but if we could only choose one, I
think it'd be more natural/less verbose to have an "unnest".

If the supporting pieces are mostly there, then I opine we should add
something.

Agreed.

Jonathan

Hi, all!

On Thu, Jun 10, 2021 at 2:00 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:

On 6/9/21 4:56 PM, Alvaro Herrera wrote:

On 2021-Jun-09, Jonathan S. Katz wrote:

I did a couple more tests around this.

As suspected, in PL/pgSQL, there is no way to unpack or iterate over a
multirange type.

Uh. This is disappointing; the need for some way to unnest or unpack a
multirange was mentioned multiple times in the range_agg thread. I had
assumed that there was some way to cast the multirange to a range array,
or somehow convert it, but apparently that doesn't work.

Just to be pedantic with examples:

SELECT datemultirange(
daterange(current_date, current_date + 2),
daterange(current_date + 5, current_date + 7))::daterange[];

ERROR: cannot cast type datemultirange to daterange[]
LINE 1: ...2), daterange(current_date + 5, current_date + 7))::daterang...

IF there was an array to cast it into an array, we could then use the
array looping construct in PL/pgSQL, but if we could only choose one, I
think it'd be more natural/less verbose to have an "unnest".

If the supporting pieces are mostly there, then I opine we should add
something.

Agreed.

I agree that unnest(), cast to array and subscription are missing
points. Proper subscription support requires expanded object
handling. And that seems too late for v14. But unnset() and cast to
array seems trivial. I've drafted unnest support (attached). I'm
going to add also cast to the array, tests, and docs within a day.
Stay tuned :)

------
Regards,
Alexander Korotkov

On 6/10/21 1:24 PM, Alexander Korotkov wrote:

Hi, all!

On Thu, Jun 10, 2021 at 2:00 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:

On 6/9/21 4:56 PM, Alvaro Herrera wrote:

On 2021-Jun-09, Jonathan S. Katz wrote:

I did a couple more tests around this.

As suspected, in PL/pgSQL, there is no way to unpack or iterate over a
multirange type.

Uh. This is disappointing; the need for some way to unnest or unpack a
multirange was mentioned multiple times in the range_agg thread. I had
assumed that there was some way to cast the multirange to a range array,
or somehow convert it, but apparently that doesn't work.

Just to be pedantic with examples:

SELECT datemultirange(
daterange(current_date, current_date + 2),
daterange(current_date + 5, current_date + 7))::daterange[];

ERROR: cannot cast type datemultirange to daterange[]
LINE 1: ...2), daterange(current_date + 5, current_date + 7))::daterang...

IF there was an array to cast it into an array, we could then use the
array looping construct in PL/pgSQL, but if we could only choose one, I
think it'd be more natural/less verbose to have an "unnest".

If the supporting pieces are mostly there, then I opine we should add
something.

Agreed.

I agree that unnest(), cast to array and subscription are missing
points. Proper subscription support requires expanded object
handling. And that seems too late for v14.

Agreed, the subscripting functionality is too late for v14. (Though
perhaps someone ambitious could bridge that gap temporarily with the
ability to add subscripting to types!).

But unnset() and cast to
array seems trivial. I've drafted unnest support (attached). I'm
going to add also cast to the array, tests, and docs within a day.
Stay tuned :)

Awesome. I'll defer to others on the implementation. I'll try to test
out the patch in a bit to see how it works.

Are there any objections adding this as a v14 open item?

Thanks,

Jonathan

On Thu, Jun 10, 2021 at 8:57 PM Jonathan S. Katz <jkatz@postgresql.org> wrote:

On 6/10/21 1:24 PM, Alexander Korotkov wrote:

I agree that unnest(), cast to array and subscription are missing
points. Proper subscription support requires expanded object
handling. And that seems too late for v14.

Agreed, the subscripting functionality is too late for v14. (Though
perhaps someone ambitious could bridge that gap temporarily with the
ability to add subscripting to types!).

But unnset() and cast to
array seems trivial. I've drafted unnest support (attached). I'm
going to add also cast to the array, tests, and docs within a day.
Stay tuned :)

Awesome. I'll defer to others on the implementation. I'll try to test
out the patch in a bit to see how it works.

Good!

Are there any objections adding this as a v14 open item?

No objections, let's add it.

------
Regards,
Alexander Korotkov

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Thanks Jonathan for excercising this implementation sooner than later.

--
Justin

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

------
Regards,
Alexander Korotkov

On Fri, Jun 11, 2021 at 11:37:58PM +0300, Alexander Korotkov wrote:

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

|+ The multirange could be explicitly cast to the array of corresponding
should say: "can be cast to an array of corresponding.."

|+ * Cast multirange to the array of ranges.
I think should be: *an array of ranges

Per sqlsmith, this is causing consistent crashes.
I took one of its less appalling queries and simplified it to this:

select
pg_catalog.multirange_to_array(
cast(pg_catalog.int8multirange() as int8multirange)) as c2
from (select 1)x;

--
Justin

()On Sat, Jun 12, 2021 at 2:30 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Fri, Jun 11, 2021 at 11:37:58PM +0300, Alexander Korotkov wrote:

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

|+ The multirange could be explicitly cast to the array of corresponding
should say: "can be cast to an array of corresponding.."

|+ * Cast multirange to the array of ranges.
I think should be: *an array of ranges

Thank you for catching this.

Per sqlsmith, this is causing consistent crashes.
I took one of its less appalling queries and simplified it to this:

select
pg_catalog.multirange_to_array(
cast(pg_catalog.int8multirange() as int8multirange)) as c2
from (select 1)x;

It seems that multirange_to_array() doesn't handle empty multiranges.
I'll post an updated version of the patch tomorrow.

------
Regards,
Alexander Korotkov

On Sat, Jun 12, 2021 at 2:44 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

()On Sat, Jun 12, 2021 at 2:30 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Fri, Jun 11, 2021 at 11:37:58PM +0300, Alexander Korotkov wrote:

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

|+ The multirange could be explicitly cast to the array of corresponding
should say: "can be cast to an array of corresponding.."

|+ * Cast multirange to the array of ranges.
I think should be: *an array of ranges

Thank you for catching this.

Per sqlsmith, this is causing consistent crashes.
I took one of its less appalling queries and simplified it to this:

select
pg_catalog.multirange_to_array(
cast(pg_catalog.int8multirange() as int8multirange)) as c2
from (select 1)x;

It seems that multirange_to_array() doesn't handle empty multiranges.
I'll post an updated version of the patch tomorrow.

A revised patch is attached. Now empty multiranges are handled
properly (and it's covered by tests). Typos are fixed as well.

------
Regards,
Alexander Korotkov

On 6/12/21 5:57 PM, Alexander Korotkov wrote:

On Sat, Jun 12, 2021 at 2:44 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

()On Sat, Jun 12, 2021 at 2:30 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Fri, Jun 11, 2021 at 11:37:58PM +0300, Alexander Korotkov wrote:

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

|+ The multirange could be explicitly cast to the array of corresponding
should say: "can be cast to an array of corresponding.."

|+ * Cast multirange to the array of ranges.
I think should be: *an array of ranges

Thank you for catching this.

Per sqlsmith, this is causing consistent crashes.
I took one of its less appalling queries and simplified it to this:

select
pg_catalog.multirange_to_array(
cast(pg_catalog.int8multirange() as int8multirange)) as c2
from (select 1)x;

It seems that multirange_to_array() doesn't handle empty multiranges.
I'll post an updated version of the patch tomorrow.

A revised patch is attached. Now empty multiranges are handled
properly (and it's covered by tests). Typos are fixed as well.

Tested both against my original cases using both SQL + PL/pgSQL. All
worked well. I also tested the empty multirange case as well.

Overall the documentation seems to make sense, I'd suggest:

+  <para>
+   The multirange can be cast to an array of corresponding ranges.
+  </para>

becomes:

+  <para>
+   A multirange can be cast to an array of ranges of the same type.
+  </para>

Again, I'll defer to others on the code, but this seems to solve the use
case I presented. Thanks for the quick turnaround!

Jonathan

On Sun, Jun 13, 2021 at 1:16 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:

On 6/12/21 5:57 PM, Alexander Korotkov wrote:

On Sat, Jun 12, 2021 at 2:44 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

()On Sat, Jun 12, 2021 at 2:30 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Fri, Jun 11, 2021 at 11:37:58PM +0300, Alexander Korotkov wrote:

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

|+ The multirange could be explicitly cast to the array of corresponding
should say: "can be cast to an array of corresponding.."

|+ * Cast multirange to the array of ranges.
I think should be: *an array of ranges

Thank you for catching this.

Per sqlsmith, this is causing consistent crashes.
I took one of its less appalling queries and simplified it to this:

select
pg_catalog.multirange_to_array(
cast(pg_catalog.int8multirange() as int8multirange)) as c2
from (select 1)x;

It seems that multirange_to_array() doesn't handle empty multiranges.
I'll post an updated version of the patch tomorrow.

A revised patch is attached. Now empty multiranges are handled
properly (and it's covered by tests). Typos are fixed as well.

Tested both against my original cases using both SQL + PL/pgSQL. All
worked well. I also tested the empty multirange case as well.

Overall the documentation seems to make sense, I'd suggest:

+  <para>
+   The multirange can be cast to an array of corresponding ranges.
+  </para>

becomes:

+  <para>
+   A multirange can be cast to an array of ranges of the same type.
+  </para>

Thank you. This change is incorporated in the attached revision of the patch.

This thread gave me another lesson about English articles. Hopefully,
I would be able to make progress in future patches :)

Again, I'll defer to others on the code, but this seems to solve the use
case I presented. Thanks for the quick turnaround!

Thank you for the feedback!

------
Regards,
Alexander Korotkov

On Sun, Jun 13, 2021 at 2:58 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

On Sun, Jun 13, 2021 at 1:16 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:

On 6/12/21 5:57 PM, Alexander Korotkov wrote:

On Sat, Jun 12, 2021 at 2:44 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

()On Sat, Jun 12, 2021 at 2:30 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Fri, Jun 11, 2021 at 11:37:58PM +0300, Alexander Korotkov wrote:

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

|+ The multirange could be explicitly cast to the array of corresponding
should say: "can be cast to an array of corresponding.."

|+ * Cast multirange to the array of ranges.
I think should be: *an array of ranges

Thank you for catching this.

Per sqlsmith, this is causing consistent crashes.
I took one of its less appalling queries and simplified it to this:

select
pg_catalog.multirange_to_array(
cast(pg_catalog.int8multirange() as int8multirange)) as c2
from (select 1)x;

It seems that multirange_to_array() doesn't handle empty multiranges.
I'll post an updated version of the patch tomorrow.

A revised patch is attached. Now empty multiranges are handled
properly (and it's covered by tests). Typos are fixed as well.

Tested both against my original cases using both SQL + PL/pgSQL. All
worked well. I also tested the empty multirange case as well.

Overall the documentation seems to make sense, I'd suggest:

+  <para>
+   The multirange can be cast to an array of corresponding ranges.
+  </para>

becomes:

+  <para>
+   A multirange can be cast to an array of ranges of the same type.
+  </para>

Thank you. This change is incorporated in the attached revision of the patch.

This thread gave me another lesson about English articles. Hopefully,
I would be able to make progress in future patches :)

Again, I'll defer to others on the code, but this seems to solve the use
case I presented. Thanks for the quick turnaround!

Thank you for the feedback!

I've added the commit message to the patch. I'm going to push it if
no objections.

------
Regards,
Alexander Korotkov

On 6/13/21 7:43 AM, Alexander Korotkov wrote:

On Sun, Jun 13, 2021 at 2:58 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:

On Sun, Jun 13, 2021 at 1:16 AM Jonathan S. Katz <jkatz@postgresql.org> wrote:

Again, I'll defer to others on the code, but this seems to solve the use
case I presented. Thanks for the quick turnaround!

Thank you for the feedback!

I've added the commit message to the patch. I'm going to push it if
no objections.

I went ahead and tried testing a few more cases with the patch, and
everything seems to work as expected.

I did skim through the code -- I'm much less familiar with this part of
the system -- and I did not see anything that I would consider "obvious
to correct" from my perspective.

So I will continue to go with what I said above: no objections on the
use case perspective, but I defer to others on the code.

One question: if I were to make a custom multirange type (e.g. let's say
I use "inet" to make "inetrange" and then a "inetmultirange") will this
method still work? It seems so, but I wanted clarify.

Thanks,

Jonathan

On 6/13/21 8:26 AM, Jonathan S. Katz wrote:

One question: if I were to make a custom multirange type (e.g. let's say
I use "inet" to make "inetrange" and then a "inetmultirange") will this
method still work? It seems so, but I wanted clarify.

I went ahead and answered this myself: "yes":

CREATE TYPE inetrange AS RANGE (SUBTYPE = inet);

SELECT unnest(inetmultirange(inetrange('192.168.1.1', '192.168.1.5'),
inetrange('192.168.1.7', '192.168.1.10')));
unnest
----------------------------
[192.168.1.1,192.168.1.5)
[192.168.1.7,192.168.1.10)
(2 rows)

Awesome stuff.

Jonathan

On Sat, Jun 12, 2021 at 4:58 PM Alexander Korotkov <aekorotkov@gmail.com>
wrote:

On Sun, Jun 13, 2021 at 1:16 AM Jonathan S. Katz <jkatz@postgresql.org>
wrote:

On 6/12/21 5:57 PM, Alexander Korotkov wrote:

On Sat, Jun 12, 2021 at 2:44 AM Alexander Korotkov <

aekorotkov@gmail.com> wrote:

()On Sat, Jun 12, 2021 at 2:30 AM Justin Pryzby <pryzby@telsasoft.com>

wrote:

On Fri, Jun 11, 2021 at 11:37:58PM +0300, Alexander Korotkov wrote:

On Fri, Jun 11, 2021 at 1:04 AM Justin Pryzby <pryzby@telsasoft.com>

wrote:

+{ oid => '1293', descr => 'expand mutlirange to set of ranges',

typo: mutlirange

Fixed, thanks.

The patch with the implementation of both unnest() and cast to array
is attached. It contains both tests and docs.

|+ The multirange could be explicitly cast to the array of

corresponding

should say: "can be cast to an array of corresponding.."

|+ * Cast multirange to the array of ranges.
I think should be: *an array of ranges

Thank you for catching this.

Per sqlsmith, this is causing consistent crashes.
I took one of its less appalling queries and simplified it to this:

select
pg_catalog.multirange_to_array(
cast(pg_catalog.int8multirange() as int8multirange)) as c2
from (select 1)x;

It seems that multirange_to_array() doesn't handle empty multiranges.
I'll post an updated version of the patch tomorrow.

A revised patch is attached. Now empty multiranges are handled
properly (and it's covered by tests). Typos are fixed as well.

Tested both against my original cases using both SQL + PL/pgSQL. All
worked well. I also tested the empty multirange case as well.

Overall the documentation seems to make sense, I'd suggest:

+  <para>
+   The multirange can be cast to an array of corresponding ranges.
+  </para>

becomes:

+  <para>
+   A multirange can be cast to an array of ranges of the same type.
+  </para>

Thank you. This change is incorporated in the attached revision of the
patch.

This thread gave me another lesson about English articles. Hopefully,
I would be able to make progress in future patches :)

Again, I'll defer to others on the code, but this seems to solve the use
case I presented. Thanks for the quick turnaround!

Thank you for the feedback!

------
Regards,
Alexander Korotkov

Hi,
+ A multirange can be cast to an array of ranges of the same type.

I think 'same type' is not very accurate. It should be 'of the subtype'.

+ ObjectAddress myself,

nit: myself -> self

+/* Turn multirange into a set of ranges */

set of ranges: sequence of ranges

Cheers