[postgis-users] memory bug in postgis query

[Martin Güther]

 hi guys,  
thanks for the feedback.

i installed the latest GEOS + POSTGIS and now it works!

thank your very much :-)  

--  
Martin Güther


Am Sonntag, 23. Oktober 2011 um 21:14 schrieb Paragon Corporation:

> Martin,
> Can't remember last time I had that error, but Ihave seen it before.
>  
> I think the cause is one of two reasons
>  
> Corrupted location on disk -- usually involving a geometry record.  That is a pain to figure out.  I think I ended up doing limits to find the bad record and then deleted it or relaoaded the table skipping that record.  That case usually screws up your backup so you can tell if your backups are failing if this is the issue since the backup would try to traverse the record and fail with a bad alloc.
>  
> or
>  
> Bad geometry that causes infinite memory allocation.
>  
> You might want to try upgrading your PostGIS to latest 1.4 minor (1.4.0 is not the latest stable release of 1.4)
>  
> You might also want to upgrade your Geos.  I would try to go to 3.2.3 if at all possible.  I think several memory issues were fixed in 3.2 and may not have been back-ported to 3.1 because the code changed too much.
> Strk might have a better idea.
>  
> Hope that helps,
> Regina
> http://www.postgis.us
>  
>  
>  
> > From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Martin Güther
> > Sent: Sunday, October 23, 2011 7:03 AM
> > To: postgis-users at postgis.refractions.net (mailto:postgis-users at postgis.refractions.net)
> > Subject: [postgis-users] memory bug in postgis query
> >  
> > Hi guys,
> > i recently asked the same question on the postgresql mailing list but they forwarded me to this mailing list.  
> > I really hope you can help me :)
> >  
> > on a test maschine from my university i'm running into a weird problem (i attached detail info):
> >  
> > A running postgis query eats more and more memory.(seen in htop)  
> > It'll start using virtual memory and after eating up that one, too, postgres will
> > crash with:
> >  
> > psql:/home/mguether/sql/benchmark.sql:6: NOTICE: std::bad_alloc
> > psql:/home/mguether/sql/benchmark.sql:6: ERROR: GEOS intersects()
> > threw an error!
> >  
> > once it gave me an
> > psql:/home/mguether/sql/benchmark.sql:6: ERROR: out of memory
> > DETAIL: Failed on request of size 182949.
> >  
> > after the crash, all memory/swap is free again, so it really is postgres :)
> >  
> > i don't really know how to debug it any more detailed or fix this
> > issue in any way.
> >  
> > Does anyone here have a hint, where the issue might be?
> >  
> > thanks,
> > martin
> >  
> >  
> >  
> >  
> >  
> >  
> >  
> > here some information:
> >  
> > the maschine:
> > 16 GB RAM, i7 4cores x64 2.6.32-21-server ubuntu
> >  
> > installed postgres:
> > psql (PostgreSQL) 8.4.9
> > including postgis
> > POSTGIS="1.4.0" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.7.1, 23 September
> > 2009" USE_STATS
> >  
> > i changed two parameters from the default config:
> > shared_buffers = 512MB
> > work_mem = 256MB
> >  
> > using osm2pgsql I important some GEO data, and want to execute this query:
> >  
> > SELECT a.osm_id, w.osm_id FROM "planet_osm_line" w,
> > "planet_osm_polygon" a WHERE ST_Intersects(w.way,a.way) AND w.highway
> > IS NOT NULL;
> >  
> > the EXPLAIN gives me:
> >  
> > Nested Loop (cost=0.00..26427424.16 rows=1511021 width=8)
> >  Join Filter: _st_intersects(w.way, a.way)
> >  -> Seq Scan on planet_osm_line w (cost=0.00..49213.10
> > rows=1137488 width=837)
> >  Filter: (highway IS NOT NULL)
> >  -> Index Scan using osm_areas_index on planet_osm_polygon a
> > (cost=0.00..23.10 rows=6 width=824)
> >  Index Cond: (w.way && a.way)
> > (6 rows)  
> >  
> >  
> >  
> >  
> >  
> >  
> >  
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net (mailto:postgis-users at postgis.refractions.net)
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111027/cd4cefc5/attachment.html>