[postgis-devel] Query interruptibility in stable branch

[Sandro Santilli]

On Wed, Jun 06, 2012 at 08:08:18AM -0700, Paul Ramsey wrote:
> I have no objection to code that is #defined as disabled being added.
> In fact I wanted to add some (the new rtree splitting approach prior
> to 2.0) recently.  That being said, why not just maintain a branch at
> github with your changes for client purposes? I've done that (actually
> SVN spikes) many times in the past for folks on MapServer features.

Yes, no problem with maintaining a fork.
Even particularly easy with git.

--strk;

> On Wed, Jun 6, 2012 at 6:51 AM, Sandro Santilli <strk at keybit.net> wrote:
> > 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------'
> >
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-devel
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel

-- 

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