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