Opened 10 years ago

Closed 5 years ago

#2975 closed defect (fixed)

ST_Intersects with a LineString and MultiPolygon crashes PostgreSQL

Reported by: royseto Owned by: strk
Priority: blocker Milestone: PostGIS GEOS
Component: postgis Version: 2.1.x
Keywords: st_intersects crash memory Cc: royseto

Description

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

Attachments (1)

testcase.tar.gz (272.2 KB ) - added by royseto 10 years ago.
test case

Download all attachments as: .zip

Change History (9)

by royseto, 10 years ago

Attachment: testcase.tar.gz added

test case

comment:1 by royseto, 10 years ago

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.

comment:2 by strk, 10 years ago

Owner: changed from pramsey to strk
Priority: highblocker
Status: newassigned

comment:3 by strk, 10 years ago

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"

comment:4 by royseto, 10 years ago

Cc: royseto added

comment:5 by strk, 10 years ago

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

comment:6 by strk, 10 years ago

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

comment:7 by strk, 10 years ago

Milestone: PostGIS 2.1.5PostGIS GEOS

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() ?

comment:8 by Algunenano, 5 years ago

Resolution: fixed
Status: assignedclosed

This has been fixed for a while.

Note: See TracTickets for help on using tickets.