Re: out of memory error on 3 table join

On Dec 11, 2006, at 1:43 PM, Tom Lane wrote:

> 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)

SELECT count (*) returns 33,061,700

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)

However attempting the join
met_data=# SELECT climate.year, solar.doy, sites.longname FROM
climate, sites, solar WHERE climate.id = sites.id AND solar.id =
sites.id AND climate.year = 1999;

gives the error.

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

>
> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that
> your
> message can get through to the mailing list cleanly