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

Hey Tom, I tried again and I can confirm now that idle connections are not
respecting timeouts. After trying my queries again. And then waiting for
idle connections to close. and then I run this query:
SELECT
datname,
pid,
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 ASC
LIMIT 20;
I got the results as follows:
datname | pid | usename | client_addr | client_port | state |
backend_xid | duration
-------------+---------+--------------+-------------+-----------------+-------+-----------------+----------
postgres | 18868 | postgres | ::1 | 42718 | idle
| | 25
postgres | 18875 | apiuser | ::1 | 36898 | idle
| | 23
postgres | 18876 | apiuser | ::1 | 36906 | idle
| | 23
postgres | 18877 | apiuser | ::1 | 36918 | idle
| | 23
postgres | 18878 | apiuser | ::1 | 36920 | idle
| | 23
postgres | 18879 | apiuser | ::1 | 36928 | idle
| | 20
postgres | 18880 | apiuser | ::1 | 36940 | idle
| | 23
postgres | 18881 | apiuser | ::1 | 36950 | idle
| | 23
postgres | 18882 | apiuser | ::1 | 36958 | idle
| | 23
postgres | 18883 | apiuser | ::1 | 36962 | idle
| | 23
postgres | 18884 | apiuser | ::1 | 36978 | idle
| | 23
postgres | 18885 | apiuser | ::1 | 36994 | idle
| | 23
postgres | 18886 | apiuser | ::1 | 37010 | idle
| | 23
postgres | 18887 | apiuser | ::1 | 37020 | idle
| | 23
postgres | 18888 | apiuser | ::1 | 37022 | idle
| | 23
postgres | 18889 | apiuser | ::1 | 37024 | idle
| | 23
postgres | 18890 | apiuser | ::1 | 37026 | idle
| | 23
postgres | 18891 | apiuser | ::1 | 37040 | idle
| | 23
postgres | 18892 | apiuser | ::1 | 37056 | idle
| | 23
postgres | 18893 | apiuser | ::1 | 37068 | idle
| | 23
(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
> >
> >
>
>