pg_terminate_backend not working

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

bhargav kamineni <bhargavpostgres@gmail.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.consulting@comcast.net