Re: tcp keep alive don't work when the backend is busy

Hi Oner

It appears that you looking for a way to detect and kill of idle
connections or process that are running for a long time Correct??

If that is the case use statement_timeout setting and then use Pg_Agent and
this script to kill off idle connections

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'Database_Name'
AND pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)'
, 'disabled')
AND state_change < current_timestamp - INTERVAL '15' MINUTE;

Statement_Timeout can be set per session/connection

On Tue, Dec 10, 2019 at 7:53 AM Олег Самойлов <splarv(at)ya(dot)ru> wrote:

> According to the documentation
> https://www.postgresql.org/docs/12/runtime-config-connection.html
> A backend must check connection to the client by tcp_keepalive messages.
> (Config option tcp_keepalives_idle).
>
> But this is don't work if the backend is busy.
>
> Simple example:
>
> psql localhost
>
> set tcp_keepalives_idle=1;
> do $$begin loop perform pg_sleep(1);end loop;end;$$;
>
> In other terminal kill -9 the psql on the first terminal.
>
> select * from pg_stat_activity where state='active';
> And we will see that the backend is still active and busy.
>
> The more realistic example. In the real code one of the loops, due to bug
> with asynchronous communication, come to the infinite loop. And occupy a
> backend and locks for a two week after the client was killed, before we
> detected this.
>
>