I managed to make it work as expected by using psycopg library to create connection pool directly and disabling idle_session_timeout on PostgreSQL as psycopg docs suggested that. The problem maybe pgbouncer or maybe some other problem in PostgreSQL. Maybe pgbouncer disables idle_session_timeout of PostgreSQL, but it's not written anywhere.
"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.