I've got a simple select query that runs every 10 minutes in order to
update data in some external rrds (it lets us make pretty graphs and so
forth). This has been working fine for months on end, when suddenly
yesterday the badness happen. For some reason, this same query that
normally takes a couple seconds has now been stuck running for over 24
hours, maxing the CPU and generally slowing other queries down.
The external script that initiates the query has been restarted, and
netstat no longer shows that connection. All subsequent calls of the
same query are quick as usual, but the renegade process lingers on,
unresponsive to signals. Some of the things I've tried so far
(unsuccessfully):
1. I've tried killing the process using kill from the command-line (INT,
TERM and HUP), as well as using pg_cancel_backend() via psql.
2. I've tried attaching gdb to the renegade process to see what it's
doing, but that hangs, forcing me to kill gdb (no problems attaching to
other postgres processes however).
Any other ideas? I'd like to avoid doing a kill -9 if at all possible.
The machine is debian (sarge) running postgres 8.1.
Jon