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

>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(at)comcast(dot)net> wrote:

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