Re: BUG #18075: configuration variable idle_session_timeout not working as expected - Mailing list pgsql-bugs
| From | Muhammad Ali Ansari |
|---|---|
| Subject | Re: BUG #18075: configuration variable idle_session_timeout not working as expected |
| Date | |
| Msg-id | CANE=mQyMvyaesx9z7nChi6YqWygHwmCsUfcddm3Kmy3OuX3wGg@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #18075: configuration variable idle_session_timeout not working as expected (Emile Amewoto <emileam@yahoo.com>) |
| List | pgsql-bugs |
RE David: David you are looking at the client port column, I copy pasted this result from psql session let me reformat it for you to better understand it. Check the duration column in query and in result. In query I have used this EXTRACT(MINUTE FROM (current_timestamp - state_change)) as duration for getting duration column, this subtracts the state_change timestamp of connections returned by pg_stat_activity from current timestamp and then Iam using EXTRACT function to extract minutes from the resultant which is resulting in 28 minutes, the query result I have given is only to support my problem with proof, otherwise I experienced this and I was monitoring it to check when it will close these idle connections and after 28 minutes I thought of reporting it. And yes I first changed the timeout, then restarted PostgreSQL service with that setting, then I ran my queries.
datname | pid | query | usename| client_addr | client_port | state |backend_xid | duration
-------------+-----+-------------+------------+---------------+---------------+-------+-----------------+----------
postgres | 975 | COMMIT | apiuser | ::1 | 45748 | idle | | 28
postgres | 974 | COMMIT | apiuser | ::1 | 45738 | idle | | 28
postgres | 973 | COMMIT | apiuser | ::1 | 45724 | idle | | 28
postgres | 972 | COMMIT | apiuser | ::1 | 45718 | idle | | 28
postgres | 971 | COMMIT | apiuser | ::1 | 45714 | idle | | 28
postgres | 970 | COMMIT | apiuser | ::1 | 45698 | idle | | 28
postgres | 969 | COMMIT | apiuser | ::1 | 45696 | idle | | 28
postgres | 968 | COMMIT | apiuser | ::1 | 45686 | idle | | 28
postgres | 967 | COMMIT | apiuser | ::1 | 45670 | idle | | 28
postgres | 966 | COMMIT | apiuser | ::1 | 45658 | idle | | 28
postgres | 965 | COMMIT | apiuser | ::1 | 45648 | idle | | 28
postgres | 964 | COMMIT | apiuser | ::1 | 45634 | idle | | 28
postgres | 963 | COMMIT | apiuser | ::1 | 45620 | idle | | 28
postgres | 962 | COMMIT | apiuser | ::1 | 45612 | idle | | 28
postgres | 961 | COMMIT | apiuser | ::1 | 45608 | idle | | 28
postgres | 960 | COMMIT | apiuser | ::1 | 45606 | idle | | 28
postgres | 959 | COMMIT | apiuser | ::1 | 45592 | idle | | 28
postgres | 958 | COMMIT | apiuser | ::1 | 45582 | idle | | 28
postgres | 957 | COMMIT | apiuser | ::1 | 45574 | idle | | 28
postgres | 956 | COMMIT | apiuser | ::1 | 45566 | idle | | 28
(20 rows)
datname | pid | query | usename| client_addr | client_port | state |backend_xid | duration
-------------+-----+-------------+------------+---------------+---------------+-------+-----------------+----------
postgres | 975 | COMMIT | apiuser | ::1 | 45748 | idle | | 28
postgres | 974 | COMMIT | apiuser | ::1 | 45738 | idle | | 28
postgres | 973 | COMMIT | apiuser | ::1 | 45724 | idle | | 28
postgres | 972 | COMMIT | apiuser | ::1 | 45718 | idle | | 28
postgres | 971 | COMMIT | apiuser | ::1 | 45714 | idle | | 28
postgres | 970 | COMMIT | apiuser | ::1 | 45698 | idle | | 28
postgres | 969 | COMMIT | apiuser | ::1 | 45696 | idle | | 28
postgres | 968 | COMMIT | apiuser | ::1 | 45686 | idle | | 28
postgres | 967 | COMMIT | apiuser | ::1 | 45670 | idle | | 28
postgres | 966 | COMMIT | apiuser | ::1 | 45658 | idle | | 28
postgres | 965 | COMMIT | apiuser | ::1 | 45648 | idle | | 28
postgres | 964 | COMMIT | apiuser | ::1 | 45634 | idle | | 28
postgres | 963 | COMMIT | apiuser | ::1 | 45620 | idle | | 28
postgres | 962 | COMMIT | apiuser | ::1 | 45612 | idle | | 28
postgres | 961 | COMMIT | apiuser | ::1 | 45608 | idle | | 28
postgres | 960 | COMMIT | apiuser | ::1 | 45606 | idle | | 28
postgres | 959 | COMMIT | apiuser | ::1 | 45592 | idle | | 28
postgres | 958 | COMMIT | apiuser | ::1 | 45582 | idle | | 28
postgres | 957 | COMMIT | apiuser | ::1 | 45574 | idle | | 28
postgres | 956 | COMMIT | apiuser | ::1 | 45566 | idle | | 28
(20 rows)
On Wed, Aug 30, 2023 at 12:58 PM Emile Amewoto <emileam@yahoo.com> wrote:
Hi David,
PostgreSQL does create and maintain connections on request because it is “expensive” to create new connections. Wouldn’t possible in your case to control the idle connections from the apps requesting connections? Things like reducing app thread pool? PostgreSQL ignore (rightfully) some of the configs even if it is set.
Regards,
Emile
> On 29 Aug 2023, at 23:46, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> On Tuesday, August 29, 2023, PG Bug reporting form <noreply@postgresql.org>
>> wrote:
>>> I have set the idle_session_timeout variable as 60000 making it 60 seconds
>>> As we can see, it shows that the time is way more than 1 minute now it is
>>> 28 minutes and they are still idle and still open in postgres and not
>>> disconnected as expected.
>
>> Not sure how you got 28 minutes from 45748…which is large enough that it is
>> probable those sessions started before you changed the timeout and so are
>> not affected by it.
>
> I believe idle_session_timeout is consulted when the session goes
> idle, and we either set a timeout interrupt or not. The prevailing
> value might change after that, but it won't affect existing sessions
> until they next go idle. I do not regard that as a bug.
>
> Also, the OP didn't say *how* he set idle_session_timeout. That
> pg_settings extract only proves that 60000 is the prevailing value in
> the session where that was done. It's possible that the value was
> only set locally, or in some other way that didn't affect those other
> sessions at all.
>
> regards, tom lane
>
>
pgsql-bugs by date: