Re: out of memory error on 3 table join

On Mon, Dec 11, 2006 at 05:50:53PM -0600, Kirk Wythers wrote:
> met_data=# SELECT count(*) FROM climate, sites, solar WHERE
> climate.id = sites.id AND solar.id = sites.id AND climate.year = 1999;
> ----------
> 33061700
> (1 row)

<snip>

> psql(394) malloc: *** vm_allocate(size=396742656) failed (error code=3)
> psql(394) malloc: *** error: can't allocate region
> psql(394) malloc: *** set a breakpoint in szone_error to debug
> out of memory

Basically, it failed to allocate nearly 400MB of memory to store the
results. The question is: do you want ot store all 400MB at once?

If you don't, use a cursor.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.