Re: I can't cancel/terminate query.

Edmundo Robles <edmundo(at)sw-argos(dot)com> writes:

> Hi!  i have  many  too long time queries,  the oldest  is  almost 16
> days, so i  tried to cancel and terminate with pg_cancel_backend and
> pg_terminate_backend  but queries is still running.
>
> STIME     ELAPSED ELAPSED %CPU   PID COMMAND
> jun27 15-23:05:46 1379146  0.3 29660 postgres: argos_admin bdkairos
> 127.0.0.1(55605) SELECT                             
> jun27 15-23:05:46 1379146  0.3 29659 postgres: argos_admin bdkairos
> 127.0.0.1(55604) SELECT                             
> jun27 15-23:05:46 1379146  0.3 29658 postgres: argos_admin bdkairos
> 127.0.0.1(55603) SELECT                             
> jun27 15-23:05:16 1379116  0.3 29909 postgres: argos_admin bdkairos
> 127.0.0.1(55767) SELECT                             
> jun27 15-23:04:51 1379091  0.3 30097 postgres: argos_admin bdkairos
> 127.0.0.1(55909) SELECT                             
> jun27 15-23:03:49 1379029  0.3 30661 postgres: argos_admin bdkairos
> 127.0.0.1(56303) SELECT        
> ...
> What should  i do to safely close that  queries, before to use kill
> command in linux? 
>
> I have Postgres 9.4  running on debian jessie.

Try stracing the catatonic backend. You will probably find it hung in
a network send like sendv' or somesuch.

The client side of such a connection is probably still alive but can't
take any more data perhaps due to being resource starved. Fully
shutdown such a client if you can locate it.

If this is a no-can-do, there's a way to forge a network packet to
close the blocked socket which you'll have to Google for if interested.

Else you will have to shutdown -m immediate. Sorry.

>  
>
>
> --
> [uc]
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800