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

On 11/3/15 8:44 AM, Merlin Moncure wrote: Actually, one other thing that would help is to have the ability to turn >this into an ERROR: > >begin; >WARNING: there is already a transaction in progress curious: does the SQL standard define this behavior? Anyways, we've pretty studiously avoided

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

On 11/4/15 4:55 PM, Stephen Frost wrote: > * Joe Conway (m...@joeconway.com) wrote: >> On 11/04/2015 01:24 PM, Alvaro Herrera wrote: >>> I agree with Pavel. Having a transaction timeout just does not make any >>> sense. I can see absolutely no use for it. An idle-in-transaction >>> timeout, on

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

On 11/05/2015 10:09 AM, Pavel Stehule wrote: > On 5.11.2015 19:02 Merlin Moncure wrote: >> Thus, I think we have consensus that transaction_timeout is good -- it >> would deprecate statement_timeout essentially. Likewise, >> pg_cancel_transaction is good and would deprecate pg_cancel_backend; >>

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

On 11/05/2015 10:48 AM, Pavel Stehule wrote: > S > tC > a<>E > rA B A B A n > t d > |====---| > > Currently we can set timeout and cancel

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

2015-11-05 19:56 GMT+01:00 Joe Conway : > On 11/05/2015 10:48 AM, Pavel Stehule wrote: > > S > > tC > > a<>E > > rA B A B A n > > t d > >

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

Dne 5.11.2015 19:02 napsal uživatel "Merlin Moncure" : > > On Wed, Nov 4, 2015 at 4:15 PM, Stephen Frost wrote: > > * Joshua D. Drake (j...@commandprompt.com) wrote: > >> On 11/04/2015 01:55 PM, Stephen Frost wrote: > >> >* Joe Conway (m...@joeconway.com)

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

On Wed, Nov 4, 2015 at 4:15 PM, Stephen Frost wrote: > * Joshua D. Drake (j...@commandprompt.com) wrote: >> On 11/04/2015 01:55 PM, Stephen Frost wrote: >> >* Joe Conway (m...@joeconway.com) wrote: >> >>On 11/04/2015 01:24 PM, Alvaro Herrera wrote: >> >>>I agree with Pavel.

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

2015-11-05 19:31 GMT+01:00 Joe Conway : > On 11/05/2015 10:09 AM, Pavel Stehule wrote: > > On 5.11.2015 19:02 Merlin Moncure wrote: > >> Thus, I think we have consensus that transaction_timeout is good -- it > >> would deprecate statement_timeout essentially. Likewise, > >>

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

On Thu, Nov 5, 2015 at 12:09 PM, Pavel Stehule wrote: > > Dne 5.11.2015 19:02 napsal uživatel "Merlin Moncure" : >> >> On Wed, Nov 4, 2015 at 4:15 PM, Stephen Frost wrote: >> > * Joshua D. Drake (j...@commandprompt.com) wrote: >>

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

On Thu, Nov 5, 2015 at 12:31 PM, Joe Conway wrote: > On 11/05/2015 10:09 AM, Pavel Stehule wrote: >> On 5.11.2015 19:02 Merlin Moncure wrote: >>> Thus, I think we have consensus that transaction_timeout is good -- it >>> would deprecate statement_timeout essentially.

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

On 11/5/15 10:10 AM, Alvaro Herrera wrote: David Steele wrote: The important thing about this implementation was that nothing was terminated unless it had exceed a timeout AND was blocking another process. This seems a nice idea, but you need to take the effect on vacuum of idle-in-xact

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

On 11/05/2015 09:01 PM, Merlin Moncure wrote: > Tom noted earlier some caveats with the 'idle' timeout in terms of > implementation. Maybe that needs to be zeroed in on. AFAIK, those issues have already been solved by Andres some time ago. -- Vik Fearing

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

On Wed, Nov 4, 2015 at 5:10 PM, Josh Berkus wrote: > On 11/04/2015 01:55 PM, Stephen Frost wrote: >> * Joe Conway (m...@joeconway.com) wrote: >>> On 11/04/2015 01:24 PM, Alvaro Herrera wrote: I agree with Pavel. Having a transaction timeout just does not make any

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

On 5 November 2015 at 23:10, Alvaro Herrera wrote: > David Steele wrote: > >> The important thing about this implementation was that nothing was >> terminated unless it had exceed a timeout AND was blocking another >> process. > > This seems a nice idea, but you need to

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

David Steele wrote: > The important thing about this implementation was that nothing was > terminated unless it had exceed a timeout AND was blocking another > process. This seems a nice idea, but you need to take the effect on vacuum of idle-in-xact sessions too. If the operator left for the

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

2015-11-04 21:56 GMT+01:00 Joshua D. Drake : > On 11/04/2015 12:31 PM, Merlin Moncure wrote: > > My issue isn't long statements, but broken client, that is broken in wrong >>> state - connect is still active, but no any statement will coming. >>> >> >> Right, 'Idle in

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

2015-11-04 21:31 GMT+01:00 Merlin Moncure : > On Wed, Nov 4, 2015 at 2:09 PM, Pavel Stehule > wrote: > > 2015-11-04 20:35 GMT+01:00 Merlin Moncure : > >> > >> On Wed, Nov 4, 2015 at 11:26 AM, Pavel Stehule

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

On 11/04/2015 01:24 PM, Alvaro Herrera wrote: > I agree with Pavel. Having a transaction timeout just does not make any > sense. I can see absolutely no use for it. An idle-in-transaction > timeout, on the other hand, is very useful. +1 -- agreed Joe -- Crunchy Data - http://crunchydata.com

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

* Joe Conway (m...@joeconway.com) wrote: > On 11/04/2015 01:24 PM, Alvaro Herrera wrote: > > I agree with Pavel. Having a transaction timeout just does not make any > > sense. I can see absolutely no use for it. An idle-in-transaction > > timeout, on the other hand, is very useful. > > +1 --

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

On 11/04/2015 01:55 PM, Stephen Frost wrote: * Joe Conway (m...@joeconway.com) wrote: On 11/04/2015 01:24 PM, Alvaro Herrera wrote: I agree with Pavel. Having a transaction timeout just does not make any sense. I can see absolutely no use for it. An idle-in-transaction timeout, on the other

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

* Joshua D. Drake (j...@commandprompt.com) wrote: > On 11/04/2015 01:55 PM, Stephen Frost wrote: > >* Joe Conway (m...@joeconway.com) wrote: > >>On 11/04/2015 01:24 PM, Alvaro Herrera wrote: > >>>I agree with Pavel. Having a transaction timeout just does not make any > >>>sense. I can see

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

Pavel Stehule wrote: > 2015-11-04 22:14 GMT+01:00 Joshua D. Drake : > > > On 11/04/2015 01:11 PM, Pavel Stehule wrote: > > > >> I am sorry, but I have a different experience from GoodData. The few > >> hours autovacuum is usual. So probably, there should be exception for >

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

On 11/04/2015 02:07 PM, Joshua D. Drake wrote: > On 11/04/2015 01:55 PM, Stephen Frost wrote: >> * Joe Conway (m...@joeconway.com) wrote: >>> On 11/04/2015 01:24 PM, Alvaro Herrera wrote: I agree with Pavel. Having a transaction timeout just does not make any sense. I can see

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

On 11/04/2015 02:15 PM, Stephen Frost wrote: Yeah but anything holding a lock that long can be terminated via statement_timeout can it not? Well, no? statement_timeout is per-statement, while transaction_timeout is, well, per transaction. If there's a process which is going and has an open

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

2015-11-04 22:14 GMT+01:00 Joshua D. Drake : > On 11/04/2015 01:11 PM, Pavel Stehule wrote: > > >> I am sorry, but I have a different experience from GoodData. The few >> hours autovacuum is usual. So probably, there should be exception for >> autovacuum, dump, .. >> > >

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

On Wed, Nov 4, 2015 at 2:09 PM, Pavel Stehule wrote: > 2015-11-04 20:35 GMT+01:00 Merlin Moncure : >> >> On Wed, Nov 4, 2015 at 11:26 AM, Pavel Stehule >> > it doesn't help. How I can set transaction_timeout if I have series

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

On 11/04/2015 12:31 PM, Merlin Moncure wrote: My issue isn't long statements, but broken client, that is broken in wrong state - connect is still active, but no any statement will coming. Right, 'Idle in transaction'. Agree that a setting directed purely at that problem could set a much

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

On 11/04/2015 01:55 PM, Stephen Frost wrote: > * Joe Conway (m...@joeconway.com) wrote: >> On 11/04/2015 01:24 PM, Alvaro Herrera wrote: >>> I agree with Pavel. Having a transaction timeout just does not make any >>> sense. I can see absolutely no use for it. An idle-in-transaction >>> timeout,

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

JD, On Wednesday, November 4, 2015, Joshua D. Drake wrote: > On 11/04/2015 02:15 PM, Stephen Frost wrote: > > Yeah but anything holding a lock that long can be terminated via >>> statement_timeout can it not? >>> >> >> Well, no? statement_timeout is per-statement, while

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

On 10/30/2015 10:20 PM, Merlin Moncure 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

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

On 11/04/2015 01:11 PM, Pavel Stehule wrote: I am sorry, but I have a different experience from GoodData. The few hours autovacuum is usual. So probably, there should be exception for autovacuum, dump, .. But autovacuum and dump are not idle in transaction or am I missing something? JD

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

2015-11-04 23:53 GMT+01:00 Stephen Frost : > JD, > > On Wednesday, November 4, 2015, Joshua D. Drake > wrote: > >> On 11/04/2015 02:15 PM, Stephen Frost wrote: >> >> Yeah but anything holding a lock that long can be terminated via

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

On 11/04/2015 02:53 PM, Stephen Frost wrote: This implies that a statement used takes a long time. It may not. The lock is held at the transaction level not the statement level, which is why a transaction level timeout is actually more useful than a statement level timeout. What I'm most

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

2015-11-04 20:35 GMT+01:00 Merlin Moncure : > On Wed, Nov 4, 2015 at 11:26 AM, Pavel Stehule > wrote: > > 2015-11-04 18:18 GMT+01:00 Merlin Moncure : > >> > >> On Wed, Nov 4, 2015 at 11:15 AM, Pavel Stehule

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

On Wed, Nov 4, 2015 at 11:26 AM, Pavel Stehule wrote: > 2015-11-04 18:18 GMT+01:00 Merlin Moncure : >> >> On Wed, Nov 4, 2015 at 11:15 AM, Pavel Stehule >> wrote: >> > >> > >> > 2015-11-04 18:11 GMT+01:00 Tom Lane

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

On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule 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

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

On Tue, Nov 3, 2015 at 10:33 PM, Amit Kapila wrote: > On Tue, Nov 3, 2015 at 7:56 PM, Pavel Stehule > wrote: >> >> >> >> 2015-11-03 3:42 GMT+01:00 Amit Kapila : >>> >>> On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule

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

> 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

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

On Wed, Nov 4, 2015 at 8:06 PM, Merlin Moncure wrote: > > On Tue, Nov 3, 2015 at 10:33 PM, Amit Kapila 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

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

2015-11-04 15:50 GMT+01:00 Merlin Moncure : > On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule > 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

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

2015-11-04 17:02 GMT+01:00 Merlin Moncure : > On Wed, Nov 4, 2015 at 8:54 AM, Pavel Stehule > wrote: > > 2015-11-04 15:50 GMT+01:00 Merlin Moncure : > >> > >> On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule

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

On Wed, Nov 4, 2015 at 10:02 AM, Merlin Moncure wrote: > On Wed, Nov 4, 2015 at 8:54 AM, Pavel Stehule wrote: >> 2015-11-04 15:50 GMT+01:00 Merlin Moncure : >>> >>> On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule

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

On Wed, Nov 4, 2015 at 8:54 AM, Pavel Stehule wrote: > 2015-11-04 15:50 GMT+01:00 Merlin Moncure : >> >> On Wed, Nov 4, 2015 at 8:42 AM, Pavel Stehule >> wrote: >> >> > Okay, I think one more point to consider is that it would

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

2015-11-04 18:18 GMT+01:00 Merlin Moncure : > On Wed, Nov 4, 2015 at 11:15 AM, Pavel Stehule > wrote: > > > > > > 2015-11-04 18:11 GMT+01:00 Tom Lane : > >> > >> Merlin Moncure writes: > >> >> Yes, and that is

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

2015-11-04 18:11 GMT+01:00 Tom Lane : > Merlin Moncure writes: > >> 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

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

On Wed, Nov 4, 2015 at 11:15 AM, Pavel Stehule wrote: > > > 2015-11-04 18:11 GMT+01:00 Tom Lane : >> >> Merlin Moncure writes: >> >> Yes, and that is what I meant. I have two problems with >> >> transaction_idle_timeout (as

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

Merlin Moncure writes: >> 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

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

On Tue, Nov 3, 2015 at 7:56 PM, Pavel Stehule wrote: > > > 2015-11-03 3:42 GMT+01:00 Amit Kapila : > >> On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule >> wrote: >>> >>> >>> It is 100% true. But the users can do strange

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

On Tue, Nov 3, 2015 at 7:53 PM, Merlin Moncure wrote: > > On Mon, Nov 2, 2015 at 8:42 PM, Amit Kapila wrote: > > > > What exactly do we want to define session_idle_timeout? Some > > possibilities: > > a. Reset the session related variables like

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

On Mon, Nov 2, 2015 at 1:23 PM, Jim Nasby wrote: > On 11/2/15 11:15 AM, Pavel Stehule wrote: >> >> I have not strong idea about how to solve it well - maybe introduce >> transaction_idle_timeout and session_idle_timeout? > > > Yes, please. This is a very common problem.

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

On Mon, Nov 2, 2015 at 8:42 PM, Amit Kapila wrote: > On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule > 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

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

2015-11-03 3:42 GMT+01:00 Amit Kapila : > On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule > 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

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

>> > >> That sounds to be a solution for this problem or otherwise for such a > case > >> can't we completely abort the active transaction and set a flag like > >> PrevCommandFailed/PrevTransFailed and on receiving next message if > >> such a flag is set, then throw an appropriate error. > > > >

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

On 11/2/15 11:15 AM, Pavel Stehule wrote: I have not strong idea about how to solve it well - maybe introduce transaction_idle_timeout and session_idle_timeout? Yes, please. This is a very common problem. I would love a better way to detect (or prevent) clients from being brain-dead about how

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

On Mon, Nov 2, 2015 at 1:28 AM, Pavel Stehule wrote: > > > 2015-11-02 5:23 GMT+01:00 Amit Kapila : >> >> On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane wrote: >> > >> > Magnus Hagander writes: >> > > On Sat,

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

On Mon, Nov 2, 2015 at 10:45 PM, Pavel Stehule 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

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

On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila wrote: > On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure > wrote: > > > > Idle hanging transactions from poorly written applications are the > > bane of my existence. Several months back one of them took

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

On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane wrote: > > Magnus Hagander writes: > > On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila > > wrote: > >> Why pg_cancel_backend(pid) is not sufficient for the above use case? > >> Basically you

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

Magnus Hagander writes: > On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila > wrote: >> 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

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

2015-11-02 5:23 GMT+01:00 Amit Kapila : > On Sun, Nov 1, 2015 at 11:34 PM, Tom Lane wrote: > > > > Magnus Hagander writes: > > > On Sat, Oct 31, 2015 at 5:50 AM, Amit Kapila > > > wrote: > > >> Why

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

On Sat, Oct 31, 2015 at 2:50 AM, Merlin Moncure 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

[HACKERS] Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

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