query leads to memory exhaustion

[akrherz]

Hi,

I am attaching a test case where this query will lead to server memory exhaustion and perhaps a crash, I have never let it get that far! My server has 16 GB of memory and it will eat it all, my test box as 4 GB and likewise.

My production environment is now:

RHEL5.3 x86_64

POSTGIS="1.3.6" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.6.0, 21 Dec 2007" USE_STATS

Postgres 8.3.7

I am probably doing something wrong, but it would still be nice for it not to take down the server in the process! hehe.

thanks from a very happy Postgis user!

daryl

[akrherz]

Zip file containing reproducer

[robe]

I confirmed this happens on Windows as well. Haven't tested on a lower version of GEOS to see if its a new problem or a long existing one. I'm assuming its probably a GEOS bug.

[akrherz]

Thanks! It happens on GEOS 3.0.3 as well.

[robe]

I tried this on windows 3.0.3 and 1.3.5 and does the same thing. I haven't dissected your query, but if I were to guess I would say the most likely culprit is buffer.

I know some fixes were made to buffer in GEOS 3.1.1 (not yet released), so perhaps some of those changes fix this problem - none of the symptoms of geos buffer trac issues match this as far as I can tell but that doesn't mean they aren't related to the other issues reported/fixed.

Haven't tested against the GEOS 3.1.1 svn yet, but if you get a chance before I do feel free to.

[akrherz]

Thanks again! I built GEOS svn nightly (downloaded this morning) and my reproducer no longer reproduces

daryl

[robe]

Darly,

Great. Yah I was about to say I confirmed my suspicions that this is caused by buffer, because the below query runs quickly.

So I'll close this out and mark it as fixed in GEOS 3.1.1

SELECT

askml(setsrid(a,4326)) as kml, length(transform(a,2163)) as sz from (

SELECT

intersection(

exteriorring(geometryn(multi(geomunion(n.geom)),1)), exteriorring(geometryn(multi(geomunion( GeomFromEWKT('SRID=4326;MULTIPOLYGON(((-66.35 18.27,-66.27 18.32,-66.1 18.33,-66.06 18.3,-66.03 18.35,-65.97 18.34,-65.94 18.23,-66.34 18.18,-66.35 18.27)))') )),1))

) as a FROM nws_ugc n WHERE ST_OverLaps(n.geom,

GeomFromEWKT('SRID=4326;MULTIPOLYGON(((-66.35 18.27,-66.27 18.32,-66.1 18.33,-66.06 18.3,-66.03 18.35,-65.97 18.34,-65.94 18.23,-66.34 18.18,-66.35 18.27)))')

) and n.ugc IN ('PRC007', 'PRC025', 'PRC019', 'PRC061', 'PRC021',

'PRC041', 'PRC045', 'PRC063', 'PRC105', 'PRC139', 'PRC047'

)

) as foo

WHERE not isempty(a);

[akrherz]

Hi,

Unfortunately, I am back with a reproducer again, with postgresql 8.4.1

POSTGIS="1.4.0" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August 2008" USE_STATS

select askml(setsrid(a,4326)) as kml, length(transform(a,2163)) as sz

from (

select

intersection(

buffer(exteriorring(geometryn(multi(geomunion(n.geom)),1)),0.02), exteriorring(geometryn(multi(geomunion(GeomFromEWKT('SRID=4326;MULTIPOLYGON(((-66.22 18.38,-66.26 18.42,-66.25 18.47,-65.93 18.37,-65.97 18.29

,-66.22 18.38)))') )),1))

) as a from nws_ugc n WHERE ST_OverLaps(n.geom, GeomFromEWKT('SRID=4326;MULTIPOLYGON(((-66.22 18.38,-66.26 18.42,-66.25 18.47,-65.93 18.37,-65.97 18.29,-66.22 18.38)))') ) and n.ugc IN ('PRC139', 'PRC031', 'PRC061', 'PRC021', 'PRC137','PRC127')

) as foo

WHERE not isempty(a)

Does that query blow up for you too? TIA!

[robe]

Akrherz,

It runs out of memory for me too in my GEOS 3.1.1 install, but works fine in my GEOS 3.2.0 SVN. So I think this is another GEOS bug that can be resolved by upgrading to GEOS 3.2 trunk (not sure if the latest GEOS 3.1 branch has the fix since I don't have that readily compiled).

On a side note we don't have geomunion in PostGIS 1.4 or at least don't ahve it in my install, so I suspect unless you hacked your install, that geomunion function you have is from an older PostGIS install. You should replace with st_union since its a much faster function and also your query will break should you ever get rid of your old lwgeom 1.3 so