Re: [GENERAL] I can't cancel/terminate query.

[Melvin Davidson]

On Thu, Jul 13, 2017 at 2:45 PM, Edmundo Robles 
wrote:

> i  executed the commands   many times like  superuser but  that queries
> still running :(
>
> On Thu, Jul 13, 2017 at 11:10 AM, Melvin Davidson 
> wrote:
>
>>
>>
>> On Thu, Jul 13, 2017 at 11:57 AM, Edmundo Robles 
>> wrote:
>>
>>> 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.
>>>
>>>
>>>
>>> --
>>>
>>>
>> T
>>
>> *o cancel a process with pg_terminate_backend, you need to be a
>> superuser.*
>> *So first;*
>>
>> *SET ROLE postgres;*
>>
>> Then you should be able to
>> *SELECT **pg_terminate_backend(); *  Where  is the pid of
>> process you want to termnate.
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
>
> --
>
>



*If you are running on Linux, then:*

*sudo su*

*kill  *

*Where  is the same as in pg_terminate_backend() *

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] I can't cancel/terminate query.

[Jerry Sievers]

Edmundo Robles  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.consult...@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] I can't cancel/terminate query.

[Melvin Davidson]

On Thu, Jul 13, 2017 at 11:57 AM, Edmundo Robles 
wrote:

> 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.
>
>
>
> --
>
>
T

*o cancel a process with pg_terminate_backend, you need to be a superuser.*
*So first;*

*SET ROLE postgres;*

Then you should be able to
*SELECT **pg_terminate_backend(); *  Where  is the pid of process
you want to termnate.
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] I can't cancel/terminate query.

[Edmundo Robles]

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.



--