pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedunnest multirange, returned order
11+ messages / 4 participants
[nested] [flat]
* unnest multirange, returned order
@ 2023-10-02 18:42 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 11+ messages in thread
From: PG Doc comments form @ 2023-10-02 18:42 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: daniel.fredouille@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/functions-range.html
Description:
The doc says:
* unnest ( anymultirange ) → setof anyrange
* Expands a multirange into a set of ranges. The ranges are read out in
storage order (ascending).
What is storage order ?
At first I thought that it was the order in which the different ranges are
inserted in the internal data structure. However, the following sort of
shows that it is not:
```
postgres=# select unnest('{[1,4), [8,10)}'::int4multirange + '{[-5,-3)}' -
'{[2,3)}') ;
unnest
---------
[-5,-3)
[1,2)
[3,4)
[8,10)
(4 lignes)
```
Whatever I try, it always return in range order instead of "storage order".
Some context: I ask because we have some seemingly random (and impossible to
repro in tests up to now) errors in our code. The code assumes that this
returns things in range order and as the doc is unclear to me on this point,
I cannot exclude this to be our culprit.
Thank you
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-03 13:46 Laurenz Albe <laurenz.albe@cybertec.at>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2023-10-03 13:46 UTC (permalink / raw)
To: daniel.fredouille@gmail.com; pgsql-docs@lists.postgresql.org
On Mon, 2023-10-02 at 18:42 +0000, PG Doc comments form wrote:
> Page: https://www.postgresql.org/docs/16/functions-range.html
>
> The doc says:
> * unnest ( anymultirange ) → setof anyrange
> * Expands a multirange into a set of ranges. The ranges are read out in
> storage order (ascending).
>
> What is storage order ?
>
> At first I thought that it was the order in which the different ranges are
> inserted in the internal data structure. However, the following sort of
> shows that it is not:
> ```
> postgres=# select unnest('{[1,4), [8,10)}'::int4multirange + '{[-5,-3)}' -
> '{[2,3)}') ;
> unnest
> ---------
> [-5,-3)
> [1,2)
> [3,4)
> [8,10)
> (4 lignes)
> ```
> Whatever I try, it always return in range order instead of "storage order".
I'd say that the storag order is the order in which PostgreSQL stores
multiranges internally:
SELECT '{[100,200),[-100,-50),[-1,2)}'::int4multirange;
int4multirange
═══════════════════════════════
{[-100,-50),[-1,2),[100,200)}
(1 row)
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-04 00:40 Daniel Fredouille <daniel.fredouille@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Fredouille @ 2023-10-04 00:40 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
>
> I'd say that the storag order is the order in which PostgreSQL stores
> multiranges internally:
Right, I believe that you are right but then this information is not useful
for the developer.
If storage order is always ascending by range order then let's make it
clear, if order cannot be counted upon as it may evolve from postgres
version to version, then let's make it clear as well. WDYT ?
Thank you.
Daniel Fredouille
Le mar. 3 oct. 2023 à 09:46, Laurenz Albe <laurenz.albe@cybertec.at> a
écrit :
> On Mon, 2023-10-02 at 18:42 +0000, PG Doc comments form wrote:
> > Page: https://www.postgresql.org/docs/16/functions-range.html
> >
> > The doc says:
> > * unnest ( anymultirange ) → setof anyrange
> > * Expands a multirange into a set of ranges. The ranges are read out in
> > storage order (ascending).
> >
> > What is storage order ?
> >
> > At first I thought that it was the order in which the different ranges
> are
> > inserted in the internal data structure. However, the following sort of
> > shows that it is not:
> > ```
> > postgres=# select unnest('{[1,4), [8,10)}'::int4multirange + '{[-5,-3)}'
> -
> > '{[2,3)}') ;
> > unnest
> > ---------
> > [-5,-3)
> > [1,2)
> > [3,4)
> > [8,10)
> > (4 lignes)
> > ```
> > Whatever I try, it always return in range order instead of "storage
> order".
>
> I'd say that the storag order is the order in which PostgreSQL stores
> multiranges internally:
>
> SELECT '{[100,200),[-100,-50),[-1,2)}'::int4multirange;
>
> int4multirange
> ═══════════════════════════════
> {[-100,-50),[-1,2),[100,200)}
> (1 row)
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-04 07:20 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Daniel Fredouille <daniel.fredouille@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2023-10-04 07:20 UTC (permalink / raw)
To: Daniel Fredouille <daniel.fredouille@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Tue, 2023-10-03 at 20:40 -0400, Daniel Fredouille wrote:
> > I'd say that the storag order is the order in which PostgreSQL stores
> > multiranges internally:
>
> Right, I believe that you are right but then this information is not useful for the developer.
> If storage order is always ascending by range order then let's make it clear,
> if order cannot be counted upon as it may evolve from postgres version to version,
> then let's make it clear as well. WDYT ?
I personally think that it is clear as it is written now.
If you have a good suggestion for an improvement, you could send it;
perhaps someone will pick it up.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-05 00:04 Daniel Fredouille <daniel.fredouille@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Fredouille @ 2023-10-05 00:04 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
Trying a suggestion then:
"""
unnest ( anymultirange ) → setof anyrange
Expands a multirange into a set of ranges. The ranges are read out in
storage order (ascending) and therefore cannot be relied upon.
unnest('{[1,2), [3,4)}'::int4multirange) →
[1,2)
[3,4)
"""
Daniel
Le mer. 4 oct. 2023 à 03:20, Laurenz Albe <laurenz.albe@cybertec.at> a
écrit :
> On Tue, 2023-10-03 at 20:40 -0400, Daniel Fredouille wrote:
> > > I'd say that the storag order is the order in which PostgreSQL stores
> > > multiranges internally:
> >
> > Right, I believe that you are right but then this information is not
> useful for the developer.
> > If storage order is always ascending by range order then let's make it
> clear,
> > if order cannot be counted upon as it may evolve from postgres version
> to version,
> > then let's make it clear as well. WDYT ?
>
> I personally think that it is clear as it is written now.
>
> If you have a good suggestion for an improvement, you could send it;
> perhaps someone will pick it up.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-05 00:12 Daniel Fredouille <daniel.fredouille@gmail.com>
parent: Daniel Fredouille <daniel.fredouille@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Fredouille @ 2023-10-05 00:12 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
Sorry correcting my own suggestion:
"""
unnest ( anymultirange ) → setof anyrange
Expands a multirange into a set of ranges. The ranges are read out in
storage order (ascending) and therefore order cannot be relied upon.
unnest('{[1,2), [3,4)}'::int4multirange) →
[1,2)
[3,4)
"""
Le mer. 4 oct. 2023 à 20:04, Daniel Fredouille <daniel.fredouille@gmail.com>
a écrit :
> Trying a suggestion then:
>
> """
>
> unnest ( anymultirange ) → setof anyrange
>
> Expands a multirange into a set of ranges. The ranges are read out in
> storage order (ascending) and therefore cannot be relied upon.
>
> unnest('{[1,2), [3,4)}'::int4multirange) →
>
> [1,2)
> [3,4)
>
> """
> Daniel
>
> Le mer. 4 oct. 2023 à 03:20, Laurenz Albe <laurenz.albe@cybertec.at> a
> écrit :
>
>> On Tue, 2023-10-03 at 20:40 -0400, Daniel Fredouille wrote:
>> > > I'd say that the storag order is the order in which PostgreSQL stores
>> > > multiranges internally:
>> >
>> > Right, I believe that you are right but then this information is not
>> useful for the developer.
>> > If storage order is always ascending by range order then let's make it
>> clear,
>> > if order cannot be counted upon as it may evolve from postgres version
>> to version,
>> > then let's make it clear as well. WDYT ?
>>
>> I personally think that it is clear as it is written now.
>>
>> If you have a good suggestion for an improvement, you could send it;
>> perhaps someone will pick it up.
>>
>> Yours,
>> Laurenz Albe
>>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-05 06:50 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Daniel Fredouille <daniel.fredouille@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2023-10-05 06:50 UTC (permalink / raw)
To: Daniel Fredouille <daniel.fredouille@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
On Wed, 2023-10-04 at 20:12 -0400, Daniel Fredouille wrote:
> unnest ( anymultirange ) → setof anyrange
> Expands a multirange into a set of ranges. The ranges are read out in storage order (ascending) and therefore order cannot be relied upon.
That's not true. The order is deterministic and can be relied on.
How about the attached patch, which does away with the confusing
mention of "storage order"?
Yours,
Laurenz Albe
Attachments:
[text/x-patch] 0001-Clarify-the-result-order-of-unnest-multirange.patch (1.3K, ../../fd85a22502ce44458bc3ddc616bf352c077e67f4.camel@cybertec.at/2-0001-Clarify-the-result-order-of-unnest-multirange.patch)
download | inline diff:
From e39d1b5760d6fa0ed143c13589f717846cc82574 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Thu, 5 Oct 2023 08:48:21 +0200
Subject: [PATCH] Clarify the result order of unnest(multirange)
It is best not to mention the storage order, because that is
an implementation detail and has confused at least one user,
who assumed that the storage order is the order in which the
constituent ranges were written in SQL.
Since the sorting order is explained at the beginning of the
page, it should be sufficient to say that the ranges are
returned in ascending order.
Discussion: https://postgr.es/m/169627213477.3727338.17653654241633692682%40wrigleys.postgresql.org
---
doc/src/sgml/func.sgml | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 24ad87f910..bbb6e878ab 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20121,7 +20121,7 @@ SELECT NULLIF(value, '(none)') ...
</para>
<para>
Expands a multirange into a set of ranges.
- The ranges are read out in storage order (ascending).
+ The ranges are read out in ascending order.
</para>
<para>
<literal>unnest('{[1,2), [3,4)}'::int4multirange)</literal>
--
2.41.0
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-13 19:33 Daniel Fredouille <daniel.fredouille@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 11+ messages in thread
From: Daniel Fredouille @ 2023-10-13 19:33 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-docs@lists.postgresql.org
Hi,
sorry it took me some time to reply. Yes, the patch is perfect if this is
indeed the behavior.
cheers
Daniel
Le jeu. 5 oct. 2023 à 02:50, Laurenz Albe <laurenz.albe@cybertec.at> a
écrit :
> On Wed, 2023-10-04 at 20:12 -0400, Daniel Fredouille wrote:
> > unnest ( anymultirange ) → setof anyrange
> > Expands a multirange into a set of ranges. The ranges are read out in
> storage order (ascending) and therefore order cannot be relied upon.
>
> That's not true. The order is deterministic and can be relied on.
>
> How about the attached patch, which does away with the confusing
> mention of "storage order"?
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-27 06:48 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Daniel Fredouille <daniel.fredouille@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Laurenz Albe @ 2023-10-27 06:48 UTC (permalink / raw)
To: Daniel Fredouille <daniel.fredouille@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Fri, 2023-10-13 at 15:33 -0400, Daniel Fredouille wrote:
> sorry it took me some time to reply. Yes, the patch is perfect if this is indeed the behavior.
I'm sending a reply to the hackers list so that I can add the patch to the commitfest.
Tiny as the patch is, I don't want it to fall between the cracks.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-27 23:08 Jeff Davis <pgsql@j-davis.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 11+ messages in thread
From: Jeff Davis @ 2023-10-27 23:08 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; Daniel Fredouille <daniel.fredouille@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Fri, 2023-10-27 at 08:48 +0200, Laurenz Albe wrote:
> On Fri, 2023-10-13 at 15:33 -0400, Daniel Fredouille wrote:
> > sorry it took me some time to reply. Yes, the patch is perfect if
> > this is indeed the behavior.
>
> I'm sending a reply to the hackers list so that I can add the patch
> to the commitfest.
>
> Tiny as the patch is, I don't want it to fall between the cracks.
Committed with adjusted wording. Thank you!
--
Jeff Davis
PostgreSQL Contributor Team - AWS
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: unnest multirange, returned order
@ 2023-10-28 08:53 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 0 replies; 11+ messages in thread
From: Laurenz Albe @ 2023-10-28 08:53 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; Daniel Fredouille <daniel.fredouille@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Fri, 2023-10-27 at 16:08 -0700, Jeff Davis wrote:
> On Fri, 2023-10-27 at 08:48 +0200, Laurenz Albe wrote:
> > On Fri, 2023-10-13 at 15:33 -0400, Daniel Fredouille wrote:
> > > sorry it took me some time to reply. Yes, the patch is perfect if
> > > this is indeed the behavior.
> >
> > I'm sending a reply to the hackers list so that I can add the patch
> > to the commitfest.
> >
> > Tiny as the patch is, I don't want it to fall between the cracks.
>
> Committed with adjusted wording. Thank you!
Thanks!
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2023-10-28 08:53 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-02 18:42 unnest multirange, returned order PG Doc comments form <noreply@postgresql.org>
2023-10-03 13:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-04 00:40 ` Daniel Fredouille <daniel.fredouille@gmail.com>
2023-10-04 07:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-05 00:04 ` Daniel Fredouille <daniel.fredouille@gmail.com>
2023-10-05 00:12 ` Daniel Fredouille <daniel.fredouille@gmail.com>
2023-10-05 06:50 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-13 19:33 ` Daniel Fredouille <daniel.fredouille@gmail.com>
2023-10-27 06:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-27 23:08 ` Jeff Davis <pgsql@j-davis.com>
2023-10-28 08:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox