ST_Intersects with a LineString and MultiPolygon crashes PostgreSQL

[royseto]

ST_Intersects with a LineString that has 52229 points and a MultiPolygon that has 509 points exhausts all the memory on our PostgreSQL server until the database crashes. On our server with 72GB of memory, this happens in 20-60 minutes.

I am attaching a test case with the LineString and MultiPolygon encoded as HEX WKB text files. It includes a SQL version of the test case as well as a standalone test case that calls GEOSIntersects() from the GEOS C API.

PostgreSQL version: PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

PostGIS full version: POSTGIS="2.1.4 r12966" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.11.1, released 2014/09/24" LIBXML="2.7.8" LIBJSON="UNKNO WN" RASTER

OS: Ubuntu 12.04

[royseto]

test case

[royseto]

Also note that we were unable to cancel the problem query in this testcase using pg_cancel_backend(), pg_terminate_backend(), or kill -TERM <postgres background process>. We had to do "kill -9 <postgres background process> which causes the postmaster to terminate all other server processes as well.

[strk]

Confirmed. Uninterruptible, taking up all memory and eventually killing the backend (the latter depends on overcommit memory being used).

Tested with POSTGIS="2.2.0dev r13108" GEOS="3.5.0dev-CAPI-1.9.0 r4002"

[strk]

I'm working on the interruptibility side of this. See the GEOS ticket: ​http://trac.osgeo.org/geos/ticket/708

[strk]

GEOS interruptability will be first available with release 3.4.3: ​http://trac.osgeo.org/geos/ticket/711

[strk]

r13122 adds tests for interruptibility of ST_Intersects (among all the other relate-based functions) to PostGIS.

Royseto, can you confirm that with the latest GEOS version (built from either trunk of 3.4 branch) you can now cancel the problem query with pg_cancel_backend() ?

[Algunenano]

This has been fixed for a while.