Re: backend hangs at sendto() and can't be terminated

we do select pg_cancel_backend(8243) several times,but the backend still hang there.

Jov

blog: http:amutu.com/blog

2013/7/8 Bill Mitchell <bill@publicrelay.com>

You can do select pg_cancel_backend(8243);

and that should terminate that process that is sending, but still leave your postgres server healthy.

regards,
Bill

On 7/8/13 5:31 AM, Jov wrote:

one of our pg user send a select * from 10 million table without limit from psql，before get the return data，he realize the mistake and quite from psql.but after 2 hours,the sql still alive:

postgres=# SELECT * from pg_stat_activity where procpid = 8243;

-[ RECORD 1 ]----+---------------------------------

datid            | 758972

datname          | xxx

procpid          | 8243

usesysid         | 661846

usename          | test

application_name | psql

client_addr      | 10.136.4.90

client_hostname  | 

client_port      | 6382

backend_start    | 2013-07-08 14:11:00.942293+08

xact_start       | 2013-07-08 14:31:11.157681+08

query_start      | 2013-07-08 14:31:11.157681+08

waiting          | f

current_query    | select * from yyyy;

pg_terminate_backend return t but the backend still there.

strace the pid show the process hang at sendto() function call:

[postgres@xxx ~]$ strace -tv -p 8243

Process 8243 attached - interrupt to quit

17:02:26 sendto(10, "70804\0\0\0\f{2012070804}D\0\0\0`\0\5\0\0\0\1"..., 8152, 0, NULL, 0^C <unfinished ...>

Process 8243 detached

os is centos 6 x86-64,pg version is 9.1.9.

the process still there,how can I help to debug the problem?

Jov

blog: http:amutu.com/blog