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

[Bryce Watkins]

Yep that query did indeed directly cause the error to occur.

Cheers,
Bryce. 

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
strk at refractions.net
Sent: Thursday, 10 March 2005 2:40 a.m.
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] Possible bug in postgis RC3 / proj4 with
largedata sets

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

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