Re: BUG #18075: configuration variable idle_session_timeout not working as expected

I will be replying here moving forward. Sorry, for the inconvenience.

On Wed, Aug 30, 2023 at 1:25 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18075
> Logged by: Muhammad Ali Ansari
> Email address: maliansari(dot)coder(at)gmail(dot)com
> PostgreSQL version: 15.4
> Operating system: ubuntu 22.04
> Description:
>
> I have set the idle_session_timeout variable as 60000 making it 60 seconds
> as defined in documentation, I ran some queries in parallel and created
> connections performed transactions and closed the connections, I checked
> the
> connection count it was 27, from which 20 are the ones that were created
> during execution of queries. The idle connections remain there even after
> 60
> seconds timeout has passed. I am using following query to get the
> connections and duration elapsed since state_change.
>
> SELECT
> datname,
> pid,
> query,
> usename,
> client_addr,
> client_port,
> state,
> backend_xid,
> EXTRACT(MINUTE FROM (current_timestamp - state_change)) as duration
> FROM pg_stat_activity
> WHERE
> datname = 'postgres'
> AND pid <> pg_backend_pid()
> AND state = 'idle'
> ORDER BY
> backend_start DESC;
>
> It gives following results:
>
> 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)
>
> 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.
>
> If I get the configuration from following query:
> select name, setting
> from pg_settings
> where
> name = 'idle_session_timeout'
> OR name = 'idle_in_transaction_session_timeout';
>
> it returns this:
>
> name | setting
> --------------------------------------------------+---------
> idle_in_transaction_session_timeout | 60000
> idle_session_timeout | 60000
> (2 rows)
>
>