Re: Insert Documentation - Returning Clause and Order - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: Insert Documentation - Returning Clause and Order
Date
Msg-id CAKFQuwY_Tc7SLiETnJAcCuzGFkH8+crV=a6sPPxxr+CDKLwFRg@mail.gmail.com
Whole thread Raw
In response to Re: Insert Documentation - Returning Clause and Order  (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>)
List pgsql-hackers
On Mon, Dec 14, 2020 at 7:09 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
But we write what's guaranteed. Anything not written in
the documents is not guaranteed.

In the case of LIMIT we go to great lengths to write what isn't guaranteed.  I suggest that this is similar enough in nature to warrant the same emphasis.

"Thus, using different LIMIT/OFFSET values to select different subsets of a query result will give inconsistent results unless you enforce a predictable result ordering with ORDER BY. This is not a bug; it is an inherent consequence of the fact that SQL does not promise to deliver the results of a query in any particular order unless ORDER BY is used to constrain the order.

It is even possible for repeated executions of the same LIMIT query to return different subsets of the rows of a table, if there is not an ORDER BY to enforce selection of a deterministic subset. Again, this is not a bug; determinism of the results is simply not guaranteed in such a case."

I'd go so far as to say that it's more important here since the observed behavior is that things are ordered, and expected to be ordered, while with limit the non-determinism seems more obvious.

David J.

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Next Steps with Hash Indexes
Next
From: "Jonathan S. Katz"
Date:
Subject: 2021-08-12 release announcement draft