Re: interesting SQL puzzle - concatenating column with itself.

From: Greg Stark <gsstark(at)mit(dot)edu>
To: nickf(at)doxpop(dot)com
Cc: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>, pgsql-sql(at)postgresql(dot)org
Subject: Re: interesting SQL puzzle - concatenating column with itself.
Date: 2005-05-13 06:05:37
Message-ID: 87zmuzmzim.fsf@stark.xeocode.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


Nick Fankhauser <nickf(at)ontko(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Mark Fenbers 2005-05-13 18:38:25 Replacing a table with constraints
Previous Message Michael Fuhr 2005-05-12 22:40:18 Re: md5 in pg_shadow?