[postgis-users] Possible bug in postgis RC3 / proj4 with large data sets

[Bryce Watkins]

Hi folks,
 
We have a problem when running pgsql2shp on a large dataset via the
following query (as a view):
 
 SELECT crs_street_address.house_number AS house_no,
crs_street_address.status, crs_road_name."type", crs_road_name.name,
crs_road_name."location", transform(translate(crs_street_address.shape,
160::double precision, 0::double precision, 0::double precision), 27200) AS
point
   FROM crs_street_address
   JOIN crs_road_name ON crs_street_address.rna_id = crs_road_name.id;

QUERY PLAN
Merge Join  (cost=0.00..83997.59 rows=1506893 width=86)
  Merge Cond: ("outer".id = "inner".rna_id)
  ->  Index Scan using crs_road_name_id_idx on crs_road_name
(cost=0.00..1854.48 rows=68168 width=51)
  ->  Index Scan using crs_street_address_rna_id_idx on crs_street_address
(cost=0.00..55602.24 rows=1506893 width=43)

The job runs for quite some time, but eventually will output the following:
Initializing... GeometryType: ERROR:  out of memory
DETAIL:  Failed on request of size 63.
CONTEXT:  SQL function "get_proj4_from_srid" statement 1
PL/pgSQL function "transform" line 2 at return

This is running on FreeBSD 5.3 with 2Gb ram and as I watch memory
utilisation, it is not using any swap at all.  So it doesn't appear to be a
traditional memory leak.  Also we have other querys that work find on a
smaller number of rows.
 
Any suggestions appreciated.
 
Bryce.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050309/b35bac31/attachment.html>