[postgis-devel] Query interruptibility in stable branch

[Sandro Santilli]

Moving the discussion on #1802 here.

The problem that ticket is trying to address is the uninterruptibility
of some processor and memory intensive queries.

An example such query is:

 select st_buffer(st_collect(geom), 1e5, 15) from (
   select (st_dumppoints(st_buffer(st_point(0,0), 1e4, 150))).geom
 ) as foo;

If you run that query the memory used by postgres will start growing
and the CPU will be spinning at 100% for more than a few seconds
(around one minute here). Note that this is a simple example, I've 
found myself more than once in need to send a SIGKILL to the backend
in order to get control of the machine back.

Normally, hitting ^C, or setting a timeout (set statement_timeout)
helps you in getting out of troubles. But some functions just don't
obey to that command. All the GEOS implemented function are of this
kind. Ironically, they are usually the ones that make your CPU spin
and drink all your memory.

Now, starting with GEOS-3.4.0 (unreleased yet), there is a way to 
request interruption of running operations, but it is up to the
client code to request such interruption.

I've committed code in trunk to request the interruption of GEOS
operations on receiving SIGINT, which is the signal received on
hitting ^C or expiring the statement_timeout time. The code in
trunk always installs the signal handler, but only request
interruption of GEOS if supported (GEOS-3.4.0+ at build time).

In the ticket we're debating whether to make this functionality
available in the 2.0 branch or not. 

For now I committed a _disabled_ version of the code in the 2.0 branch
that you can bring up by defining a macro. The version in 2.0, if enabled,
is less invasive in that it doesn't install the signal handler unless
GEOS version is 3.4.0 or higher (SVN, at time of writing).

I'm ready to revert the change, but I don't think it hurts anyone,
being disabled by default, while it will help anyone willing to retain
the power to interrupt an harmful query w/out playing gunfighting nor
jumping on the 2.1 branch (which means giving up stability).

Mark gave his +1 for reversion. I'll be -0 here.

--strk; 

  ,------o-. 
  |   __/  |    Delivering high quality PostGIS 2.0 !
  |  / 2.0 |    http://strk.keybit.net - http://vizzuality.com
  `-o------'