out of memory error on 3 table join
От | Kirk Wythers |
---|---|
Тема | out of memory error on 3 table join |
Дата | |
Msg-id | 450E4C40-DE8B-4930-A915-8E1CB866AE67@umn.edu обсуждение исходный текст |
Ответы |
Re: out of memory error on 3 table join
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
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)
В списке pgsql-general по дате отправления:
Следующее
От: "Raymond O'Donnell"Дата:
Сообщение: Re: World Wide International Law: Linux is compulsory (mandotary) in all schools/universities world-wide