Re: [HACKERS] idle_in_transaction_timeout

2014-07-07 Thread Bruce Momjian
On Tue, Jun 24, 2014 at 10:17:49AM -0700, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 06/23/2014 03:52 PM, Andres Freund wrote: True. Which makes me wonder whether we shouldn't default this to something non-zero -- even if it is 5 or 10 days. I'd go for even shorter: 48

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: On Wed, Jun 25, 2014 at 11:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: Fujii Masao masao.fu...@gmail.com writes: Why is IIT timeout turned on only when send_ready_for_query is true? I was thinking it should be turned on every time a message is

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Tom Lane
Kevin Grittner kgri...@ymail.com writes: I propose to push this as it stands except for the postgres_fdw part.  The default is easy enough to change if we reach consensus, and expanding the scope can be a new patch in a new CF. Objections? There's been enough noise about the external

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Robert Haas
On Sun, Jun 29, 2014 at 12:32 PM, Kevin Grittner kgri...@ymail.com wrote: Moreover, there would be no way to implement a timeout like that without adding a gettimeofday() call after every packet receipt, which is overhead we do not need and cannot afford. I don't think this feature should

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sun, Jun 29, 2014 at 12:32 PM, Kevin Grittner kgri...@ymail.com wrote: I propose to push this as it stands except for the postgres_fdw part. The default is easy enough to change if we reach consensus, and expanding the scope can be a new patch in a

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Andres Freund
On 2014-06-29 15:48:15 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Jun 29, 2014 at 12:32 PM, Kevin Grittner kgri...@ymail.com wrote: I propose to push this as it stands except for the postgres_fdw part. The default is easy enough to change if we reach

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2014-06-29 15:48:15 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Yeah, I think someone should do some analysis of whether this is adding gettimeofday() calls, and how many, and what the performance implications are. I

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Andres Freund
On 2014-06-29 12:53:56 -0400, Tom Lane wrote: I do not think it is: specifically, the notion that we will call ereport(FATAL) directly from a signal handler does not fill me with warm fuzzies. Aren't we already pretty much doing that for SIGTERM/pg_terminate_backend() and recovery conflict

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Andres Freund
On 2014-06-29 17:28:06 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-06-29 15:48:15 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Yeah, I think someone should do some analysis of whether this is adding gettimeofday() calls, and how many,

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2014-06-29 12:53:56 -0400, Tom Lane wrote: I do not think it is: specifically, the notion that we will call ereport(FATAL) directly from a signal handler does not fill me with warm fuzzies. Aren't we already pretty much doing that for

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Andres Freund
On 2014-06-29 19:13:55 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-06-29 12:53:56 -0400, Tom Lane wrote: I do not think it is: specifically, the notion that we will call ereport(FATAL) directly from a signal handler does not fill me with warm fuzzies.

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: Note that we're *inside* recv() here. Well, actually, the recv() has probably failed with an EINTR at this point, or else it will when/if control returns. Uh, no, that would pretty much destroy the point of trying to report an error message at all.

Re: [HACKERS] idle_in_transaction_timeout

2014-06-29 Thread Andres Freund
On 2014-06-29 19:51:05 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: Note that we're *inside* recv() here. Well, actually, the recv() has probably failed with an EINTR at this point, or else it will when/if control returns. Well, the point is that we'll be reentering

Re: [HACKERS] idle_in_transaction_timeout

2014-06-27 Thread Robert Haas
On Wed, Jun 25, 2014 at 11:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: Fujii Masao masao.fu...@gmail.com writes: Why is IIT timeout turned on only when send_ready_for_query is true? I was thinking it should be turned on every time a message is received. Imagine the case where the session is in

Re: [HACKERS] idle_in_transaction_timeout

2014-06-26 Thread Vik Fearing
On 06/26/2014 06:45 AM, Fujii Masao wrote: The point of this feature, AFAICS, is to detect clients that are failing to issue another query or close their transaction as a result of bad client logic. It's not to protect against network glitches. If so, the document should explain that

Re: [HACKERS] idle_in_transaction_timeout

2014-06-25 Thread Tom Lane
Fujii Masao masao.fu...@gmail.com writes: Why is IIT timeout turned on only when send_ready_for_query is true? I was thinking it should be turned on every time a message is received. Imagine the case where the session is in idle-in-transaction state and a client gets stuck after sending Parse

Re: [HACKERS] idle_in_transaction_timeout

2014-06-25 Thread Fujii Masao
On Thu, Jun 26, 2014 at 12:40 AM, Tom Lane t...@sss.pgh.pa.us wrote: Fujii Masao masao.fu...@gmail.com writes: Why is IIT timeout turned on only when send_ready_for_query is true? I was thinking it should be turned on every time a message is received. Imagine the case where the session is in

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Vik Fearing
On 06/22/2014 05:11 PM, Kevin Grittner wrote: I found one substantive issue that had been missed in discussion, though. The patch modifies the postgres_fdw extension to make it automatically exempt from an attempt to set a limit like this on the server to which it connects. I'm not sure

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread David G Johnston
On Tue, Jun 24, 2014 at 9:20 AM, Vik Fearing [via PostgreSQL] ml-node+s1045698n5808882...@n5.nabble.com wrote: On 06/22/2014 05:11 PM, Kevin Grittner wrote: I found one substantive issue that had been missed in discussion, though. The patch modifies the postgres_fdw extension to make it

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Kevin Grittner
David G Johnston david.g.johns...@gmail.com wrote: Vik Fearing [via PostgreSQL] [hidden email]wrote: On 06/22/2014 05:11 PM, Kevin Grittner wrote: I found one substantive issue that had been missed in discussion, though.  The patch modifies the postgres_fdw extension to make it automatically

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Vik Fearing
On 06/24/2014 03:29 PM, David G Johnston wrote: On Tue, Jun 24, 2014 at 9:20 AM, Vik Fearing [via PostgreSQL] [hidden email] /user/SendEmail.jtp?type=nodenode=5808883i=0wrote: On 06/22/2014 05:11 PM, Kevin Grittner wrote: I found one substantive issue that had been missed in

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Robert Haas
On Tue, Jun 24, 2014 at 9:18 AM, Vik Fearing vik.fear...@dalibo.com wrote: On 06/22/2014 05:11 PM, Kevin Grittner wrote: I found one substantive issue that had been missed in discussion, though. The patch modifies the postgres_fdw extension to make it automatically exempt from an attempt to

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Andres Freund
On 2014-06-24 10:04:03 -0400, Robert Haas wrote: On Tue, Jun 24, 2014 at 9:18 AM, Vik Fearing vik.fear...@dalibo.com wrote: My reasoning for doing it the way I did is that if a transaction touches a foreign table and then goes bumbling along with other things the transaction is active but

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread David G Johnston
On Tue, Jun 24, 2014 at 10:05 AM, Robert Haas [via PostgreSQL] ml-node+s1045698n580889...@n5.nabble.com wrote: On Tue, Jun 24, 2014 at 9:18 AM, Vik Fearing [hidden email] http://user/SendEmail.jtp?type=nodenode=5808893i=0 wrote: On 06/22/2014 05:11 PM, Kevin Grittner wrote: I found one

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Vik Fearing
On 06/24/2014 04:04 PM, Robert Haas wrote: If the local transaction is actually idle in transaction and the local server doesn't have a timeout, we're no worse off than before this patch. I think we are. First, the correct timeout is a matter of remote-server-policy, not

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Robert Haas
On Tue, Jun 24, 2014 at 10:50 AM, Vik Fearing vik.fear...@dalibo.com wrote: On 06/24/2014 04:04 PM, Robert Haas wrote: If the local transaction is actually idle in transaction and the local server doesn't have a timeout, we're no worse off than before this patch. I think we are. First, the

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread David G Johnston
On Tue, Jun 24, 2014 at 11:11 AM, Robert Haas [via PostgreSQL] ml-node+s1045698n5808915...@n5.nabble.com wrote: On Tue, Jun 24, 2014 at 10:50 AM, Vik Fearing [hidden email] http://user/SendEmail.jtp?type=nodenode=5808915i=0 wrote: On 06/24/2014 04:04 PM, Robert Haas wrote: If the local

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Josh Berkus
On 06/23/2014 03:52 PM, Andres Freund wrote: On 2014-06-23 13:19:47 -0700, Kevin Grittner wrote: which already seems less clear (because the transaction belongs to idle) I have no idea what that means. It's idle_in_transaction_session_timeout. Not idle_in_transaction_session_timeout.

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Vik Fearing
On 06/24/2014 06:43 PM, Josh Berkus wrote: A long idle in transaction state pretty much always indicates a problematic interaction with postgres. True. Which makes me wonder whether we shouldn't default this to something non-zero -- even if it is 5 or 10 days. I'd go for even shorter:

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Pavel Stehule
2014-06-24 18:43 GMT+02:00 Josh Berkus j...@agliodbs.com: On 06/23/2014 03:52 PM, Andres Freund wrote: On 2014-06-23 13:19:47 -0700, Kevin Grittner wrote: which already seems less clear (because the transaction belongs to idle) I have no idea what that means. It's

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Josh Berkus
On 06/24/2014 07:50 AM, Vik Fearing wrote: On 06/24/2014 04:04 PM, Robert Haas wrote: If the local transaction is actually idle in transaction and the local server doesn't have a timeout, we're no worse off than before this patch. I think we are. First, the correct timeout is a matter of

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: On 06/24/2014 07:50 AM, Vik Fearing wrote: Once the remote times out, the local transaction is doomed (and won't even know it until it tries to commit). If we don't allow the fdw to be special, then the local transaction can't run at all. Ever. I'm

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: On 06/23/2014 03:52 PM, Andres Freund wrote: True. Which makes me wonder whether we shouldn't default this to something non-zero -- even if it is 5 or 10 days. I'd go for even shorter: 48 hours. I'd suggest 24 hours, but that would trip up some users

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Vik Fearing
On 06/24/2014 07:17 PM, Tom Lane wrote: BTW, has anyone thought about the interaction of this feature with prepared transactions? I wonder whether there shouldn't be a similar but separately-settable maximum time for a transaction to stay in the prepared state. If we could set a nonzero

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Andres Freund
On 2014-06-24 10:17:49 -0700, Tom Lane wrote: BTW, has anyone thought about the interaction of this feature with prepared transactions? I wonder whether there shouldn't be a similar but separately-settable maximum time for a transaction to stay in the prepared state. If we could set a

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Tom Lane
Andres Freund and...@2ndquadrant.com writes: On 2014-06-24 10:17:49 -0700, Tom Lane wrote: BTW, has anyone thought about the interaction of this feature with prepared transactions? I wonder whether there shouldn't be a similar but separately-settable maximum time for a transaction to stay in

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Which makes me wonder whether we shouldn't default this to something non-zero -- even if it is 5 or 10 days. I'd go for even shorter: 48 hours.  I'd suggest 24 hours, but that would trip up some users who just need

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-06-24 10:17:49 -0700, Tom Lane wrote: BTW, has anyone thought about the interaction of this feature with prepared transactions? I wonder whether there shouldn't be a similar but

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Josh Berkus
On 06/24/2014 10:17 AM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: On 06/23/2014 03:52 PM, Andres Freund wrote: True. Which makes me wonder whether we shouldn't default this to something non-zero -- even if it is 5 or 10 days. I'd go for even shorter: 48 hours. I'd suggest 24

Re: [HACKERS] idle_in_transaction_timeout

2014-06-24 Thread Fujii Masao
On Sun, Jun 22, 2014 at 6:54 AM, Vik Fearing vik.fear...@dalibo.com wrote: On 06/21/2014 08:23 PM, Kevin Grittner wrote: OK, so I think we want to see a patch based on v1 (FATAL approach) with a change of the name to idle_in_transaction_session_timeout and the units changed to milliseconds. I

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Andres Freund
On 2014-06-22 19:03:32 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: I think we'll want a version of this that just fails the transaction once we have the infrastructure. So we should choose a name that allows for a complimentary GUC. If we stick with the

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Fujii Masao
On Mon, Jun 23, 2014 at 7:48 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-22 19:03:32 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: I think we'll want a version of this that just fails the transaction once we have the infrastructure. So we should

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Andres Freund
On 2014-06-23 20:29:17 +0900, Fujii Masao wrote: On Mon, Jun 23, 2014 at 7:48 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-22 19:03:32 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: I think we'll want a version of this that just fails the

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Vik Fearing
On 06/22/2014 07:47 PM, Andres Freund wrote: On 2014-06-22 09:27:24 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: The idea with the GUC name is that if we ever get support for cancelling transactions we can name that idle_in_transaction_transaction_timeout? That

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Andres Freund
On 2014-06-23 13:33:46 +0200, Vik Fearing wrote: On 06/22/2014 07:47 PM, Andres Freund wrote: On 2014-06-22 09:27:24 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: but I don't like that much. Not sure what'd be good, the best I currently can come up with is:

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Josh Berkus
On 06/22/2014 09:02 PM, Abhijit Menon-Sen wrote: I (somewhat reluctantly) agree with Kevin that idle_in_transaction_session_timeout (for FATAL) and idle_transaction_timeout (for ERROR) would work. Given that an IIT timeout has been a TODO for at least 6 years before being addressed, I'm not

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote: On 2014-06-23 20:29:17 +0900, Fujii Masao wrote: On Mon, Jun 23, 2014 at 7:48 PM, Andres Freund and...@2ndquadrant.com wrote: On 2014-06-22 19:03:32 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: I think we'll want a

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread David G Johnston
I think that'd be rather confusing. For one it'd need to be idle_in_transaction_timeout Why? We're cancelling an idle transaction, not an idle in transaction, whatever that is. ​The confusion derives from the fact we are affecting a session whose state is idle in transaction, not one

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread David G Johnston
A long idle in transaction state pretty much always indicates a problematic interaction with postgres. True. Which makes me wonder whether we shouldn't default this to something non-zero -- even if it is 5 or 10 days. ​I guess it depends on how parental we want to be. But if we go that

Re: [HACKERS] idle_in_transaction_timeout

2014-06-23 Thread Andres Freund
On 2014-06-23 13:19:47 -0700, Kevin Grittner wrote: which already seems less clear (because the transaction belongs to idle) I have no idea what that means. It's idle_in_transaction_session_timeout. Not idle_in_transaction_session_timeout. and for another that distinction seems to be to

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread Kevin Grittner
Abhijit Menon-Sen a...@2ndquadrant.com I've marked it Ready for Committer after a quick read-through. I took a pass through it with an eye toward committing it.  I found a couple minor whitespace issues, where the patch didn't follow conventional indenting practice; I can fix that no problem. 

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread Andres Freund
On 2014-06-21 11:23:44 -0700, Kevin Grittner wrote: Andrew Dunstan and...@dunslane.net wrote: On 06/19/2014 06:33 PM, Josh Berkus wrote: ISTM our realistic options are for seconds or msec as the unit.  If it's msec, we'd be limited to INT_MAX msec or around 600 hours at the top end,

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote: The idea with the GUC name is that if we ever get support for cancelling transactions we can name that idle_in_transaction_transaction_timeout? That seems a bit awkward... No, the argument was that for all the other *_timeout settings what came

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread Andres Freund
On 2014-06-22 09:27:24 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: The idea with the GUC name is that if we ever get support for cancelling transactions we can name that idle_in_transaction_transaction_timeout? That seems a bit awkward... No, the argument

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread Pavel Stehule
2014-06-22 19:47 GMT+02:00 Andres Freund and...@2ndquadrant.com: On 2014-06-22 09:27:24 -0700, Kevin Grittner wrote: Andres Freund and...@2ndquadrant.com wrote: The idea with the GUC name is that if we ever get support for cancelling transactions we can name that

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread Kevin Grittner
Andres Freund and...@2ndquadrant.com wrote: I think we'll want a version of this that just fails the transaction once we have the infrastructure. So we should choose a name that allows for a complimentary GUC. If we stick with the rule that what is to the left of _timeout is what is being

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread David G Johnston
On Sunday, June 22, 2014, Kevin Grittner-5 [via PostgreSQL] ml-node+s1045698n580830...@n5.nabble.com wrote: Andres Freund [hidden email] http://user/SendEmail.jtp?type=nodenode=5808309i=0 wrote: I think we'll want a version of this that just fails the transaction once we have the

Re: [HACKERS] idle_in_transaction_timeout

2014-06-22 Thread Abhijit Menon-Sen
At 2014-06-22 19:45:08 -0700, david.g.johns...@gmail.com wrote: On Sunday, June 22, 2014, Kevin Grittner-5 [via PostgreSQL] ml-node+s1045698n580830...@n5.nabble.com wrote: If we stick with the rule that what is to the left of _timeout is what is being cancelled, the a GUC to cancel a

Re: [HACKERS] idle_in_transaction_timeout

2014-06-21 Thread Kevin Grittner
Andrew Dunstan and...@dunslane.net wrote: On 06/19/2014 06:33 PM, Josh Berkus wrote: ISTM our realistic options are for seconds or msec as the unit.  If it's msec, we'd be limited to INT_MAX msec or around 600 hours at the top end, which seems like enough to me but maybe somebody thinks

Re: [HACKERS] idle_in_transaction_timeout

2014-06-21 Thread Vik Fearing
On 06/21/2014 08:23 PM, Kevin Grittner wrote: OK, so I think we want to see a patch based on v1 (FATAL approach) with a change of the name to idle_in_transaction_session_timeout and the units changed to milliseconds. I don't see why the remoteversion test shouldn't be changed to use 90500

Re: [HACKERS] idle_in_transaction_timeout

2014-06-21 Thread Abhijit Menon-Sen
At 2014-06-21 23:54:58 +0200, vik.fear...@dalibo.com wrote: I'll flip this to Waiting on Author for those changes. And back to Needs Review. I've marked it Ready for Committer after a quick read-through. -- Abhijit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Pavel Stehule
Hello pgbouncer has idle_transaction_timeout defined some years without any problems, so we can take this design idle_transaction_timeout If client has been in idle in transaction state longer, it will be disconnected. [seconds] Default: 0.0 (disabled) This feature can be very important, and

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Abhijit Menon-Sen
At 2014-06-19 10:58:34 +0200, pavel.steh...@gmail.com wrote: Hello pgbouncer has idle_transaction_timeout defined some years without any problems, so we can take this design idle_transaction_timeout Let's steal the name too. :-) (I didn't realise there was precedent in pgbouncer, but

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Kevin Grittner
Abhijit Menon-Sen a...@2ndquadrant.com wrote: At 2014-06-19 10:58:34 +0200, pavel.steh...@gmail.com wrote: pgbouncer has idle_transaction_timeout defined some years without any problems, so we can take this design idle_transaction_timeout Let's steal the name too. :-) (I didn't realise

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Vik Fearing
On 06/19/2014 05:42 PM, Kevin Grittner wrote: Abhijit Menon-Sen a...@2ndquadrant.com wrote: At 2014-06-19 10:58:34 +0200, pavel.steh...@gmail.com wrote: pgbouncer has idle_transaction_timeout defined some years without any problems, so we can take this design idle_transaction_timeout

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Abhijit Menon-Sen
At 2014-06-19 08:42:01 -0700, kgri...@ymail.com wrote: I'm not sure whether using the same name as pgbouncer for the same functionality makes it less confusing or might lead to confusion about which layer is disconnecting the client. I don't think the names of the respective configuration

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Abhijit Menon-Sen
At 2014-06-19 17:53:17 +0200, vik.fear...@dalibo.com wrote: I much prefer with in but it doesn't much matter. If you look at similar settings like statement_timeout, lock_timeout, etc., what comes before the _timeout is a concrete *thing* that can timeout, or that a timeout can be applied to

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread David G Johnston
On Thu, Jun 19, 2014 at 12:40 PM, Abhijit Menon-Sen-2 [via PostgreSQL] ml-node+s1045698n5808016...@n5.nabble.com wrote: At 2014-06-19 17:53:17 +0200, [hidden email] http://user/SendEmail.jtp?type=nodenode=5808016i=0 wrote: I much prefer with in but it doesn't much matter. If you look at

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Josh Berkus
On 06/19/2014 10:39 AM, David G Johnston wrote: Advocating for the Devil (or a more robust, if probably complicated, solution): idle_in_transaction_timeout=10s idle_in_transaction_target=session|transaction Per Kevin Grittner's assessment, aborting the transaction is currently a nonstarter.

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Kevin Grittner
Josh Berkus j...@agliodbs.com wrote: Also, I really hope that nobody is setting this to 10s ... Well, we get to decide what the minimum allowed value is.  What do you think that should be? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Josh Berkus
On 06/19/2014 02:44 PM, Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: Also, I really hope that nobody is setting this to 10s ... Well, we get to decide what the minimum allowed value is. What do you think that should be? 1s? I don't think that setting it to 1s would ever be

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: Now ... can we decrease CPU overhead (wakeups) if we only check once per minute? If so, there's a good argument for making 1min the minimum. Polling wakeups are right out, and are unnecessary anyway. The utils/misc/timeout.c infrastructure calculates the

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Josh Berkus
Tom, ISTM our realistic options are for seconds or msec as the unit. If it's msec, we'd be limited to INT_MAX msec or around 600 hours at the top end, which seems like enough to me but maybe somebody thinks differently? Seconds are probably OK but I'm worried about somebody complaining that

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Alvaro Herrera
Josh Berkus wrote: Tom, ISTM our realistic options are for seconds or msec as the unit. If it's msec, we'd be limited to INT_MAX msec or around 600 hours at the top end, which seems like enough to me but maybe somebody thinks differently? Seconds are probably OK but I'm worried about

Re: [HACKERS] idle_in_transaction_timeout

2014-06-19 Thread Andrew Dunstan
On 06/19/2014 06:33 PM, Josh Berkus wrote: Tom, ISTM our realistic options are for seconds or msec as the unit. If it's msec, we'd be limited to INT_MAX msec or around 600 hours at the top end, which seems like enough to me but maybe somebody thinks differently? Seconds are probably OK but

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I thought the reason why this hasn't been implemented before now is that sending an ErrorResponse to the client will result in a loss of protocol sync. Hmm ... you are

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Josh Berkus
On 06/18/2014 11:50 AM, Kevin Grittner wrote: The first thing is that I don't think a delay between the BEGIN and the SELECT should cause a timeout to trigger, but more importantly there should not be two ERROR responses to one SELECT statement. I do think a delay between BEGIN and SELECT

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: There are plenty of badly-written applications which auto-begin, that is, they issue a BEGIN; immediately after every COMMIT; whether or not there's any additional work to do. This is a major source of IIT and the timeout should not ignore it. Nonsense.

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Josh Berkus
On 06/18/2014 12:32 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: There are plenty of badly-written applications which auto-begin, that is, they issue a BEGIN; immediately after every COMMIT; whether or not there's any additional work to do. This is a major source of IIT and the

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Robert Haas
On Wed, Jun 18, 2014 at 3:53 PM, Josh Berkus j...@agliodbs.com wrote: On 06/18/2014 12:32 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: There are plenty of badly-written applications which auto-begin, that is, they issue a BEGIN; immediately after every COMMIT; whether or not

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Bruce Momjian
On Wed, Jun 18, 2014 at 04:41:30PM -0400, Robert Haas wrote: On Wed, Jun 18, 2014 at 3:53 PM, Josh Berkus j...@agliodbs.com wrote: On 06/18/2014 12:32 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: There are plenty of badly-written applications which auto-begin, that is, they

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Josh Berkus
On 06/18/2014 02:52 PM, Bruce Momjian wrote: On Wed, Jun 18, 2014 at 04:41:30PM -0400, Robert Haas wrote: The only problem I see is that it makes the semantics kind of weird and confusing. Kill connections that are idle in transaction for too long is a pretty clear spec; kill connections that

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Marko Tiikkaja
On 2014-06-19 1:46 AM, Josh Berkus wrote: Robert's right, not killing the BEGIN; only transactions is liable to result in user confusion unless we label those sessions differently in pg_stat_activity. Wouldn't they be labeled differently already? i.e. the last query would be BEGIN. Unless

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Josh Berkus
On 06/18/2014 04:54 PM, Marko Tiikkaja wrote: On 2014-06-19 1:46 AM, Josh Berkus wrote: Robert's right, not killing the BEGIN; only transactions is liable to result in user confusion unless we label those sessions differently in pg_stat_activity. Wouldn't they be labeled differently

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: Counter-argument: most app frameworks which do an automatic BEGIN; also do other stuff like SET TIMEZONE each time as well. Is this really a case worth worrying about? SET TIMEZONE doesn't result in taking a snapshot either.

Re: [HACKERS] idle_in_transaction_timeout

2014-06-18 Thread David G Johnston
On Wed, Jun 18, 2014 at 8:01 PM, Josh Berkus [via PostgreSQL] ml-node+s1045698n5807868...@n5.nabble.com wrote: On 06/18/2014 04:54 PM, Marko Tiikkaja wrote: On 2014-06-19 1:46 AM, Josh Berkus wrote: Robert's right, not killing the BEGIN; only transactions is liable to result in user

Re: [HACKERS] idle_in_transaction_timeout

2014-06-04 Thread Andres Freund
On 2014-06-03 23:37:28 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: I thought the reason why this hasn't been implemented before now is that sending an ErrorResponse to the client will result in a loss of protocol sync. Hmm ... you are right that this isn't as simple

[HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Vik Fearing
This patch implements a timeout for broken clients that idle in transaction. This is a TODO item. When the timeout occurs, the backend commits suicide with a FATAL ereport. I thought about just aborting the transaction to free the locks but decided that the client is clearly broken so might as

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Abhijit Menon-Sen
At 2014-06-03 15:06:11 +0200, vik.fear...@dalibo.com wrote: This patch implements a timeout for broken clients that idle in transaction. I think this is a nice feature, but I suggest that (at the very least) the GUC should be named idle_transaction_timeout. +para + Terminate

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Vik Fearing
On 06/03/2014 03:30 PM, Abhijit Menon-Sen wrote: At 2014-06-03 15:06:11 +0200, vik.fear...@dalibo.com wrote: This patch implements a timeout for broken clients that idle in transaction. I think this is a nice feature, but I suggest that (at the very least) the GUC should be named

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread David G Johnston
Vik Fearing wrote On 06/03/2014 03:30 PM, Abhijit Menon-Sen wrote: At 2014-06-03 15:06:11 +0200, vik.fearing@ wrote: This patch implements a timeout for broken clients that idle in transaction. I think this is a nice feature, but I suggest that (at the very least) the GUC should be

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Josh Berkus
Vik, When the timeout occurs, the backend commits suicide with a FATAL ereport. I thought about just aborting the transaction to free the locks but decided that the client is clearly broken so might as well free up the connection as well. Out of curiosity, how much harder would it have been

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes: Out of curiosity, how much harder would it have been just to abort the transaction? I think breaking the connection is probably the right behavior, but before folks start arguing it out, I wanted to know if aborting the transaction is even a reasonable

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Alvaro Herrera
Josh Berkus wrote: Argument in favor of breaking the connection: most of the time, IITs are caused by poor error-handling or garbage-collection code on the client side, which has already abandoned the application session but hadn't let go of the database handle. Since the application is

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Andrew Dunstan
On 06/03/2014 05:53 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Out of curiosity, how much harder would it have been just to abort the transaction? I think breaking the connection is probably the right behavior, but before folks start arguing it out, I wanted to know if aborting

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Josh Berkus
On 06/03/2014 02:53 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Out of curiosity, how much harder would it have been just to abort the transaction? I think breaking the connection is probably the right behavior, but before folks start arguing it out, I wanted to know if

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Vik Fearing
On 06/04/2014 01:38 AM, Josh Berkus wrote: On 06/03/2014 02:53 PM, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: Out of curiosity, how much harder would it have been just to abort the transaction? I think breaking the connection is probably the right behavior, but before folks start

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread David G Johnston
On Tue, Jun 3, 2014 at 7:40 PM, Josh Berkus [via PostgreSQL] ml-node+s1045698n5805933...@n5.nabble.com wrote: On 06/03/2014 02:53 PM, Tom Lane wrote: Josh Berkus [hidden email] http://user/SendEmail.jtp?type=nodenode=5805933i=0 writes: Out of curiosity, how much harder would it have been

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Vik Fearing
On 06/04/2014 02:01 AM, David G Johnston wrote: ​Default to dropping the connection but give the usersadministrators the capability to decide for themselves? Meh. I still haven't heard an argument for why this wouldn't apply to aborted idle-in-transactions. I get the focus in on releasing

Re: [HACKERS] idle_in_transaction_timeout

2014-06-03 Thread Robert Haas
On Tue, Jun 3, 2014 at 5:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Out of curiosity, how much harder would it have been just to abort the transaction? I think breaking the connection is probably the right behavior, but before folks start arguing it out, I

  1   2   >