On Mon, Jul 20, 2009 at 6:48 PM, Scott Marlowe<scott.marlowe@xxxxxxxxx> wrote: > On Mon, Jul 20, 2009 at 4:44 PM, David Kerr<dmk@xxxxxxxxxxxxxx> wrote: >> What's the generally accepted method for killing processes that went 'all wacky' in postgres? >> >> I think i've seen in this group that kill -INT would be the way to go. >> >> I'm playing around with different options for a median function. this one got out of hand >> and was taking too long, so i wanted to kill it: >> >> test=# select array_median(array(select t1 from test2 order by 1)); >> ^CCancel request sent >> >> It just sits there, it's been trying to die for 1/2 an hour. >> >> At the OS it's taking up 100% of the CPU. >> >> I tried kill -INT <pid> but that didn't help. >> >> It's not updating anything, and i'm the only one in the database. >> >> Fortunatly it's not production, so I don't really care. But if it was production, what would >> be the method to kill it? (I know about kill -9, i'm assuming that == bad) >> >> If this were production, I'd need to end the process, force a rollback (if necessary) and get >> my CPU back so "just waiting for it to die" really isn't an option... >> >> (PostgreSQL 8.3.5, linux/SLES11) > > What's most likely happening is that it's stuck in a tight loop that > doesn't check for interrupts, so it just keeps on going. > > You can kill -9 a process. It'll cause the postmaster to kill all > backends and flush the buffers if I remember correctly. That's right -- and if there is any case where built in functions, sql, or pgsql functions get stuck in such a way that a kill -9 is required, it should be reported so that it can be fixed. Trust me, you should avoid kill -9 if at all possible. Backend C functions you write should check interrupts at appropriate places so you can respond to cancels appropriately with CHECK_FOR_INTERRUPTS(); A good example if how this is done, take a look at ITAGAKI's proposed enhancement of dblink and the attached patch here: http://www.nabble.com/query-cancel-issues-in-contrib-dblink-td24214133.html merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
