[postgis-users] Query that locks up system memory/disk usage

[Mike Leahy]

Hello list,

I've run into some situations where running certain queries end up 
locking up all of my system's ram memory, with constant disk access.  I 
can't cancel the query by hitting ctrl+c in the psql terminal, by 
restarting the service, or even killing the postmaster.  I'm running on 
a fairly high end system, so it's not an issue with CPU power or 
available ram.  Here's an example of what I did today that caused this:

Table A is a table I imported from a tile index shapefile.

Table B has several fairly large irregular polygons of different study 
areas.

To get all of the polygons in Table A within a certain distance (50km) 
of one of the polygons in Table B, without giving it much thought I did 
the following:

select * from a where st_intersects(a.the_geom,buffer((select b.the_geom 
where gid = 10),50000));

I realize how wrong that is, as calculates the buffer for every tile it 
compares to...I should have done something like:

select * from a where st_intersects(a.the_geom,(select b.the_geom where 
gid = 10)) or st_distance(a.the_geom,(select b.the_geom where gid = 
10))<=50000;

The problem is...I'm still waiting for the first query to either finish, 
or cancel, or something.  In the meantime, postmaster is still using 99% 
of my memory, and the disk is still thrashing away (though CPU usage 
pretty much at 0).  What's the best strategy to kill the previous query 
without having to shut down the entire server?

Keep in mind that is just an example of how this can happen for me - 
I've had it happen in other more complex situations where it was less 
obvious what I was doing wrong in the logic of the query.  I'm just 
wondering how I can recover from these sorts of mistakes without 
potentially damaging the database.

Regards,
Mike