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