> 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.
these queries are not doing network calls,they have loopback dblink connection with in.
>Not sure though what is relaunching them after you say they were
disabled some{where,how}.
>
> 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.
these queries are not doing network calls,they have loopback dblink connection with in.
>Not sure though what is relaunching them after you say they were
disabled some{where,how}.
Is there any workaround to terminate those stuck process apart from restarting the database ?
On Wed, 25 Sep 2019 at 00:05, Jerry Sievers <gsievers19@xxxxxxxxxxx> wrote:
bhargav kamineni <bhargavpostgres@xxxxxxxxx> 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@xxxxxxxxxxx
