Re: killing processes

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. Yeah, not the
ideal solution in production but on a non-prod machine it's an ok way
to get out of these issues. And even in production, it's often much
faster to kill -9 a single process than to wait for it to finish. I
think there might be choices other than -9 here, but I can't recall
them off the top of my head.