Re: pg_terminate_backend not working

bhargav kamineni <bhargavpostgres(at)gmail(dot)com> writes:

> Hi Team ,
>
> We are observing long running process hung in active state on one of
> our db from last two days with usename 'xyz', We have tried killing
> the respective pid with pg_terminate_backned(pid), it's returning
> true but the quires are not actually being terminated. Based on the
> client address and port number from pg_stat_activity  we found the
> connection origin and killed that connection initiation process from
> that host , even after that the connections on db from   'xyz' are
> not getting terminated.

Your client backend is most likely sitting in a blocked sys call such as
network send, etc.

Not sure though what is relaunching them after you say they were
disabled some{where,how}.

HTH

>
> and these connections are not making network calls a self dblink
> connection is being established in the query.
>
> as a workaround we have disabled the connections initiation script
> for xyz user and restarted the database  right after restart we
> observed the new connections from xyz user to database which is not 
> expected because as we have  already disables the connections
> initiation script.
>
> select  pg_postmaster_start_time();
>    pg_postmaster_start_time  
> ------------------------------
>  2019-09-24 06:44:07.41594+00
>
> select usename,backend_start from pg_stat_activity where now()
> -xact_start > '3 hours' and usename ='xyz';
>      usename     |         backend_start                            
>                    
> -----------------+----------------------------
>  xyz | 2019-09-24 06:44:38.879047+00 |
>  xyz | 2019-09-24 06:44:38.880501+00 |
>  xyz | 2019-09-24 06:44:38.881326+00 |
>  xyz | 2019-09-24 06:44:38.877561+00 |
>  xyz | 2019-09-24 06:44:38.878336+00 | 
>
> what could be the possible reason for these connections and
> pg_termiante_backend(pid) not working ?
>
> Thanks, 
> Bhargav K  
>
>
>
>

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