On 9/9/15 7:44 PM, David G. Johnston wrote:
On Wed, Sep 9, 2015 at 7:53 PM, Charles Sheridan <cesh...@swbell.net
David, yes, I agree that sorting at the end is the highest-confidence
approach. I don't (yet) have a large stack of views with an assumption
of a guaranteed underlying sort order, I'm just trying to get a better
sense of what Postgres behavior I can reasonably expect here.
When there are several views defined on top of each other, are
SELECTs on views that do not specify a SORT order guaranteed to
preserve the cumulative sort order of the lower-level views ?
Is the answer true for any arbitrarily large set of layered views?
Is the answer the same if the layers of relations are a mix of
views and tables ?
The answer to any question as broad and non-specific as yours is
likely to be answered with a no.
The better question is how expensive is it to sort already sorted
data. If its cheap, and it likely is, then placing explicit sorting
where you care is the best solution regardless of your level of
confidence that lower level sorting is being maintained.
Since tables are never sorted I don't get why you think they enter
into the equation.
If ones operates under the guideline that only top-layer queries
should contain ORDER BY then your whole structure is unsound.
Presumably those queries you rely upon are or were themselves
considered top-level queries at one point and now you are adding a
dependent to them that they likely were never intended to consider.
Simplification queries should not use ORDER BY unless it is necessary
to implement their logic. A query whose logic depends on order really
should declare that fact.