pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18075: configuration variable idle_session_timeout not working as expected
13+ messages / 5 participants
[nested] [flat]
* BUG #18075: configuration variable idle_session_timeout not working as expected
@ 2023-08-29 20:24 PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-30 17:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: PG Bug reporting form @ 2023-08-29 20:24 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: maliansari.coder@gmail.com
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)
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
@ 2023-08-29 21:12 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2023-08-29 21:12 UTC (permalink / raw)
To: maliansari.coder@gmail.com <maliansari.coder@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tuesday, August 29, 2023, PG Bug reporting form <noreply@postgresql.org>
wrote:
> 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
>
> EXTRACT(MINUTE FROM (current_timestamp - state_change)) as duration
>
> datname | pid | query | usename | client_addr | client_port | state |
> backend_xid | duration
> ----------+-----+--------+---------+-------------+----------
> ---+-------+-------------+----------
> postgres | 975 | COMMIT | apiuser | ::1 | 45748 | idle |
>
> | 28
>
>
> 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.
David J.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
@ 2023-08-29 21:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 07:58 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Emile Amewoto <emileam@yahoo.com>
2023-08-30 16:31 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2023-08-29 21:46 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: maliansari.coder@gmail.com <maliansari.coder@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-30 07:58 ` Emile Amewoto <emileam@yahoo.com>
2023-08-30 17:24 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 17:24 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 17:25 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 17:26 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
1 sibling, 4 replies; 13+ messages in thread
From: Emile Amewoto @ 2023-08-30 07:58 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: maliansari.coder@gmail.com <maliansari.coder@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 07:58 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Emile Amewoto <emileam@yahoo.com>
@ 2023-08-30 17:24 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
3 siblings, 0 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 17:24 UTC (permalink / raw)
To: Emile Amewoto <emileam@yahoo.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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@yahoo.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@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
> >
> >
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 07:58 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Emile Amewoto <emileam@yahoo.com>
@ 2023-08-30 17:24 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
3 siblings, 0 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 17:24 UTC (permalink / raw)
To: Emile Amewoto <emileam@yahoo.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
RE Tom: If you check my query I have subtracted state_change timestamp that
is returned by pg_stat_activity, and according to my knowledge state_change
returns the last time the state got changed for the session, and if it's
saying 28 minutes then I suppose this means from 28 minutes the session did
not change state from idle. About you second "how" question, I set the
timeout in postgresql.conf file and didn't do it directly in the session. I
will double check if I made this mistake or not but I remember that I
changed the config and then restarted PostgreSQL service and then checked
if the timeouts got set by querying from them, only then I started my
queries. But I will try it again for making sure that what you said might
be the problem
On Wed, Aug 30, 2023 at 12:58 PM Emile Amewoto <emileam@yahoo.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@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
> >
> >
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 07:58 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Emile Amewoto <emileam@yahoo.com>
@ 2023-08-30 17:25 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
3 siblings, 0 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 17:25 UTC (permalink / raw)
To: Emile Amewoto <emileam@yahoo.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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@yahoo.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@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
> >
> >
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 07:58 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Emile Amewoto <emileam@yahoo.com>
@ 2023-08-30 17:26 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
3 siblings, 0 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 17:26 UTC (permalink / raw)
To: Emile Amewoto <emileam@yahoo.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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@yahoo.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@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
> >
> >
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-08-30 16:31 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-30 17:27 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
1 sibling, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2023-08-30 16:31 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: maliansari.coder@gmail.com <maliansari.coder@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 16:31 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
@ 2023-08-30 17:27 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 22:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 22:14 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 17:27 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
I have no user specific settings. Here check the output.
[image: image.png]
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.
>
>
Attachments:
image.png (image/png, 1.7K) download
view image
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 16:31 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-30 17:27 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
@ 2023-08-30 22:12 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 22:12 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
On Wed, Aug 30, 2023 at 10:27 PM Muhammad Ali Ansari <
maliansari.coder@gmail.com> wrote:
> I have no user specific settings. Here check the output.
> [image: image.png]
>
> 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.
>>
>>
Attachments:
image.png (image/png, 1.7K) download
view image
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 16:31 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected David G. Johnston <david.g.johnston@gmail.com>
2023-08-30 17:27 ` Re: BUG #18075: configuration variable idle_session_timeout not working as expected Muhammad Ali Ansari <maliansari.coder@gmail.com>
@ 2023-08-30 22:14 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 22:14 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
On Wed, Aug 30, 2023 at 10:27 PM Muhammad Ali Ansari <
maliansari.coder@gmail.com> wrote:
> I have no user specific settings. Here check the output.
> [image: image.png]
>
> 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.
>>
>>
Attachments:
image.png (image/png, 1.7K) download
view image
* Re: BUG #18075: configuration variable idle_session_timeout not working as expected
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
@ 2023-08-30 17:12 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Muhammad Ali Ansari @ 2023-08-30 17:12 UTC (permalink / raw)
To: maliansari.coder@gmail.com; pgsql-bugs@lists.postgresql.org
I will be replying here moving forward. Sorry, for the inconvenience.
On Wed, Aug 30, 2023 at 1:25 AM PG Bug reporting form <
noreply@postgresql.org> wrote:
> 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)
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2023-08-30 22:14 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-08-29 20:24 BUG #18075: configuration variable idle_session_timeout not working as expected PG Bug reporting form <noreply@postgresql.org>
2023-08-29 21:12 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-29 21:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-08-30 07:58 ` Emile Amewoto <emileam@yahoo.com>
2023-08-30 17:24 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 17:24 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 17:25 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 17:26 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 16:31 ` David G. Johnston <david.g.johnston@gmail.com>
2023-08-30 17:27 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 22:12 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 22:14 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
2023-08-30 17:12 ` Muhammad Ali Ansari <maliansari.coder@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox