Thread: How to terminate a query

Hi there!

select * from pg_stat_activity;
shows me a
  select my_function(....)
query that has been running for too long.
How do I kill it?

kill -9 of the procpid seems  to kill the entire server process. So
I'm not really comfortable with that.
The query was started by a webscript that was closed a long time ago.

So how do you stop them? (This is on a Linux box)

On Tue, Apr 20, 2010 at 2:42 PM, A B <gentosaker@gmail.com> wrote:
> Hi there!
>
> select * from pg_stat_activity;
> shows me a
>  select my_function(....)
> query that has been running for too long.
> How do I kill it?

select pg_cancel_backend(pid);
will kill a running query.

> kill -9 of the procpid seems  to kill the entire server process. So

kill -9 is NOT recommended.

Sometimes cancel_backend fails because there's no point where the code
checks for a cancel.

> select pg_cancel_backend(pid);
> will kill a running query.

Thanks.

> Sometimes cancel_backend fails because there's no point where the code
> checks for a cancel.

What would such a point in the code look like?

On Wed, Apr 21, 2010 at 1:59 AM, A B <gentosaker@gmail.com> wrote:
>> Sometimes cancel_backend fails because there's no point where the code
>> checks for a cancel.
>
> What would such a point in the code look like?

For instance, if your backend is waiting on a socket call in libc, as
in this thread:
http://archives.postgresql.org/pgsql-general/2010-03/msg00663.php

Josh