Re: out of memory error on 3 table join

Kirk Wythers <kwythers(at)umn(dot)edu> writes:
> I have an database (pg 8.1.0 on OS X) where a three table inner-join
> gives the following errors:

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

How many rows are you expecting from this join?
(If you're not sure, try SELECT count(*) FROM same-from-and-where-clauses)

My bet is that you've mistyped something leading to an unconstrained
join --- maybe an extra instance of a table, or something. The query
doesn't show anything like that as given:

> SELECT sites.longname, solar.year, solar.doy, solar.solar
> climate.tmax FROM solar, sites, climate WHERE solar.id = sites.id AND
> climate.id = sites.id AND solar.year = 1999;

but you have obviously not copied-and-pasted exactly what you typed.

regards, tom lane