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

RE Emile: I am using pgbouncer in front of postgresql for connection
pooling and I have set the timeouts in pgbouncer too, I checked and those
timeouts are working perfectly, if I reuse pgbouncer connections within my
timeouts it doesn't create new connections and reuse the existing
connections, but once the time runs out for idle connections it closes the
connections to PostgreSQL, but if I check on PostgreSQL the connections
remain open in idle state. Even if there is problem with pgbouncer that it
might be keeping connections open on backend, I think PostgreSQL should
behave as expected by closing idle connections once the timeout hits. Also
if what you are saying about PostgreSQL ignoring some configs is correct
then can you point me to where it's written in docs and also is this the
case with idle_session_timeout?

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