Thread
Commits
-
Clarify the result order of unnest(multirange).
- d014e6cb1886 17.0 landed
-
unnest multirange, returned order
The Post Office <noreply@postgresql.org> — 2023-10-02T18:42:14Z
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 -
Re: unnest multirange, returned order
Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-03T13:46:23Z
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 -
Re: unnest multirange, returned order
Daniel Fredouille <daniel.fredouille@gmail.com> — 2023-10-04T00:40:40Z
> > 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 > -
Re: unnest multirange, returned order
Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-04T07:20:16Z
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
-
Re: unnest multirange, returned order
Daniel Fredouille <daniel.fredouille@gmail.com> — 2023-10-05T00:04:41Z
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 > -
Re: unnest multirange, returned order
Daniel Fredouille <daniel.fredouille@gmail.com> — 2023-10-05T00:12:19Z
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 >> > -
Re: unnest multirange, returned order
Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-05T06:50:24Z
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
-
Re: unnest multirange, returned order
Daniel Fredouille <daniel.fredouille@gmail.com> — 2023-10-13T19:33:57Z
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 >
-
Re: unnest multirange, returned order
Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-27T06:48:49Z
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
-
Re: unnest multirange, returned order
Jeff Davis <pgsql@j-davis.com> — 2023-10-27T23:08:37Z
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
-
Re: unnest multirange, returned order
Laurenz Albe <laurenz.albe@cybertec.at> — 2023-10-28T08:53:58Z
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