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

From: Michele Bosi <michele(dot)bosi(at)gmail(dot)com>
To: pgsql-in-general(at)postgresql(dot)org
Subject: Result set stability in queries sorted by column with non-unique values
Date: 2015-04-15 11:40:30
Message-ID: CACmQ0_Kd76jtCF09UYTf=zVSvzsbizW+y=DKTWif0rpLKmduDA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-in-general

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.

Responses

Browse pgsql-in-general by date

  From Date Subject
Next Message Niranjan 2015-04-15 14:54:07 Re: Result set stability in queries sorted by column with non-unique values
Previous Message Jayadevan M 2014-12-10 03:16:03 Re: Hardware Requirements