Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

On Tue, Nov 3, 2015 at 10:33 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Tue, Nov 3, 2015 at 7:56 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>>
>>
>> 2015-11-03 3:42 GMT+01:00 Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>:
>>>
>>> On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>> wrote:
>>>>
>>>>
>>>> It is 100% true. But the users can do strange things. If we solve idle
>>>> transactions and not idle session, then they are able to increase
>>>> max_connections to thousands with happy smile in face.
>>>>
>>>> I have not strong idea about how to solve it well - maybe introduce
>>>> transaction_idle_timeout and session_idle_timeout?
>>>>
>>>
>>> What exactly do we want to define session_idle_timeout? Some
>>> possibilities:
>>> a. Reset the session related variables like transaction, prepared
>>> statements, etc. and retain it for connection pool kind of stuff
>>> b. Exit from the session
>>
>>
>> b is safe state - and currently it is only one state, that we can forward
>> to client side (with keep_alive packets) - so I prefer b
>>
>
> 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?

merlin