2015-11-04 17:02 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: > On Wed, Nov 4, 2015 at 8:54 AM, Pavel Stehule <pavel.steh...@gmail.com> > wrote: > > 2015-11-04 15:50 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: > >> > >> On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule <pavel.steh...@gmail.com> > >> wrote: > >> >> > Okay, I think one more point to consider is that it would be > >> >> > preferable > >> >> > to > >> >> > have such an option for backend sessions and not for other > processes > >> >> > like WalSender. > >> >> > >> >> All right...I see the usage.. I withdraw my objection to 'session' > >> >> prefix then now that I understand the case. So, do you agree that: > >> >> > >> >> *) session_idle_timeout: dumps the backend after X time in 'idle' > state > >> >> and > >> >> *) transaction_timeout: cancels transaction after X time, regardless > >> >> of > >> >> state > >> >> > >> >> sounds good? > >> > > >> > > >> > Not too much > >> > > >> > *) transaction_timeout: cancels transaction after X time, regardless > of > >> > state > >> > > >> > This is next level of statement_timeout. I can't to image sense. What > is > >> > a > >> > issue solved by this property? > >> > >> That's the entire point of the thread (or so I thought): cancel > >> transactions 'idle in transaction'. This is entirely different than > >> killing idle sessions. BTW, I would never configure > >> session_idle_timeout, because I have no idea what that would do to > >> benign cases where connection poolers have grabbed a few extra > >> connections during a load spike. It's pretty common not to have > >> those applications have coded connection retry properly and it would > >> cause issues. > > > > you wrote "transaction_timeout: cancels transaction after X time, > regardless > > Yes, and that is what I meant. I have two problems with > transaction_idle_timeout (as opposed to transaction_timeout): > > A) It's more complex. Unsophisticated administrators may not > understand or set it properly > > B) There is no way to enforce an upper bound on transaction time with > that setting. A pathological application could keep a transaction > open forever without running into any timeouts -- that's a dealbreaker > for me. > > From my point of view the purpose of the setting should be to protect > you from any single actor from doing things that damage the database. > 'idle in transaction' happens to be one obvious way, but upper bound > on transaction time protects you in general way. >
I agree so transaction_timeout is more general. But I have same problem @A. How it set properly. In our production max transaction can 30hours - (VACUUM) or 5hours (ETL). But transaction_idle_timeout can be 5minutes, I know so 5 minutes in "idle in transaction" state signalizes some issue. It looks very similar to relation between statement_timeout and lock_timeout I am think. Regards Pavel > merlin >
