Обсуждение: Auto close idle connections for specific user (not by pg_cancelcommand)
Hello,
In psql, may I know is there a way/config that idle connections could be auto closed (but not by pg_cancel_backend command) for specific user(s) please?
For example,
select usename, waiting, query_start from pg_stat_activity
where usename like 'connUser1' and
current_query='<IDLE>';
All connections from connUser1, if idle more than 1 day, psql auto close them.
Thanks a lot.
In psql, may I know is there a way/config that idle connections could be auto closed (but not by pg_cancel_backend command) for specific user(s) please?
For example,
select usename, waiting, query_start from pg_stat_activity
where usename like 'connUser1' and
current_query='<IDLE>';
All connections from connUser1, if idle more than 1 day, psql auto close them.
Thanks a lot.
Hi
út 5. 2. 2019 v 18:47 odesílatel Emi <emilu@encs.concordia.ca> napsal:
Hello,
In psql, may I know is there a way/config that idle connections could be auto closed (but not by pg_cancel_backend command) for specific user(s) please?
For example,
select usename, waiting, query_start from pg_stat_activity
where usename like 'connUser1' and
current_query='<IDLE>';
All connections from connUser1, if idle more than 1 day, psql auto close them.
Maybe pgbouncer can do this https://pgbouncer.github.io/config.html
client_idle_timeout
Regards
Pavel
Thanks a lot.
Le mar. 5 févr. 2019 à 18:52, Pavel Stehule <pavel.stehule@gmail.com> a écrit :
Hiút 5. 2. 2019 v 18:47 odesílatel Emi <emilu@encs.concordia.ca> napsal:Hello,
In psql, may I know is there a way/config that idle connections could be auto closed (but not by pg_cancel_backend command) for specific user(s) please?
For example,
select usename, waiting, query_start from pg_stat_activity
where usename like 'connUser1' and
current_query='<IDLE>';
All connections from connUser1, if idle more than 1 day, psql auto close them.What I know, there is not any timeout for this purpose.
Maybe pgbouncer can do this https://pgbouncer.github.io/config.html
client_idle_timeoutRegardsPavel
Thanks a lot.
Or you could use
Parameter idle_in_transaction_session_timeout
And use alter role in database set idle_in_transaction_session_timeout to...
Regards
Le mar. 5 févr. 2019 à 18:59, Thomas Poty <thomas.poty@gmail.com> a écrit :
Le mar. 5 févr. 2019 à 18:52, Pavel Stehule <pavel.stehule@gmail.com> a écrit :Hiút 5. 2. 2019 v 18:47 odesílatel Emi <emilu@encs.concordia.ca> napsal:Hello,
In psql, may I know is there a way/config that idle connections could be auto closed (but not by pg_cancel_backend command) for specific user(s) please?
For example,
select usename, waiting, query_start from pg_stat_activity
where usename like 'connUser1' and
current_query='<IDLE>';
All connections from connUser1, if idle more than 1 day, psql auto close them.What I know, there is not any timeout for this purpose.
Maybe pgbouncer can do this https://pgbouncer.github.io/config.html
client_idle_timeoutRegardsPavel
Thanks a lot.
You are right. So it is not suitable for your case. Sorry
Le mar. 5 févr. 2019 à 19:27, Ravi Krishna <srkrishna100@aol.com> a écrit :
>
> https://www.cybertec-postgresql.com/en/idle_in_transaction_session_timeout-terminating-idle-transactions-in-postgresql/
>
> And use alter role in database set idle_in_transaction_session_timeout to...
>
> https://www.postgresql.org/docs/10/sql-alterrole.html
But this works only for those sessions which has started a transaction and not committed within the session_timeout period, right ?