Re: unnest multirange, returned order
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
Unclear guarantees about sort order on https://www.postgresql.org/docs/current/queries-order.html
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/queries-order.html Description: The document only says this about unsorted queries: > After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen. It mentions "If sorting is not chosen". This sort of implies that if you pick a sort the output order is predictable. However I believe that the only actual guarantee is if the sort columns selected produce a unique value. For example if you do `ORDER BY name` and have two rows with the same name I don't think the order of those rows is predictable. I think the docs should be updated to either: 1. Clearly state that the order **is** consent as long as any sort clause is present, and specify what that order is. 2. Update the quoted sentence to refer to "If sorting is not chosen or the sort columns do not form a unique key" instead of just "If sorting is not chosen".
Missing closing ]
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/16/rangetypes.html Description: In the example session at https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-EXAMPLES there are missing closing square bracket ] Take a look below CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); there should be ] after 15:30
Re: Missing closing ]
PG Doc comments form writes: > In the example session at > https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-EXAMPLES > there are missing closing square bracket ] > Take a look below > CREATE TABLE reservation (room int, during tsrange); > INSERT INTO reservation VALUES > (1108, '[2010-01-01 14:30, 2010-01-01 15:30)'); > there should be ] after 15:30 No, it looks correct as given: that end of the range is open not closed. regards, tom lane
Re: unnest multirange, returned order
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 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
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
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 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: Unclear guarantees about sort order on https://www.postgresql.org/docs/current/queries-order.html
On 2023-10-04 16:24 +0200, PG Doc comments form write: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/queries-order.html > Description: > > The document only says this about unsorted queries: > > > After a query has produced an output table (after the select list has been > > processed) it can optionally be sorted. If sorting is not chosen, the rows > > will be returned in an unspecified order. The actual order in that case will > > depend on the scan and join plan types and the order on disk, but it must > > not be relied on. A particular output ordering can only be guaranteed if the > > sort step is explicitly chosen. > > It mentions "If sorting is not chosen". This sort of implies that if you > pick a sort the output order is predictable. However I believe that the only > actual guarantee is if the sort columns selected produce a unique value. > > For example if you do `ORDER BY name` and have two rows with the same name I > don't think the order of those rows is predictable. Right, without an explicit sorting the order of rows is unpredictable. When only sorting over some columns/expressions then the ordering is only predictable in those columns/expressions. The order of rows with ties within the same sorted group is also unpredictable. This is also implied on the same page after the first example: "When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values." This implies that without later values, those rows remain in unpredictable order. The SQL standards linked by [1] provide some definitions: SQL:2003, Part 2, Annex C, 26b: "The relative ordering of two rows that are not distinct with respect to the is implementation-dependent." SQL:2011, Part 2, Annex C, 24e: "If a immediately contains an and a group of two or more rows in the table specified by that contain values that are not distinct in all sort keys specified in the , then the ordering of these rows in that group is implementation-dependent." I find the first one quite succinct. > I think the docs should be updated to either: > > 1. Clearly state that the order **is** consent as long as any sort clause is > present, and specify what that order is. What do you mean with "any sort clause"? Any sort clause at all? Or a sort clause that covers all columns? If the order should be predictable it must be specified by the client to some degree. And if the client does not care about rows with ties than he should not be required to specify a more specific sorting. > 2. Update the quoted sentence to refer to "If sorting is not chosen or the > sort columns do not form a unique key" instead of just "If sorting is not > chosen". I think "unique key" is misleading in this case because sorting still leaves duplicates. I'd go with something that mentions "sorted groups" and "tie breaks" or some form of the quoted SQL:2003 definition. [1] https://wiki.postgresql.org/wiki/Developer_FAQ#Where_can_I_get_a_copy_of_the_SQL_standards.3F -- Erik
Re: unnest multirange, returned order
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
From e39d1b5760d6fa0ed143c13589f717846cc82574 Mon Sep 17 00:00:00 2001
From: Laurenz Albe
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)') ...
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.
unnest('{[1,2), [3,4)}'::int4multirange)
--
2.41.0
