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

Hmm..
In that case, I think that select pg_terminate_backend() might be in order?

http://www.postgresql.org/docs/9.1/static/functions-admin.html

regards,
Bill

On 7/8/13 5:46 AM, Jov wrote:
> we do select pg_cancel_backend(8243) several times,but the backend
> still hang there.
>
> Jov
> blog: http:amutu.com/blog <http://amutu.com/blog>
>
>
> 2013/7/8 Bill Mitchell <bill(at)publicrelay(dot)com
> <mailto:bill(at)publicrelay(dot)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(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>
>
>