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

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(at)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 <http://amutu.com/blog>