Re: Result set stability in queries sorted by column with non-unique values

Thanks Niranjan,

In our case we require the rows returned to be in the same consistent
order across several queries as we need to paginate through a large
set of results by doing a combination of ORDER BY x, LIMIT, OFFSET.
However the sorting key "x" can contain non-unique values, thus we
need to be sure that rows with the same values end up in the same
position across the different queries we do per each result "page".

As I understand, unless we add a secondary sorting key (like the "id"
of the row) which is guaranteed to be unique, we might end up with
rows that can potentially "fluctuate" across the query result, is it
correct?

Thank you

On 15 April 2015 at 15:54, Niranjan <niranjan81(at)gmail(dot)com> wrote:
> Hi,
> I am sure the variation depends on the query plan decided by the engine at
> the time of execution, in short, AFAIK (as far as I know) the answer is No.
> There is no guarantee that the rows will be returned in the exact order.
>
> Some of the possible answers to be further explored would be
> A) if we want the data to be always randomly ordered explore function
> random(), or is it rand() I guess? to be included in the query.
> B) if we want the data to be arbitrarily ordered but maintain the order for
> each query execution, save a randomly ordered same number of records in a
> different table and then write a joined query.
>
> I hope it helps.
>
> Thanks & Regards
> Niranjan D. Pandit
>
> On 15-Apr-2015 5:11 pm, "Michele Bosi" <michele(dot)bosi(at)gmail(dot)com> wrote:
>>
>> Hi all,
>>
>> I would like to know if postgresql is guaranteed to return the rows
>> always in the same order in the following case:
>>
>> table "boxes"
>> -------------------
>> width | height
>> 1 | 5
>> 1 | 3
>> 1 | 7
>> 1 | 2
>> 1 | 4
>>
>> SELECT * FROM boxes ORDER BY width;
>>
>> result:
>> 1 | 5
>> 1 | 3
>> 1 | 7
>> 1 | 2
>> 1 | 4
>>
>> In practice we've seen that postgresql returns the rows in some kind
>> of stably arbitrary order (which is expected because "width" contains
>> non unique values) but the subtle ambiguity here (which is important
>> for our application) is whether postresql is guaranteed to always
>> return the same arbitrary order in a stable fashion or if it can (even
>> theoretically) return an always different aribtrary order every time I
>> perform the query, say for example:
>>
>> SELECT * FROM boxes ORDER BY width;
>> 1 | 3
>> 1 | 5
>> 1 | 7
>> 1 | 4
>> 1 | 2
>>
>> SELECT * FROM boxes ORDER BY width;
>> 1 | 2
>> 1 | 5
>> 1 | 7
>> 1 | 4
>> 1 | 3
>>
>> etc.
>>
>> The only two relevant lines I've found in the docs
>> (http://www.postgresql.org/docs/current/interactive/queries-order.html)
>> are:
>>
>> "A particular output ordering can only be guaranteed if the sort step
>> is explicitly chosen." which does not tell us in what order rows with
>> same sorting-key value are returned.
>>
>> and
>>
>> "If sorting is not chosen, the rows will be returned in an unspecified
>> order." which I would expect to be the case also when sorting is
>> chosen BUT the sorting-key values are always the same. However this
>> still does not tell us if the "unspecified order" is always the same
>> or not, ie. if it's stable. Another way to put this last point would
>> be: is "SELECT * FROM boxes LIMIT 3" guaranteed to always return the
>> same 3 rows (assuming I don't insert or delete new rows) or can
>> theoretically return any different 3 every time I query?
>>
>> Thank you.
>>
>>
>> --
>> Sent via pgsql-in-general mailing list (pgsql-in-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-in-general