Re: pg_terminate_backend not working

Any thoughts on this ?

On Tue, 24 Sep 2019 at 16:44, bhargav kamineni <bhargavpostgres(at)gmail(dot)com>
wrote:

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