[postgis-users] memory bug in postgis query

[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
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)









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