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

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)

On Wed, Aug 30, 2023 at 12:58 PM Emile Amewoto <emileam(at)yahoo(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> >> On Tuesday, August 29, 2023, PG Bug reporting form <
> noreply(at)postgresql(dot)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
> >
> >
>
>