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 (minus a couple of anachronisms) .conf setting thats control behavior of SQL commands in a non performance way. If we had an event trigger on BEGIN and a way to tell whether we were already in a transaction this wouldn't need to be a config setting. IMO, this as yet another case for 'stored procedures' that can manage transaction state: you could rig up your own procedure: CALL begin_tx_safe(); which would test transaction state and fail if already in one. This doesn't help you if you're not in direct control of application generated SQL but it's a start. Barring that, at least Even then it would be very easy to mess this up. warnings tend to stand out in the database log. That depends greatly on how much other stuff is in the log. Something else I wish we had was the ability to send different log output to different places. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 take the effect on vacuum of > idle-in-xact sessions too. If the operator left for the day and their > session doesn't block any other process, the next day you could find > some tables bloated to such extreme as to cause problems later on. The additional qualifier "and isn't pinning xmin" would probably be useful there. Often it's pretty harmless to keep an xact open for ages, the problem has, until the recent changes in pg_stat_activity, been knowing when. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 sense. I can see absolutely no use for it. An idle-in-transaction timeout, on the other hand, is very useful. >>> >>> +1 -- agreed >> >> I'm not sure of that. I can certainly see a use for transaction >> timeouts- after all, they hold locks and can be very disruptive in the >> long run. Further, there are cases where a transaction is normally very >> fast and in a corner case it becomes extremely slow and disruptive to >> the rest of the system. In those cases, having a timeout for it is >> valuable. > > I could see a use for both, having written scripts which do both. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 sessions too. If the operator left for the day and their session doesn't block any other process, the next day you could find some tables bloated to such extreme as to cause problems later on. Surely the operator can review their terminal to re-do the work, in case it was valuable. (If it was valuable, why didn't they commit the transaction?) These particular databases were not subject to bloat since they were partitioned and append-only - no inserts or deletes whatsoever except to tiny dimension tables. In general, though, you are correct. An absolute transaction timeout would be a good first step but a blocking timeout would also be very handy. It would be very applicable to data warehouse scenarios where bloat is controlled by other means and long transactions are the norm (and idle-in-transactions times can also be long). -- -David da...@pgmasters.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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. Likewise, >>> pg_cancel_transaction is good and would deprecate pg_cancel_backend; >>> it's hard for me to imagine a scenario where a user would call >>> pg_cancel_backend if pg_cancel_transaction were to be available. >> >> I am sorry, I see a consensus between you and Stephen only. > > S > tC > a<>E > rA B A B A n > t d > |====---| > > Currently we can set timeout and cancel for period B (). I can see > based on this discussion that there are legitimate use cases for wanting > timeout and cancel for any of the periods A, B, or C. > > I guess the question then becomes how we provide that coverage. I think > for coverage of timeout you need three individual timeout settings. > However for cancel, it would seem that pg_cancel_transaction would cover > all three cases. Agreed on all points. Tom noted earlier some caveats with the 'idle' timeout in terms of implementation. Maybe that needs to be zeroed in on. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 > > |====---| > > > > Currently we can set timeout and cancel for period B (). I can > see > > based on this discussion that there are legitimate use cases for > wanting > > timeout and cancel for any of the periods A, B, or C. > > > > I guess the question then becomes how we provide that coverage. I > think > > for coverage of timeout you need three individual timeout settings. > > However for cancel, it would seem that pg_cancel_transaction would > cover > > all three cases. > > > > > > It can be difficult to set it properly, because you don't know how much > > statements (cycles of A.B) will be in transaction. Respective for > > setting C, I have to know the number of A,B and it isn't possible > everytime. > > But you might have a limit you want to enforce regardless of the size or > quantity of A & B periods. That's why it needs to be a separate timeout > IMHO. Let's say I never want a transaction to be around more than 60 > minutes no matter what. But I also don't want idle in transaction to > ever exceed 30 seconds, and I don't expect individual statements to > exceed 10 minutes. > I am not sure due my wrong English if we are in agreement or not, I am sorry :/ - Any mentioned timeouts are useful and covers little bit different issues - and we need all. Regards Pavel > > Joe > > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > >
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 for period B (). I can see > based on this discussion that there are legitimate use cases for wanting > timeout and cancel for any of the periods A, B, or C. > > I guess the question then becomes how we provide that coverage. I think > for coverage of timeout you need three individual timeout settings. > However for cancel, it would seem that pg_cancel_transaction would cover > all three cases. > > > It can be difficult to set it properly, because you don't know how much > statements (cycles of A.B) will be in transaction. Respective for > setting C, I have to know the number of A,B and it isn't possible everytime. But you might have a limit you want to enforce regardless of the size or quantity of A & B periods. That's why it needs to be a separate timeout IMHO. Let's say I never want a transaction to be around more than 60 minutes no matter what. But I also don't want idle in transaction to ever exceed 30 seconds, and I don't expect individual statements to exceed 10 minutes. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
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, > >> pg_cancel_transaction is good and would deprecate pg_cancel_backend; > >> it's hard for me to imagine a scenario where a user would call > >> pg_cancel_backend if pg_cancel_transaction were to be available. > > > > I am sorry, I see a consensus between you and Stephen only. > > S > tC > a<>E > rA B A B A n > t d > |====---| > > Currently we can set timeout and cancel for period B (). I can see > based on this discussion that there are legitimate use cases for wanting > timeout and cancel for any of the periods A, B, or C. > > I guess the question then becomes how we provide that coverage. I think > for coverage of timeout you need three individual timeout settings. > However for cancel, it would seem that pg_cancel_transaction would cover > all three cases. > It can be difficult to set it properly, because you don't know how much statements (cycles of A.B) will be in transaction. Respective for setting C, I have to know the number of A,B and it isn't possible everytime. Regards Pavel > > Joe > > -- > Crunchy Data - http://crunchydata.com > PostgreSQL Support for Secure Enterprises > Consulting, Training, & Open Source Development > >
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; >> it's hard for me to imagine a scenario where a user would call >> pg_cancel_backend if pg_cancel_transaction were to be available. > > I am sorry, I see a consensus between you and Stephen only. S tC a<>E rA B A B A n t d |====---| Currently we can set timeout and cancel for period B (). I can see based on this discussion that there are legitimate use cases for wanting timeout and cancel for any of the periods A, B, or C. I guess the question then becomes how we provide that coverage. I think for coverage of timeout you need three individual timeout settings. However for cancel, it would seem that pg_cancel_transaction would cover all three cases. Joe -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
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: >> >> 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 hand, is very useful. >> >> >> >> >> >>+1 -- agreed >> >> > >> >> >I'm not sure of that. I can certainly see a use for transaction >> >> >timeouts- after all, they hold locks and can be very disruptive in the >> >> >long run. Further, there are cases where a transaction is normally >> >> > very >> >> >fast and in a corner case it becomes extremely slow and disruptive to >> >> >the rest of the system. In those cases, having a timeout for it is >> >> >valuable. >> >> >> >> 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 transaction and it's holding locks, that can be an issue. >> > >> > To be frank, my gut feeling is that transaction_timeout is actually more >> > useful than statement_timeout. >> >> Exactly. statement_timeout is weak because it resets for every >> statement regardless of transaction. Similarly, pg_cancel_backend is >> weak because it only works if a backend is actually in statement >> regardless of transaction state (reading this thread, it's clear that >> this is not widely known even among -hackers which further reinforces >> the point). >> >> 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; >> it's hard for me to imagine a scenario where a user would call >> pg_cancel_backend if pg_cancel_transaction were to be available. >> > > I am sorry, I see a consensus between you and Stephen only. :-). I guess then maybe not. Note, I'm not taking a position on other proposed settings.I'm not claiming they (noted above) solve every problem, just that they are good. IOW, the (narrow) claim pg_cancel_backend and statement_timeout are broken and need to be fixed. If you disagree, then you are arguing that it's bad to give administrator ability to cancel running transaction regardless of execution state. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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) 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 -- agreed > >> > > >> >I'm not sure of that. I can certainly see a use for transaction > >> >timeouts- after all, they hold locks and can be very disruptive in the > >> >long run. Further, there are cases where a transaction is normally very > >> >fast and in a corner case it becomes extremely slow and disruptive to > >> >the rest of the system. In those cases, having a timeout for it is > >> >valuable. > >> > >> 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 transaction and it's holding locks, that can be an issue. > > > > To be frank, my gut feeling is that transaction_timeout is actually more > > useful than statement_timeout. > > Exactly. statement_timeout is weak because it resets for every > statement regardless of transaction. Similarly, pg_cancel_backend is > weak because it only works if a backend is actually in statement > regardless of transaction state (reading this thread, it's clear that > this is not widely known even among -hackers which further reinforces > the point). > > 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; > it's hard for me to imagine a scenario where a user would call > pg_cancel_backend if pg_cancel_transaction were to be available. > I am sorry, I see a consensus between you and Stephen only. Regards Pavel > merlin
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. 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 >> > >> >I'm not sure of that. I can certainly see a use for transaction >> >timeouts- after all, they hold locks and can be very disruptive in the >> >long run. Further, there are cases where a transaction is normally very >> >fast and in a corner case it becomes extremely slow and disruptive to >> >the rest of the system. In those cases, having a timeout for it is >> >valuable. >> >> 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 transaction and it's holding locks, that can be an issue. > > To be frank, my gut feeling is that transaction_timeout is actually more > useful than statement_timeout. Exactly. statement_timeout is weak because it resets for every statement regardless of transaction. Similarly, pg_cancel_backend is weak because it only works if a backend is actually in statement regardless of transaction state (reading this thread, it's clear that this is not widely known even among -hackers which further reinforces the point). 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; it's hard for me to imagine a scenario where a user would call pg_cancel_backend if pg_cancel_transaction were to be available. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 day and their session doesn't block any other process, the next day you could find some tables bloated to such extreme as to cause problems later on. Surely the operator can review their terminal to re-do the work, in case it was valuable. (If it was valuable, why didn't they commit the transaction?) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 the other hand, is very useful. >> >> +1 -- agreed > > I'm not sure of that. I can certainly see a use for transaction > timeouts- after all, they hold locks and can be very disruptive in the > long run. Further, there are cases where a transaction is normally very > fast and in a corner case it becomes extremely slow and disruptive to > the rest of the system. In those cases, having a timeout for it is > valuable. > > David (adding him to the CC) actually developed a utility specifically > to identify what transactions are blocking what others and to kill off > other processes if they were running for too long and blocking higher > priority processes. It didn't matter, in that environment, if they were > idle-in-transaction or actively running. You are remembering correctly, Stephen, though there were different timeouts for blocking transactions that were running and those that were idle-in-transaction. We usually set the idle-in-transaction timeout much lower as it measured not total transaction time but idle time since the last state change. In that environment, at least, an idle-in-transaction session was always due to a stuck process, bug, or user session left open overnight. Because partitions and FKs were continuously being created even ACCESS SHARE locks could be a problem. The important thing about this implementation was that nothing was terminated unless it had exceed a timeout AND was blocking another process. A feature of this particular system was that it had very long running transactions that needed to execute unless there was a conflict. Even then, we'd get an alert some time in advance of the transaction being terminated so we could make the judgement call to terminate the other process(es) instead. -- -David da...@pgmasters.net signature.asc Description: OpenPGP digital signature
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 interested in, in the use case which I described and which David built a system for, is getting that lock released from the lower priority process to let the higher priority process run. I couldn't care less about statement level anything. Ahh, o.k. Yes, I could see the benefit to that. JD Thanks! Stephen -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. New rule for social situations: "If you think to yourself not even JD would say this..." Stop and shut your mouth. It's going to be bad. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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 transaction and it's holding locks, that can be an issue. >>> >> >> No, what I mean is this: >> >> BEGIN; >> select * from foo; >> update bar; >> delete baz; >> >> Each one of those is subject to statement_timeout, yes? If so, then I >> don't see a point for transaction timeout. You set statement_timeout for >> what works for your environment. Once the timeout is reached within the >> statement (within the transaction), the transaction is going to rollback >> too. >> > > 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. > It hard to compare these proposals because any proposal solves slightly different issue and has different advantages and disadvantages. The flat solution probably will by too limited. I see a possible advantages of transaction_timeout (max lock duration), transaction_idle_timeout, statement_timeout. Any of these limits has sense, and can helps with resource management. There is not full substitution. Regards Pavel > > What I'm most interested in, in the use case which I described and which > David built a system for, is getting that lock released from the lower > priority process to let the higher priority process run. I couldn't care > less about statement level anything. > > Thanks! > > Stephen >
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 transaction_timeout >> is, well, per transaction. If there's a process which is going and has >> an open transaction and it's holding locks, that can be an issue. >> > > No, what I mean is this: > > BEGIN; > select * from foo; > update bar; > delete baz; > > Each one of those is subject to statement_timeout, yes? If so, then I > don't see a point for transaction timeout. You set statement_timeout for > what works for your environment. Once the timeout is reached within the > statement (within the transaction), the transaction is going to rollback > too. > 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 interested in, in the use case which I described and which David built a system for, is getting that lock released from the lower priority process to let the higher priority process run. I couldn't care less about statement level anything. Thanks! Stephen
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 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. It's nice to think about a world where > applications don't do such things, but in this endless sea of > enterprise java soup I live it it's, uh, not realistic. This would be > lot cleaner than the cron driven sweep I'm forced to implement now, > and could be made to be part of the standard configuration across the > enterprise. I would like to request that no one work on this. I wrote a patch to do just that a year and a half ago[1] which was rejected for technical reasons. Since then, Andres has fixed those reasons, and prodded me last week at PGConf.EU to pick my patch back up. I am planning on resubmitting it for the next commitfest. I will also take into account the things said on this thread. [1] http://www.postgresql.org/message-id/538dc843.2070...@dalibo.com -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 transaction and it's holding locks, that can be an issue. No, what I mean is this: BEGIN; select * from foo; update bar; delete baz; Each one of those is subject to statement_timeout, yes? If so, then I don't see a point for transaction timeout. You set statement_timeout for what works for your environment. Once the timeout is reached within the statement (within the transaction), the transaction is going to rollback too. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. New rule for social situations: "If you think to yourself not even JD would say this..." Stop and shut your mouth. It's going to be bad. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 absolutely no use for it. An idle-in-transaction > >>>timeout, on the other hand, is very useful. > >> > >>+1 -- agreed > > > >I'm not sure of that. I can certainly see a use for transaction > >timeouts- after all, they hold locks and can be very disruptive in the > >long run. Further, there are cases where a transaction is normally very > >fast and in a corner case it becomes extremely slow and disruptive to > >the rest of the system. In those cases, having a timeout for it is > >valuable. > > 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 transaction and it's holding locks, that can be an issue. To be frank, my gut feeling is that transaction_timeout is actually more useful than statement_timeout. Thanks! Stephen signature.asc Description: Digital signature
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 absolutely no use for it. An idle-in-transaction timeout, on the other hand, is very useful. >>> >>> +1 -- agreed >> >> I'm not sure of that. I can certainly see a use for transaction >> timeouts- after all, they hold locks and can be very disruptive in the >> long run. Further, there are cases where a transaction is normally very >> fast and in a corner case it becomes extremely slow and disruptive to >> the rest of the system. In those cases, having a timeout for it is >> valuable. > > Yeah but anything holding a lock that long can be terminated via > statement_timeout can it not? That is exactly what I was thinking -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
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 hand, is very useful. >> >> +1 -- agreed > > I'm not sure of that. I can certainly see a use for transaction > timeouts- after all, they hold locks and can be very disruptive in the > long run. Further, there are cases where a transaction is normally very > fast and in a corner case it becomes extremely slow and disruptive to > the rest of the system. In those cases, having a timeout for it is > valuable. I could see a use for both, having written scripts which do both. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 hand, is very useful. +1 -- agreed I'm not sure of that. I can certainly see a use for transaction timeouts- after all, they hold locks and can be very disruptive in the long run. Further, there are cases where a transaction is normally very fast and in a corner case it becomes extremely slow and disruptive to the rest of the system. In those cases, having a timeout for it is valuable. Yeah but anything holding a lock that long can be terminated via statement_timeout can it not? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. New rule for social situations: "If you think to yourself not even JD would say this..." Stop and shut your mouth. It's going to be bad. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 -- agreed I'm not sure of that. I can certainly see a use for transaction timeouts- after all, they hold locks and can be very disruptive in the long run. Further, there are cases where a transaction is normally very fast and in a corner case it becomes extremely slow and disruptive to the rest of the system. In those cases, having a timeout for it is valuable. David (adding him to the CC) actually developed a utility specifically to identify what transactions are blocking what others and to kill off other processes if they were running for too long and blocking higher priority processes. It didn't matter, in that environment, if they were idle-in-transaction or actively running. David, please correct/confirm my recollection above. Thanks! Stephen signature.asc Description: Digital signature
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 PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development signature.asc Description: OpenPGP digital signature
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 > >> autovacuum, dump, .. > > > > But autovacuum and dump are not idle in transaction or am I missing > > something? > > last Merlin's proposal was about transaction_timeout not > transaction_idle_timeout 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. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 > > > >> > it doesn't help. How I can set transaction_timeout if I have series of > >> > slow > >> > statements? In this case I cannot to set transaction_timeout before > any > >> > statement or after any success statement. > >> > >> Not quite following you. The client has to go: > >> BEGIN; > >> SET transaction_timeout = x; > >> > > > > where is the point when transaction_timeout start? In BEGIN or in SET > > transaction_timeout ? > > transaction start (BEGIN). > > > How I can emulate transaction_idle_timeout? Can I refresh > > transaction_timeout? > > Well, for my part, I'd probably set default to around an hour with > longer running batch driven tasks having to override. > > > 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 lower timeout, say, 5 minutes or less > since it almost never comes up in real applications. In fact, in 15 > years of postgres development, I've never seen 'idle transaction' that > indicated anything but application malfunction. > > That being said, hour timeout for general case would work for me. It > would only have to be set lower for very busy OLTP databases where > continuous vacuum is essential. In those cases, I don't mind forcing > all batch processes to disclose in advance they are running long. > If I have a statement_timeout 20minutes, what can be transaction_timeout? hour or 2 hours. If you don't know how much statements are in transaction, then is pretty difficult to set it. One hour is nothing for bigger databases with mix OLAP/OLTP and the age for massive used OLAP. Regards Pavel > > merlin >
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, .. >> > > But autovacuum and dump are not idle in transaction or am I missing > something? > last Merlin's proposal was about transaction_timeout not transaction_idle_timeout Regards Pavel > > JD > > > -- > Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 > PostgreSQL Centered full stack support, consulting and development. > New rule for social situations: "If you think to yourself not even > JD would say this..." Stop and shut your mouth. It's going to be bad. >
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 -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. New rule for social situations: "If you think to yourself not even JD would say this..." Stop and shut your mouth. It's going to be bad. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 transaction'. Agree that a setting directed purely at >> that problem could set a much lower timeout, say, 5 minutes or less >> since it almost never comes up in real applications. In fact, in 15 >> years of postgres development, I've never seen 'idle transaction' that >> indicated anything but application malfunction. >> > > I can +1 that. > > >> That being said, hour timeout for general case would work for me. It >> would only have to be set lower for very busy OLTP databases where >> continuous vacuum is essential. In those cases, I don't mind forcing >> all batch processes to disclose in advance they are running long. >> > > Yeah about an hour sounds right. > 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, .. Regards Pavel > > > JD > > >> merlin >> >> >> > > -- > Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 > PostgreSQL Centered full stack support, consulting and development. > New rule for social situations: "If you think to yourself not even > JD would say this..." Stop and shut your mouth. It's going to be bad. >
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 lower timeout, say, 5 minutes or less since it almost never comes up in real applications. In fact, in 15 years of postgres development, I've never seen 'idle transaction' that indicated anything but application malfunction. I can +1 that. That being said, hour timeout for general case would work for me. It would only have to be set lower for very busy OLTP databases where continuous vacuum is essential. In those cases, I don't mind forcing all batch processes to disclose in advance they are running long. Yeah about an hour sounds right. JD merlin -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. New rule for social situations: "If you think to yourself not even JD would say this..." Stop and shut your mouth. It's going to be bad. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 of >> > slow >> > statements? In this case I cannot to set transaction_timeout before any >> > statement or after any success statement. >> >> Not quite following you. The client has to go: >> BEGIN; >> SET transaction_timeout = x; >> > > where is the point when transaction_timeout start? In BEGIN or in SET > transaction_timeout ? transaction start (BEGIN). > How I can emulate transaction_idle_timeout? Can I refresh > transaction_timeout? Well, for my part, I'd probably set default to around an hour with longer running batch driven tasks having to override. > 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 lower timeout, say, 5 minutes or less since it almost never comes up in real applications. In fact, in 15 years of postgres development, I've never seen 'idle transaction' that indicated anything but application malfunction. That being said, hour timeout for general case would work for me. It would only have to be set lower for very busy OLTP databases where continuous vacuum is essential. In those cases, I don't mind forcing all batch processes to disclose in advance they are running long. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 > > >> 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 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. > >> >> > >> >> > Note, having both settings would work too. > >> >> > >> >> I'd vote for just transaction_timeout. The way our timeout manager > >> >> logic works, that should be more efficient, as the timeout would only > >> >> have to be established once at transaction start, not every time the > >> >> main command loop iterates. > >> > > >> > > >> > I cannot to say, so transaction_timeout is not useful, but it cannot > be > >> > effective solution for some mentioned issues. With larger data you > >> > cannot to > >> > set transaction_timeout less than few hours. > >> > >> sure. note however any process can manually opt in to a longer timeout. > > > > > > it doesn't help. How I can set transaction_timeout if I have series of > slow > > statements? In this case I cannot to set transaction_timeout before any > > statement or after any success statement. > > Not quite following you. The client has to go: > BEGIN; > SET transaction_timeout = x; > > where is the point when transaction_timeout start? In BEGIN or in SET transaction_timeout ? How I can emulate transaction_idle_timeout? Can I refresh transaction_timeout? 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. Regards Pavel > or the client can do that on session start up. There are two problem > cases I can think of: > 1) connection pooler (pgbouncer): This can work, but you have to be > very careful. Maybe DISCARD needs to be able to undo adjusted > session settings if it doesn't already. > > 2) procedure emulating functions: It's a major pain that you can't > manage timeout inside a function itself. You also can't manage > transaction state or isolation level. The real solution here is to > implement stored procedures though. > > merlin >
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 : >> >> >> >> 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 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. >> >> >> >> > Note, having both settings would work too. >> >> >> >> I'd vote for just transaction_timeout. The way our timeout manager >> >> logic works, that should be more efficient, as the timeout would only >> >> have to be established once at transaction start, not every time the >> >> main command loop iterates. >> > >> > >> > I cannot to say, so transaction_timeout is not useful, but it cannot be >> > effective solution for some mentioned issues. With larger data you >> > cannot to >> > set transaction_timeout less than few hours. >> >> sure. note however any process can manually opt in to a longer timeout. > > > it doesn't help. How I can set transaction_timeout if I have series of slow > statements? In this case I cannot to set transaction_timeout before any > statement or after any success statement. Not quite following you. The client has to go: BEGIN; SET transaction_timeout = x; or the client can do that on session start up. There are two problem cases I can think of: 1) connection pooler (pgbouncer): This can work, but you have to be very careful. Maybe DISCARD needs to be able to undo adjusted session settings if it doesn't already. 2) procedure emulating functions: It's a major pain that you can't manage timeout inside a function itself. You also can't manage transaction state or isolation level. The real solution here is to implement stored procedures though. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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. > >> > >> > Note, having both settings would work too. > >> > >> I'd vote for just transaction_timeout. The way our timeout manager > >> logic works, that should be more efficient, as the timeout would only > >> have to be established once at transaction start, not every time the > >> main command loop iterates. > > > > > > I cannot to say, so transaction_timeout is not useful, but it cannot be > > effective solution for some mentioned issues. With larger data you > cannot to > > set transaction_timeout less than few hours. > > sure. note however any process can manually opt in to a longer timeout. > it doesn't help. How I can set transaction_timeout if I have series of slow statements? In this case I cannot to set transaction_timeout before any statement or after any success statement. > > merlin >
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 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. >> >> > Note, having both settings would work too. >> >> I'd vote for just transaction_timeout. The way our timeout manager >> logic works, that should be more efficient, as the timeout would only >> have to be established once at transaction start, not every time the >> main command loop iterates. > > > I cannot to say, so transaction_timeout is not useful, but it cannot be > effective solution for some mentioned issues. With larger data you cannot to > set transaction_timeout less than few hours. sure. note however any process can manually opt in to a longer timeout. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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. > > > Note, having both settings would work too. > > I'd vote for just transaction_timeout. The way our timeout manager > logic works, that should be more efficient, as the timeout would only > have to be established once at transaction start, not every time the > main command loop iterates. > I cannot to say, so transaction_timeout is not useful, but it cannot be effective solution for some mentioned issues. With larger data you cannot to set transaction_timeout less than few hours. Regards Pavel > > regards, tom lane >
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 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. > Note, having both settings would work too. I'd vote for just transaction_timeout. The way our timeout manager logic works, that should be more efficient, as the timeout would only have to be established once at transaction start, not every time the main command loop iterates. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 > >> 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 >
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 >>> 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. Note, having both settings would work too. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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 of > state" - I understand if text is "cancels transaction after X time if state is "idle in tramsaction" Pavel > > The problem at hand is idle *transactions*, not sessions, and a > configuration setting that deals with transaction time. I do not > understand the objection to setting an upper bound on transaction > time. I'm ok with cancelling or dumping the session with a slight > preference on cancel. > > merlin >
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 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. The problem at hand is idle *transactions*, not sessions, and a configuration setting that deals with transaction time. I do not understand the objection to setting an upper bound on transaction time. I'm ok with cancelling or dumping the session with a slight preference on cancel. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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? Pavel > > merlin >
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 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 > Agreed. > and > *) transaction_timeout: cancels transaction after X time, regardless of state > I am not sure about this, let us see if any body else has opinion about this parameter. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
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 >>> 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
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 transaction, prepared > > statements, etc. and retain it for connection pool kind of stuff > > b. Exit from the session > > > > If we want something on lines of option (a), then I think it is better > > to have just a single time out (session_idle_timeout/idle_timeout) > > I'm not thrilled about the prefix 'session_': most .conf variables > apply to the session (like statement_timeout) and we don't use the > session prefix for any of those. > > "transaction_idle_timeout" is ok, if you want the timeout to apply as > an expiration for a transaction going idle. > > "idle_timeout" doesn't make much sense to me. It's the responsibility > of the pooler to mange idle-but-not-in-transaction sessions and we > already have machinery to support that (DISCARD). > I think if transaction is idle for long time, then the chances that someone will use that session is less, so idle_timeout seems to me the right tool for such sessions. I have checked that databases like Oracle also has such a variable to help out users for such situations. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
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. I would love a better way to > detect (or prevent) clients from being brain-dead about how they're using > transactions, but short of that this is the next best thing. > > 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 (minus a couple of anachronisms) .conf setting thats control behavior of SQL commands in a non performance way. IMO, this as yet another case for 'stored procedures' that can manage transaction state: you could rig up your own procedure: CALL begin_tx_safe(); which would test transaction state and fail if already in one. This doesn't help you if you're not in direct control of application generated SQL but it's a start. Barring that, at least warnings tend to stand out in the database log. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 >> 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 Regards Pavel > > If we want something on lines of option (a), then I think it is better > to have just a single time out (session_idle_timeout/idle_timeout) > > > > With Regards, > Amit Kapila. > EnterpriseDB: http://www.enterprisedb.com >
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 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 > > If we want something on lines of option (a), then I think it is better > to have just a single time out (session_idle_timeout/idle_timeout) I'm not thrilled about the prefix 'session_': most .conf variables apply to the session (like statement_timeout) and we don't use the session prefix for any of those. "transaction_idle_timeout" is ok, if you want the timeout to apply as an expiration for a transaction going idle. "idle_timeout" doesn't make much sense to me. It's the responsibility of the pooler to mange idle-but-not-in-transaction sessions and we already have machinery to support that (DISCARD). "transaction_timeout" is the best, and simplest, hypothetical setting IMNSHO. It gives you a well defined upper bound guarantee of transaction time regardless of application behavior, which neither statement_timeout or transaction_idle_timeout give, even when used in conjunction as I understand them. It would completely displace statement_timeout in all servers I manage. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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 If we want something on lines of option (a), then I think it is better to have just a single time out (session_idle_timeout/idle_timeout) With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
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 they're using transactions, but short of that this is the next best thing. 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 -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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. > > > > This is only partial solution - when some application is broken, then > there > > will be orphaned sessions. It is less wrong, than orphaned connections, > but > > it can enforce some issues too. The solution of this problem should to > work > > well with session pool sw like pgbouncer and similar. > I wrote a nonsense - should be "It is less wrong, than orphaned transaction" > > Sure. Unfortunately it's not always practical to do so when you have > 100's of applications running against 100's of databases, all written > by teams of variable quality, some of whom have been ejected for > overseas devlopment or vice versa. This is the world I live in. > I would to say so the breaking transaction is not enough - it needs some protocol enhancing. There is a advantage of terminate_session, because if keep_alive packets are used, then client can to know so session is broken in few seconds. > > The point stands that neither pg_cancel_backend or statement_timeout > (especially) provide *any* kind of safety guarantees because they only > work if execution is in the database. All the locks they hold and > other long running issues pertaining to long running transactions > (say, advancing xmin) are silent killers with no automatic way of > detecting or destroying. I understand the challenges here -- not > griping in any way -- the workaround is to cron up an executioner. > Just pointing out we have an issue. > 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? Regards Pavel > > merlin >
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, 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 pg_cancel_backend. >> > >> > > Not when the session is idle in transaction, only when it's actually >> > > doing >> > > something. >> > >> >> Okay, thats right and the reason is that while reading message from >> client, >> if an error occurs, it can loose track of previous and next messages and >> that >> could lead to an unrecoverable state. >> >> > >> > I think in principle it could be done by transitioning the backend into >> > a new xact.c state, wherein we know that the active transaction has been >> > canceled (at least to the extent of releasing externally visible >> > resources >> > such as locks and snapshots), but this fact hasn't been reported to the >> > connected client. Then the next command submitted by the client would >> > get >> > a "transaction cancelled" error and we'd go into the normal transaction- >> > failed state. >> > >> >> 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. > > This is only partial solution - when some application is broken, then there > will be orphaned sessions. It is less wrong, than orphaned connections, but > it can enforce some issues too. The solution of this problem should to work > well with session pool sw like pgbouncer and similar. Sure. Unfortunately it's not always practical to do so when you have 100's of applications running against 100's of databases, all written by teams of variable quality, some of whom have been ejected for overseas devlopment or vice versa. This is the world I live in. The point stands that neither pg_cancel_backend or statement_timeout (especially) provide *any* kind of safety guarantees because they only work if execution is in the database. All the locks they hold and other long running issues pertaining to long running transactions (say, advancing xmin) are silent killers with no automatic way of detecting or destroying. I understand the challenges here -- not griping in any way -- the workaround is to cron up an executioner. Just pointing out we have an issue. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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. > > > > > Not when the session is idle in transaction, only when it's actually > doing > > > something. > > > > Okay, thats right and the reason is that while reading message from client, > if an error occurs, it can loose track of previous and next messages and > that > could lead to an unrecoverable state. > > > > > I think in principle it could be done by transitioning the backend into > > a new xact.c state, wherein we know that the active transaction has been > > canceled (at least to the extent of releasing externally visible > resources > > such as locks and snapshots), but this fact hasn't been reported to the > > connected client. Then the next command submitted by the client would > get > > a "transaction cancelled" error and we'd go into the normal transaction- > > failed state. > > > > 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. > This is only partial solution - when some application is broken, then there will be orphaned sessions. It is less wrong, than orphaned connections, but it can enforce some issues too. The solution of this problem should to work well with session pool sw like pgbouncer and similar. Regards Pavel > > > With Regards, > Amit Kapila. > EnterpriseDB: http://www.enterprisedb.com >
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 want to rollback current transaction, I think that can be > >> achieved by pg_cancel_backend. > > > Not when the session is idle in transaction, only when it's actually doing > > something. > Okay, thats right and the reason is that while reading message from client, if an error occurs, it can loose track of previous and next messages and that could lead to an unrecoverable state. > > I think in principle it could be done by transitioning the backend into > a new xact.c state, wherein we know that the active transaction has been > canceled (at least to the extent of releasing externally visible resources > such as locks and snapshots), but this fact hasn't been reported to the > connected client. Then the next command submitted by the client would get > a "transaction cancelled" error and we'd go into the normal transaction- > failed state. > 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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
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 pg_cancel_backend. > Not when the session is idle in transaction, only when it's actually doing > something. > IIRC one of the reasons is that when idle in transaction, the client is not > expecting any response, and would get out of sync. I know this has been > discussed a number of times, so a better explanation can probably be found > in the archives :) I think in principle it could be done by transitioning the backend into a new xact.c state, wherein we know that the active transaction has been canceled (at least to the extent of releasing externally visible resources such as locks and snapshots), but this fact hasn't been reported to the connected client. Then the next command submitted by the client would get a "transaction cancelled" error and we'd go into the normal transaction- failed state. I don't think this would be exactly trivial, but it's probably doable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
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 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. > Not when the session is idle in transaction, only when it's actually doing something. IIRC one of the reasons is that when idle in transaction, the client is not expecting any response, and would get out of sync. I know this has been discussed a number of times, so a better explanation can probably be found in the archives :) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
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 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. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
[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 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. It's nice to think about a world where applications don't do such things, but in this endless sea of enterprise java soup I live it it's, uh, not realistic. This would be lot cleaner than the cron driven sweep I'm forced to implement now, and could be made to be part of the standard configuration across the enterprise. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers