On Dec 11, 2006, at 1:43 PM, Tom Lane wrote:
Kirk Wythers <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that
your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
