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

[Bryce Watkins]

Ok it was successful without the where clause, but upon watching the process
utilisation it did continue to consume a lot of memory - but it finished
before it ran out.

The server has 2Gb ram and I have the following settings:
shared_buffers = 45000          # min 16, at least max_connections*2, 8KB
each
work_mem = 10240                # min 64, size in KB 

I have tried reducing these values but then it just fails sooner.

I have included a text file with a bit of a process log as it runs.

Also FYI:
Explain SELECT distinct geometrytype(point) from address_geom
where NOT geometrytype(point) is null;

QUERY PLAN
Unique  (cost=272467.60..279964.39 rows=1499359 width=25)
  ->  Sort  (cost=272467.60..276216.00 rows=1499359 width=25)
        Sort Key: geometrytype(transform(translate(crs_street_address.shape,
160::double precision, 0::double precision, 0::double precision), 27200))
        ->  Hash Join  (cost=1750.10..97787.90 rows=1499359 width=25)
              Hash Cond: ("outer".rna_id = "inner".id)
              ->  Seq Scan on crs_street_address  (cost=0.00..47308.63
rows=1499359 width=29)
                    Filter: (NOT (geometrytype(transform(translate(shape,
160::double precision, 0::double precision, 0::double precision), 27200)) IS
NULL))
              ->  Hash  (cost=1579.68..1579.68 rows=68168 width=4)
                    ->  Seq Scan on crs_road_name  (cost=0.00..1579.68
rows=68168 width=4)

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: Friday, 11 March 2005 8:06 p.m.
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Possible bug in postgis RC3 / proj4
withlargedata sets

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
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: process log.txt
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050311/f7547469/attachment.txt>