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

Bill Mitchell <bill@xxxxxxxxxxxxxxx> · Mon, 08 Jul 2013 05:51:46 -0400



    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
          
          
          2013/7/8 Bill Mitchell <bill@xxxxxxxxxxxxxxx>

            
               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