Thread: out of memory error on 3 table join
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
In this case table 'a' references table 'b', and table 'c' references
table 'b'. Two table joins between 'a' and 'b' work fine. Likewise a
two table join between 'b' and 'c' work fine. However, trying to do a
join between 'a', 'b', and 'c' results in the error. For example:
met_data=# SELECT sites.longname, climate.year, climate.doy,
climate.tmax FROM climate, sites WHERE climate.id = sites.id AND
climate.year = 1999;
returns the expected result, and a query like:
SELECT sites.longname, solar.year, solar.doy, solar.solar FROM solar,
sites WHERE solar.id = sites.id AND solar.year = 1999;
also returns the expected result.
However, combining all three tables such as below results in the errors:
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;
I am beginning to suspect that I set up the tables incorrectly. Can
anyone spot where I messed this up? Tables are as follows:
met_data=# \d climate
Table "public.climate"
Column | Type | Modifiers
--------+----------+-----------
id | integer |
year | smallint |
month | smallint |
day | smallint |
doy | smallint |
date | date |
precip | real |
tmin | real |
tmax | real |
Foreign-key constraints:
"idfk" FOREIGN KEY (id) REFERENCES sites(id)
met_data=# \d solar
Table "public.solar"
Column | Type | Modifiers
--------+----------+-----------
id | integer |
year | smallint |
month | smallint |
day | smallint |
doy | smallint |
date | date |
solar | real |
Foreign-key constraints:
"idfk" FOREIGN KEY (id) REFERENCES sites(id)
met_data=# \d sites
Table "public.sites"
Column | Type | Modifiers
-----------------+-------------------+-----------
id | integer | not null
name | character varying |
longname | character varying |
state | character varying |
lat | double precision |
lon | double precision |
the_geom_lonlat | geometry |
the_geom_meters | geometry | not null
Indexes:
"sites_pkey" PRIMARY KEY, btree (id)
"sites_id_key" UNIQUE, btree (id)
"idx_sites_the_geom_lonlat" gist (the_geom_lonlat)
"idx_sites_the_geom_meters" gist (the_geom_meters) CLUSTER
Check constraints:
"enforce_dims_the_geom_meters" CHECK (ndims(the_geom_meters) = 2)
"enforce_dims_the_goem_lonlat" CHECK (ndims(the_geom_lonlat) = 2)
"enforce_geotype_the_geom_meters" CHECK (geometrytype
(the_geom_meters) = 'POINT'::text OR the_geom_meters IS NULL)
"enforce_geotype_the_goem_lonlat" CHECK (geometrytype
(the_geom_lonlat) = 'POINT'::text OR the_geom_lonlat IS NULL)
"enforce_srid_the_geom_meters" CHECK (srid(the_geom_meters) =
32661)
"enforce_srid_the_goem_lonlat" CHECK (srid(the_geom_lonlat) = 4269)
Kirk Wythers <kwythers@umn.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
On Dec 11, 2006, at 1:43 PM, Tom Lane wrote: > Kirk Wythers <kwythers@umn.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@postgresql.org so that > your > message can get through to the mailing list cleanly
On Mon, 2006-12-11 at 17:50 -0600, Kirk Wythers wrote:
> 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
>
That's a client-side error. Why are you trying to return 33M records to
the client?
Regards,
Jeff Davis
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@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.