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

[strk at refractions.net]

Try defining VERBOSE to 3 on top of pgsql2shp.c
it will print queries as they are executed.

I have checked the getGeometryType() function, and the two queries it execute
both do release allocated memory before continuing.

The error message should be generated from the following query:

SELECT distinct geometrytype(the_geom) from table where
	NOT geometrytype(the_geom) is null;

Try that directly with the monitor.

--strk;

On Wed, Mar 09, 2005 at 11:17:35PM +1300, Bryce Watkins wrote:
> 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.

> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users