On Wed, Nov 4, 2015 at 8:54 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2015-11-04 15:50 GMT+01:00 Merlin Moncure <mmoncure@gmail.com>: >> >> On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule <pavel.stehule@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.