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

[strk at refractions.net]

On Fri, Mar 11, 2005 at 01:58:53PM +1300, Bryce Watkins wrote:
> Yep that query did indeed directly cause the error to occur.

Do you still get it removing the WHERE clause ?
Can you monitor process size while running ?
--strk;

> 
> 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
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users