Re: Do Layered Views/Relations Preserve Sort Order ?

On 9/9/15 7:44 PM, David G. Johnston wrote:
> On Wed, Sep 9, 2015 at 7:53 PM, Charles Sheridan <cesheri(at)swbell(dot)net
> <mailto:cesheri(at)swbell(dot)net>>wrote:
>
> Hi All,
>
> 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 wit​h 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.
>
> David J.
>
>
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.

Thanks, Charles