Обсуждение: BUG #18075: configuration variable idle_session_timeout not working as expected

The following bug has been logged on the website:

Bug reference:      18075
Logged by:          Muhammad Ali Ansari
Email address:      maliansari.coder@gmail.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)

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
>
>

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
>
>

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
>
>

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
>
>

On Tue, Aug 29, 2023 at 2:46 PM 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.

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.

Yeah, the fact all of those are for "apiuser" suggests to me that user has their own setting for this which takes precedence over the server default.

The output of \drds would be more informative than just looking at some unknown session's value.

David J.

I have no user specific settings. Here check the output.

On Wed, Aug 30, 2023 at 9:32 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Aug 29, 2023 at 2:46 PM 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.

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.

Yeah, the fact all of those are for "apiuser" suggests to me that user has their own setting for this which takes precedence over the server default.

The output of \drds would be more informative than just looking at some unknown session's value.

David J.

I have no user specific settings. Here check the output.

On Wed, Aug 30, 2023 at 9:32 PM David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Aug 29, 2023 at 2:46 PM 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.

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.

Yeah, the fact all of those are for "apiuser" suggests to me that user has their own setting for this which takes precedence over the server default.

The output of \drds would be more informative than just looking at some unknown session's value.

David J.