Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions
| От | Amit Kapila |
|---|---|
| Тема | Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions |
| Дата | |
| Msg-id | CAA4eK1LOwd2Mn0rJSjgJFALyxSftaQzQcB4_tBQ4cvdkBTzVmA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Request: pg_cancel_backend variant that handles 'idle in transaction' sessions (Merlin Moncure <mmoncure@gmail.com>) |
| Ответы |
Re: Request: pg_cancel_backend variant that handles 'idle
in transaction' sessions
|
| Список | pgsql-hackers |
On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
> Idle hanging transactions from poorly written applications are the
> bane of my existence. Several months back one of them took down one
> of hour production websites for several hours.
>
> Unfortunately, the only way to deal with them is to terminate the
> backend which is heavy handed and in some cases causes further damage.
> Something like pg_cancel_transaction(pid) would be nice; it would
> end the transaction regardless if in an actual statement or not.
> Similarly, transaction_timeout would be a lot more effective than
> statement_timeout.
>
> Idle hanging transactions from poorly written applications are the
> bane of my existence. Several months back one of them took down one
> of hour production websites for several hours.
>
> Unfortunately, the only way to deal with them is to terminate the
> backend which is heavy handed and in some cases causes further damage.
> Something like pg_cancel_transaction(pid) would be nice; it would
> end the transaction regardless if in an actual statement or not.
>
Why pg_cancel_backend(pid) is not sufficient for the above use case?
Basically you want to rollback current transaction, I think that can be
achieved by pg_cancel_backend.
> Similarly, transaction_timeout would be a lot more effective than
> statement_timeout.
>
I think here by transaction_timeout you mean to say cancel all
transactions that are idle for transaction_timeout time. So it is better
to call it as transaction_idle_timeout. Having said that I am not sure
if holding such a connection is meaningful either because I think there
is high probablity that user of such a session might not perform any further
action for a long time, so why not have idle_timeout to indicate the termination
of session if it is idle for idle_timeout time.
В списке pgsql-hackers по дате отправления: