RE: Terminate the idle sessions

Dear Tom, > So I propose to change the new ERRCODE_IDLE_SESSION_TIMEOUT to be in > class 57 and call it good. I agreed your suggestion and I confirmed your commit. Thanks! Hayato Kuroda FUJITSU LIMITED

Re: Terminate the idle sessions

Thomas Munro writes: > On Thu, Jan 7, 2021 at 4:51 PM Tom Lane wrote: >> Thomas Munro writes: >>> One of the strange things about these errors is that they're >>> asynchronous/unsolicited, but they appear to the client to be the >>> response to their next request (if it doesn't eat ECONNRESET

Re: Terminate the idle sessions

On Thu, Jan 7, 2021 at 4:51 PM Tom Lane wrote: > Thomas Munro writes: > > One of the strange things about these errors is that they're > > asynchronous/unsolicited, but they appear to the client to be the > > response to their next request (if it doesn't eat ECONNRESET instead). > > Right, which

Re: Terminate the idle sessions

-- Best regards Japin Li On Jan 7, 2021, at 10:03 AM, Thomas Munro mailto:thomas.mu...@gmail.com>> wrote: * I'm not entirely comfortable with the name "idle_session_timeout", because it sounds like it applies to all idle states, but actually it only applies when we're not in a transaction. I

Re: Terminate the idle sessions

Thomas Munro writes: > One of the strange things about these errors is that they're > asynchronous/unsolicited, but they appear to the client to be the > response to their next request (if it doesn't eat ECONNRESET instead). Right, which is what makes class 57 (operator intervention) seem

Re: Terminate the idle sessions

On Thu, Jan 7, 2021 at 3:03 PM Thomas Munro wrote: > On Thu, Jan 7, 2021 at 12:55 PM Tom Lane wrote: > > * The SQLSTATE you chose for the new error condition seems pretty > > random. I do not see it in the SQL standard, so using a code that's > > within the spec-reserved code range is certainly

Re: Terminate the idle sessions

On Thu, Jan 7, 2021 at 12:55 PM Tom Lane wrote: > * Thomas' patch for improving timeout.c seems like a great idea, but > it did indeed have a race condition, and I felt the comments could do > with more work. Oops, and thanks! Very happy to see this one in the tree. > * I'm not entirely

Re: Terminate the idle sessions

Li Japin writes: > [ v9-0001-Allow-terminating-the-idle-sessions.patch ] I've reviewed and pushed this. A few notes: * Thomas' patch for improving timeout.c seems like a great idea, but it did indeed have a race condition, and I felt the comments could do with more work. * I'm not entirely

Re: Terminate the idle sessions

On 25.11.2020 05:18, Li Japin wrote: On Nov 24, 2020, at 11:20 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Nov 23, 2020 at 11:22 PM Li Japin > wrote: How about use “foreign-data wrapper” replace “postgres_fdw”? I don't see

Re: Terminate the idle sessions

On Nov 24, 2020, at 11:20 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Nov 23, 2020 at 11:22 PM Li Japin mailto:japi...@hotmail.com>> wrote: How about use “foreign-data wrapper” replace “postgres_fdw”? I don't see much value in avoiding mentioning that specific

Re: Terminate the idle sessions

On Mon, Nov 23, 2020 at 11:22 PM Li Japin wrote: > > How about use “foreign-data wrapper” replace “postgres_fdw”? > I don't see much value in avoiding mentioning that specific term - my proposal turned it into an example instead of being exclusive. > - This parameter should be set to

Re: Terminate the idle sessions

Hi, David Thanks for your suggestion! On Nov 24, 2020, at 11:39 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Nov 23, 2020 at 5:02 PM kuroda.hay...@fujitsu.com mailto:kuroda.hay...@fujitsu.com>> wrote: No one have any comments,

Re: Terminate the idle sessions

ITED > > -Original Message- > From: kuroda.hay...@fujitsu.com > Sent: Friday, November 20, 2020 11:05 AM > To: 'japin' > Cc: David G. Johnston ; Kyotaro Horiguchi > ; Thomas Munro ; > bharath.rupireddyforpostg...@gmail.com; pgsql-hackers@lists.postgresql.org

Re: Terminate the idle sessions

On Mon, Nov 23, 2020 at 5:02 PM kuroda.hay...@fujitsu.com < kuroda.hay...@fujitsu.com> wrote: > No one have any comments, patch tester says OK, and I think this works > well. > I changed status to "Ready for Committer." > Some proof-reading: v8-0001 Documentation: My suggestion wasn't taken

RE: Terminate the idle sessions

; Kyotaro Horiguchi ; Thomas Munro ; bharath.rupireddyforpostg...@gmail.com; pgsql-hackers@lists.postgresql.org Subject: RE: Terminate the idle sessions Dear Li, > Thanks! Add the comment for idle-session timeout. I confirmed it. OK. I don't have any comments anymore. If no one has,

RE: Terminate the idle sessions

Dear Li, > Thanks! Add the comment for idle-session timeout. I confirmed it. OK. I don't have any comments anymore. If no one has, I will change the status few days later. Other comments or suggestions to him? Best Regards, Hayato Kuroda FUJITSU LIMITED

Re: Terminate the idle sessions

hi, Kuroda On 11/19/20 4:32 PM, kuroda.hay...@fujitsu.com wrote: Dear Li, Thanks for your suggestion.  Attached! I prefer your comments:-). I think this patch is mostly good. I looked whole the codes again and I found the following comment in the PostgresMain(): ```c /*

RE: Terminate the idle sessions

Dear Li, > Thanks for your suggestion.  Attached! I prefer your comments:-). I think this patch is mostly good. I looked whole the codes again and I found the following comment in the PostgresMain(): ```c /* * (5) turn off the idle-in-transaction timeout

Re: Terminate the idle sessions

On Nov 18, 2020, at 2:22 PM, kuroda.hay...@fujitsu.com wrote: Oops.. I forgot putting my suggestion. Sorry. How about substituting sigalrm_delivered to true in the reschedule_timeouts()? Maybe this processing looks strange, so some comments should be put too.

RE: Terminate the idle sessions

Dear Li, > Yeah, it might be occurred. Any suggestions to fix it? Oops.. I forgot putting my suggestion. Sorry. How about substituting sigalrm_delivered to true in the reschedule_timeouts()? Maybe this processing looks strange, so some comments should be put too. Here is an example: ```diff @@

Re: Terminate the idle sessions

On Nov 18, 2020, at 10:40 AM, kuroda.hay...@fujitsu.com wrote: I’m not familiar with the system interrupt, however, the sigalrm_due_at is subsutitue between HOLD_INTERRUPTS() and RESUM_INTERRUPTS(), so I think it cannot be interrupted. The following comments

RE: Terminate the idle sessions

Kuroda, Hayato/黒田 隼人 Cc: David G. Johnston ; Kyotaro Horiguchi ; Thomas Munro ; bharath.rupireddyforpostg...@gmail.com; pgsql-hackers@lists.postgresql.org Subject: Re: Terminate the idle sessions On Nov 17, 2020, at 2:07 PM, mailto:kuroda.hay...@fujitsu.com wrote: Dear Li, David,  

Re: Terminate the idle sessions

On Nov 17, 2020, at 2:07 PM, kuroda.hay...@fujitsu.com wrote: Dear Li, David, > Additionally, using postgres_fdw within the server doesn't cause issues, > its using postgres_fdw and the remote server having this setting set to zero > that causes a problem. I

RE: Terminate the idle sessions

Dear Li, David, > Additionally, using postgres_fdw within the server doesn't cause issues, > its using postgres_fdw and the remote server having this setting set to zero > that causes a problem. I didn't know the fact that postgres_fdw can use within the server... Thanks. I read

Re: Terminate the idle sessions

On Nov 17, 2020, at 10:53 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Monday, November 16, 2020, Li Japin mailto:japi...@hotmail.com>> wrote: Consider setting this for specific users instead of as a server default. Client connections managed by connection poolers,

Re: Terminate the idle sessions

On Monday, November 16, 2020, Li Japin wrote: > > > Consider setting this for specific users instead of as a server default. > Client connections managed by connection poolers, or initiated indirectly > like those by a remote postgres_fdw using server, should probably be > excluded from this

Re: Terminate the idle sessions

-- Best regards Japin Li On Nov 17, 2020, at 7:59 AM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Nov 16, 2020 at 5:41 AM Li Japin mailto:japi...@hotmail.com>> wrote: Thanks for your review! Attached. Reading the doc changes: I'd rather not name postgres_fdw

Re: Terminate the idle sessions

On Mon, Nov 16, 2020 at 5:41 AM Li Japin wrote: > Thanks for your review! Attached. > Reading the doc changes: I'd rather not name postgres_fdw explicitly, or at least not solely, as a reason for setting this to zero. Additionally, using postgres_fdw within the server doesn't cause issues,

Re: Terminate the idle sessions

Hi Kuroda, On Nov 16, 2020, at 1:22 PM, kuroda.hay...@fujitsu.com wrote: @@ -30,6 +30,7 @@ typedef enum TimeoutId STANDBY_DEADLOCK_TIMEOUT, STANDBY_TIMEOUT, STANDBY_LOCK_TIMEOUT, + IDLE_SESSION_TIMEOUT, IDLE_IN_TRANSACTION_SESSION_TIMEOUT, /* First

RE: Terminate the idle sessions

Dear Li, > Thanks for your advice! Attached v4. I confirmed it. OK. > @@ -30,6 +30,7 @@ typedef enum TimeoutId > STANDBY_DEADLOCK_TIMEOUT, > STANDBY_TIMEOUT, > STANDBY_LOCK_TIMEOUT, > + IDLE_SESSION_TIMEOUT, > IDLE_IN_TRANSACTION_SESSION_TIMEOUT, > /* First

Re: Terminate the idle sessions

On Nov 13, 2020, at 6:27 PM, kuroda.hay...@fujitsu.com wrote: I read your patch, and I think the documentation is too simple to avoid all problems. (I think if some connection pooling is used, the same problem will occur.) Could you add some explanations in

RE: Terminate the idle sessions

Dear Li, I read your patch, and I think the documentation is too simple to avoid all problems. (I think if some connection pooling is used, the same problem will occur.) Could you add some explanations in the doc file? I made an example: ``` Note that this values should be set to zero if you

Re: Terminate the idle sessions

> On Aug 31, 2020, at 11:43 AM, Thomas Munro wrote: > > On Mon, Aug 31, 2020 at 2:40 PM Li Japin wrote: >> Could you give the more details about the test instructions? > > Hi Japin, > > Sure. Because I wasn't trying to get reliable TPS number or anything, > I just used a simple short

Re: Terminate the idle sessions

At Mon, 31 Aug 2020 12:51:20 +1200, Thomas Munro wrote in > On Tue, Aug 18, 2020 at 2:13 PM Li Japin wrote: > > On Aug 18, 2020, at 9:19 AM, Kyotaro Horiguchi > > wrote: > > The same already happens for idle_in_transaction_session_timeout and > > we can use "ALTER ROLE/DATABASE SET" to

Re: Terminate the idle sessions

On Mon, Aug 31, 2020 at 2:40 PM Li Japin wrote: > Could you give the more details about the test instructions? Hi Japin, Sure. Because I wasn't trying to get reliable TPS number or anything, I just used a simple short read-only test with one connection, like this: pgbench -i -s10 postgres

Re: Terminate the idle sessions

On Aug 31, 2020, at 8:51 AM, Thomas Munro mailto:thomas.mu...@gmail.com>> wrote: The main problem I have with it is the high frequency setitimer() calls. If you enable both statement_timeout and idle_session_timeout, then we get up to huge number of system calls, like the following strace -c

Re: Terminate the idle sessions

On Tue, Aug 18, 2020 at 2:13 PM Li Japin wrote: > On Aug 18, 2020, at 9:19 AM, Kyotaro Horiguchi > wrote: > The same already happens for idle_in_transaction_session_timeout and > we can use "ALTER ROLE/DATABASE SET" to dislable or loosen them, it's > a bit cumbersome, though. I don't think we

Re: Terminate the idle sessions

On Aug 18, 2020, at 9:19 AM, Kyotaro Horiguchi mailto:horikyota@gmail.com>> wrote: The same already happens for idle_in_transaction_session_timeout and we can use "ALTER ROLE/DATABASE SET" to dislable or loosen them, it's a bit cumbersome, though. I don't think we should (at least

Re: Terminate the idle sessions

Hello. At Mon, 17 Aug 2020 19:28:10 +0530, Bharath Rupireddy wrote in > On Fri, Aug 14, 2020 at 1:32 PM Li Japin wrote: > > > > On Aug 14, 2020, at 2:15 PM, Bharath Rupireddy < > bharath.rupireddyforpostg...@gmail.com> wrote: > > > > I think, since the idle_session_timeout is by default

Re: Terminate the idle sessions

On Fri, Aug 14, 2020 at 1:32 PM Li Japin wrote: > > On Aug 14, 2020, at 2:15 PM, Bharath Rupireddy < bharath.rupireddyforpostg...@gmail.com> wrote: > > I think, since the idle_session_timeout is by default disabled, we > have no problem. My thought is what if a user enables the >

Re: Terminate the idle sessions

On Aug 14, 2020, at 2:15 PM, Bharath Rupireddy mailto:bharath.rupireddyforpostg...@gmail.com>> wrote: I think, since the idle_session_timeout is by default disabled, we have no problem. My thought is what if a user enables the feature(knowingly or unknowingly) on the remote backend? If the

Re: Terminate the idle sessions

On Tue, Aug 11, 2020 at 8:45 AM Li Japin wrote: > > I’ve attached a new version that add “idle_session_timeout” in the default > postgresql.conf. > Hi, I would like to just mention a use case I thought of while discussing [1]: In postgres_fdw: assuming we use idle_in_session_timeout on remote

Re: Terminate the idle sessions

Hi, On Aug 11, 2020, at 5:42 AM, Cary Huang mailto:cary.hu...@highgo.ca>> wrote: I applied this patch to the PG13 branch and generally this feature works as described. The new "idle_session_timeout" that controls the idle session disconnection is not in the default postgresql.conf and I think

Re: Terminate the idle sessions

On Mon, Aug 10, 2020 at 2:43 PM Cary Huang wrote: > There is currently no enforced minimum value for "idle_session_timeout" > (except for value 0 for disabling the feature), so user can put any value > larger than 0 and it could be very small like 500 or even 50 millisecond, > this would make

Re: Terminate the idle sessions

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: tested, passed Documentation:tested, passed I applied this patch to the PG13 branch and generally this

Re: Terminate the idle sessions

On Jun 10, 2020, at 10:27 PM, Adam Brusselback mailto:adambrusselb...@gmail.com>> wrote: My use case is, I have a primary application that connects to the DB, most users work through that (setting is useless for this scenario, app manages it's connections well enough). I also have a number

Re: Terminate the idle sessions

> > > Why not implement it in the core of Postgres? Are there any disadvantages of > implementing it in the core of Postgres? I was surprised this wasn't a feature when I looked into it a couple years ago. I'd use it if it were built in, but I am not installing something extra just for this. >

Re: Terminate the idle sessions

On Jun 10, 2020, at 4:25 PM, Michael Paquier mailto:mich...@paquier.xyz>> wrote: Idle sessions staying around can be a problem in the long run as they impact snapshot building. You could for example use a background worker to do this work, like that:

Re: Terminate the idle sessions

On Wed, Jun 10, 2020 at 05:20:36AM +, Li Japin wrote: > I agree with you. But we can also give the user to control the idle > sessions lifetime. Idle sessions staying around can be a problem in the long run as they impact snapshot building. You could for example use a background worker to

Re: Terminate the idle sessions

On Jun 9, 2020, at 10:35 PM, David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: I’m curious as to the use case because I cannot imagine using this. Idle connections are normal. Seems better to monitor them and conditionally execute the disconnect backend function from the

Re: Terminate the idle sessions

On Tuesday, June 9, 2020, Li Japin wrote: > Hi, hackers > > When some clients connect to database in idle state, postgres do not close > the idle sessions, > here i add a new GUC idle_session_timeout to let postgres close the idle > sessions, it samilar > to idle_in_transaction_session_timeout >