Re: killing processes

On Mon, Jul 20, 2009 at 6:48 PM, Scott Marlowe<scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Mon, Jul 20, 2009 at 4:44 PM, David Kerr<dmk(at)mr-paradox(dot)net> 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