Re: Do AGGREGATES consistently use sort order? - Mailing list pgsql-general

From Chris Browne
Subject Re: Do AGGREGATES consistently use sort order?
Date
Msg-id 603axrtw9w.fsf@dba2.int.libertyrms.com
Whole thread Raw
In response to Do AGGREGATES consistently use sort order?  ("Webb Sprague" <webb.sprague@gmail.com>)
Responses Re: Do AGGREGATES consistently use sort order?
Re: Do AGGREGATES consistently use sort order?
List pgsql-general
stark@enterprisedb.com (Gregory Stark) writes:
> "Webb Sprague" <webb.sprague@gmail.com> writes:
>
>> I can always count on (note the order name):
>>
>> \a
>> oregon_2007_08_20=# select array_accum(name) from (select name from
>> placenames where desig='crater' order by name desc) a;
>> array_accum
>> {"Yapoah Crater","West Crater","Twin Craters","Timber Crater","Red
>> Crater","Newberry Crater","Nash Crater","Mount Mazama","Millican
>> Crater","Little Nash Crater","Le Conte Crater","Jordan
>> Craters","Diamond Craters","Coffeepot Crater","Cayuse Crater","Black
>> Crater","Big Hole","Belknap Crater"}
>> (1 row)
>>
>> I am interested in stitching a line out of points in postgis, but
>> the order/aggregate thing is a general question.
>
> Yes.
>
> You can even do this with GROUP BY as long as the leading columns of
> the ORDER BY inside the subquery exactly matches the GROUP BY
> columns.
>
> In theory we can't promise anything about future versions of
> Postgres but there are lots of people doing this already so if ever
> this was lost there would probably be some new explicit way to
> achieve the same thing.

Is there not some risk that the query planner might choose to do
hash-based accumulation could discard the subquery's ordering?

Under the visible circumstances, it's unlikely, but isn't it possible
for the aggregation to pick hashing and make a hash of this?
--
output = reverse("gro.mca" "@" "enworbbc")
http://linuxfinances.info/info/spiritual.html
If anyone ever  markets  a really  well-documented Unix that   doesn't
require  babysitting by a phalanx of  provincial Unix clones, there'll
be a  lot of unemployable,  twinky-braindamaged misfits out deservedly
pounding the pavements.

pgsql-general by date:

Previous
From: Chris Browne
Date:
Subject: Re: a provocative question?
Next
From: "Dann Corbit"
Date:
Subject: Re: an other provokative question??