To: nickf@doxpop.com
Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>, pgsql-sql@postgresql.org
Subject: Re: interesting SQL puzzle - concatenating column with itself.
References: <427E60BC.8060701@ontko.com>
	<20050508212905.GA14422@dcc.uchile.cl> <428111F3.2090002@ontko.com>
In-Reply-To: <428111F3.2090002@ontko.com>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 13 May 2005 02:05:37 -0400
Message-ID: <87zmuzmzim.fsf@stark.xeocode.com>
Lines: 32
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii


Nick Fankhauser <nickf@ontko.com> writes:

> Alvaro Herrera wrote:
> 
> > The order is not really guaranteed, though if this is a one-shot thing,
> > you may get away with turning off hashed aggregates.
> >
> 
> When I read this, I assumed there was a runtime parameter I could set that was
> similar to ENABLE_HASHJOIN. Are you referring to a different runtime parameter
> or something else entirely?

Similar but different. enable_hashagg. You can see all of these with "show
all" in psql.

However you do not have to worry. Even with (actually, *especially with*) hash
aggregates the records will be processed in the order they're received.

It's actually the normal aggregate method of sorting on the GROUP BY columns
that risks damaging the order. However Postgres does a special check to see if
the subquery is already sorted by the GROUP BY column and avoids the extra
sort which could cause you problems.

So this is not guaranteed by the SQL spec to work (but then the SQL spec
doesn't have custom aggregates at all) but Postgres goes out of its way to
make sure this doesn't break unnecessarily.

[This is all for 8.0 and I think 7.4. Some versions before that might 

-- 
greg