Idle In Transaction Session Timeout, revived
Attached is a rebased and revised version of my
idle_in_transaction_session_timeout patch from last year.
This version does not suffer the problems the old one did where it would
jump out of SSL code thanks to Andres' patch in commit
4f85fde8eb860f263384fffdca660e16e77c7f76.
The basic idea is if a session remains idle in a transaction for longer
than the configured time, that connection will be dropped thus releasing
the connection slot and any locks that may have been held by the broken
client.
Added to the March commitfest.
--
Vik Fearing +33 6 46 75 15 36
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
Attachments:
iitt.v4.patchtext/x-patch; name=iitt.v4.patchDownload+71-3
On Sun, Jan 31, 2016 at 8:33 AM, Vik Fearing <vik@2ndquadrant.fr> wrote:
Attached is a rebased and revised version of my
idle_in_transaction_session_timeout patch from last year.This version does not suffer the problems the old one did where it would
jump out of SSL code thanks to Andres' patch in commit
4f85fde8eb860f263384fffdca660e16e77c7f76.The basic idea is if a session remains idle in a transaction for longer
than the configured time, that connection will be dropped thus releasing
the connection slot and any locks that may have been held by the broken
client.Added to the March commitfest.
+1 for doing something like this. Great idea!
--
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
On 2/3/16 2:30 PM, Robert Haas wrote:
On Sun, Jan 31, 2016 at 8:33 AM, Vik Fearing <vik@2ndquadrant.fr> wrote:
Attached is a rebased and revised version of my
idle_in_transaction_session_timeout patch from last year.This version does not suffer the problems the old one did where it would
jump out of SSL code thanks to Andres' patch in commit
4f85fde8eb860f263384fffdca660e16e77c7f76.The basic idea is if a session remains idle in a transaction for longer
than the configured time, that connection will be dropped thus releasing
the connection slot and any locks that may have been held by the broken
client.Added to the March commitfest.
+1 for doing something like this. Great idea!
Wouldn't it be more sensible to just roll the transaction back and not
disconnect?
--
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
On Wed, Feb 3, 2016 at 3:41 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
On 2/3/16 2:30 PM, Robert Haas wrote:
On Sun, Jan 31, 2016 at 8:33 AM, Vik Fearing <vik@2ndquadrant.fr> wrote:
Attached is a rebased and revised version of my
idle_in_transaction_session_timeout patch from last year.This version does not suffer the problems the old one did where it would
jump out of SSL code thanks to Andres' patch in commit
4f85fde8eb860f263384fffdca660e16e77c7f76.The basic idea is if a session remains idle in a transaction for longer
than the configured time, that connection will be dropped thus releasing
the connection slot and any locks that may have been held by the broken
client.Added to the March commitfest.
+1 for doing something like this. Great idea!
Wouldn't it be more sensible to just roll the transaction back and not
disconnect?
It would be nice to be able to do that, but the client-server protocol
can't handle it without losing sync. Basically, if you send an error
to an idle client, you have to kill the session. This has come up
many times before.
--
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
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 3, 2016 at 3:41 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
Wouldn't it be more sensible to just roll the transaction back and not
disconnect?
It would be nice to be able to do that, but the client-server protocol
can't handle it without losing sync. Basically, if you send an error
to an idle client, you have to kill the session. This has come up
many times before.
Well, you can't just spit out an unprompted error message and go back to
waiting for the next command; as Robert says, that would leave the wire
protocol state out of sync. But in principle we could kill the
transaction and not say anything to the client right then. Instead set
some state that causes the next command from the client to get an error.
(This would not be much different from what happens when you send a
command in an already-reported-failed transaction; though we'd want to
issue a different error message than for that case.)
I'm not sure how messy this would be in practice. But if we think that
killing the whole session is not desirable but something we're doing for
expediency, then it would be worth looking into that approach.
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
On 2/3/16 4:25 PM, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 3, 2016 at 3:41 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
Wouldn't it be more sensible to just roll the transaction back and not
disconnect?I'm not sure how messy this would be in practice. But if we think that
killing the whole session is not desirable but something we're doing for
expediency, then it would be worth looking into that approach.
I think killing the session is a perfectly sensible thing to do in this
case. Everything meaningful that was done in the session will be rolled
back - no need to waste resources keeping the connection open.
--
-David
david@pgmasters.net
On 2/3/16 4:05 PM, David Steele wrote:
On 2/3/16 4:25 PM, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 3, 2016 at 3:41 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
Wouldn't it be more sensible to just roll the transaction back and not
disconnect?I'm not sure how messy this would be in practice. But if we think that
killing the whole session is not desirable but something we're doing for
expediency, then it would be worth looking into that approach.I think killing the session is a perfectly sensible thing to do in this
case. Everything meaningful that was done in the session will be rolled
back - no need to waste resources keeping the connection open.
Except you end up losing stuff like every GUC you've set, existing temp
tables, etc. For an application that presumably doesn't matter, but for
a user connection it would be a PITA.
I wouldn't put a bunch of effort into it though. Dropping the connection
is certainly better than nothing.
--
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
On 02/03/2016 11:36 PM, Jim Nasby wrote:
On 2/3/16 4:05 PM, David Steele wrote:
On 2/3/16 4:25 PM, Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Wed, Feb 3, 2016 at 3:41 PM, Jim Nasby <Jim.Nasby@bluetreble.com>
wrote:Wouldn't it be more sensible to just roll the transaction back and not
disconnect?I'm not sure how messy this would be in practice. But if we think that
killing the whole session is not desirable but something we're doing for
expediency, then it would be worth looking into that approach.I think killing the session is a perfectly sensible thing to do in this
case. Everything meaningful that was done in the session will be rolled
back - no need to waste resources keeping the connection open.
That was the consensus last time I presented this bikeshed for painting.
Except you end up losing stuff like every GUC you've set, existing temp
tables, etc. For an application that presumably doesn't matter, but for
a user connection it would be a PITA.I wouldn't put a bunch of effort into it though. Dropping the connection
is certainly better than nothing.
You could always put SET idle_in_transaction_session_timeout = 0; in
your .psqlrc file to exempt your manual sessions from it. Or change it
just for your user or something.
--
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
On Wed, Feb 3, 2016 at 5:36 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
I think killing the session is a perfectly sensible thing to do in this
case. Everything meaningful that was done in the session will be rolled
back - no need to waste resources keeping the connection open.Except you end up losing stuff like every GUC you've set, existing temp
tables, etc. For an application that presumably doesn't matter, but for a
user connection it would be a PITA.I wouldn't put a bunch of effort into it though. Dropping the connection is
certainly better than nothing.
Well, my view is that if somebody wants an alternative behavior
besides dropping the connection, they can write a patch to provide
that as an additional option. That, too, has been discussed before.
But the fact that somebody might want that doesn't make this a bad or
useless behavior. Indeed, I'd venture that more people would want
this than would want that.
--
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
On 02/03/2016 02:52 PM, Robert Haas wrote:
On Wed, Feb 3, 2016 at 5:36 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
I think killing the session is a perfectly sensible thing to do in this
case. Everything meaningful that was done in the session will be rolled
back - no need to waste resources keeping the connection open.Except you end up losing stuff like every GUC you've set, existing temp
tables, etc. For an application that presumably doesn't matter, but for a
user connection it would be a PITA.I wouldn't put a bunch of effort into it though. Dropping the connection is
certainly better than nothing.Well, my view is that if somebody wants an alternative behavior
besides dropping the connection, they can write a patch to provide
that as an additional option. That, too, has been discussed before.
But the fact that somebody might want that doesn't make this a bad or
useless behavior. Indeed, I'd venture that more people would want
this than would want that.
Something feels wrong about just dropping the connection. I can see
doing what connection poolers do (DISCARD ALL) + a rollback but the idea
that we are going to destroy a connection to the database due to an idle
transaction seems like a potential foot gun. Unfortunately, outside of a
feeling I can not provide a good example.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"Joshua D. Drake" <jd@commandprompt.com> writes:
On 02/03/2016 02:52 PM, Robert Haas wrote:
Well, my view is that if somebody wants an alternative behavior
besides dropping the connection, they can write a patch to provide
that as an additional option. That, too, has been discussed before.
But the fact that somebody might want that doesn't make this a bad or
useless behavior. Indeed, I'd venture that more people would want
this than would want that.
Something feels wrong about just dropping the connection.
I have the same uneasy feeling about it as JD. However, you could
certainly argue that if the client application has lost its marbles
to the extent of allowing a transaction to time out, there's no good
reason to suppose that it will wake up any time soon, and then we are
definitely wasting resources by letting it monopolize a backend. Not as
many resources as if the xact were still open, but waste none the less.
My design sketch wherein we do nothing to notify the client certainly
doesn't do anything to help the client wake up, either. So maybe it's
fine and we should just go forward with the kill-the-connection approach.
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
On Wed, Feb 3, 2016 at 6:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
On 02/03/2016 02:52 PM, Robert Haas wrote:
Well, my view is that if somebody wants an alternative behavior
besides dropping the connection, they can write a patch to provide
that as an additional option. That, too, has been discussed before.
But the fact that somebody might want that doesn't make this a bad or
useless behavior. Indeed, I'd venture that more people would want
this than would want that.Something feels wrong about just dropping the connection.
I have the same uneasy feeling about it as JD. However, you could
certainly argue that if the client application has lost its marbles
to the extent of allowing a transaction to time out, there's no good
reason to suppose that it will wake up any time soon, ...
That's exactly what I think. If you imagine a user who starts a
transaction and then leaves for lunch, aborting the transaction seems
nicer than killing the connection. But what I think really happens is
some badly-written Java application loses track of a connection
someplace and just never finds it again. Again, I'm not averse to
having both behavior someday, but my gut feeling is that killing the
connection will be the more useful one.
--
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
On 2/3/16 8:04 PM, Robert Haas wrote:
On Wed, Feb 3, 2016 at 6:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Joshua D. Drake" <jd@commandprompt.com> writes:
On 02/03/2016 02:52 PM, Robert Haas wrote:
Well, my view is that if somebody wants an alternative behavior
besides dropping the connection, they can write a patch to provide
that as an additional option. That, too, has been discussed before.
But the fact that somebody might want that doesn't make this a bad or
useless behavior. Indeed, I'd venture that more people would want
this than would want that.Something feels wrong about just dropping the connection.
I have the same uneasy feeling about it as JD. However, you could
certainly argue that if the client application has lost its marbles
to the extent of allowing a transaction to time out, there's no good
reason to suppose that it will wake up any time soon, ...<...> But what I think really happens is
some badly-written Java application loses track of a connection
someplace and just never finds it again. <...>
That's what I've seen over and over again. And then sometimes it's not
a badly-written Java application, but me, and in that case I definitely
want the connection killed. Without logging, if you please.
--
-David
david@pgmasters.net
David Steele wrote:
<...> But what I think really happens is
some badly-written Java application loses track of a connection
someplace and just never finds it again. <...>
I've seen that also, plenty of times.
That's what I've seen over and over again. And then sometimes it's not
a badly-written Java application, but me, and in that case I definitely
want the connection killed. Without logging, if you please.
So the way to escape audit logging is to open a transaction, steal some
data, then leave the connection open so that it's not logged when it's
killed?
--
�lvaro Herrera http://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
On Sun, Jan 31, 2016 at 10:33 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:
Attached is a rebased and revised version of my
idle_in_transaction_session_timeout patch from last year.This version does not suffer the problems the old one did where it would
jump out of SSL code thanks to Andres' patch in commit
4f85fde8eb860f263384fffdca660e16e77c7f76.The basic idea is if a session remains idle in a transaction for longer
than the configured time, that connection will be dropped thus releasing
the connection slot and any locks that may have been held by the broken
client.
+1
But, IIRC, one of the problems that prevent the adoption of this feature is
the addition of gettimeofday() call after every SQL command receipt.
Have you already resolved that problem? Or we don't need to care about
it because it's almost harmless?
Regards,
--
Fujii Masao
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 02/04/2016 02:24 PM, Fujii Masao wrote:
On Sun, Jan 31, 2016 at 10:33 PM, Vik Fearing <vik@2ndquadrant.fr> wrote:
Attached is a rebased and revised version of my
idle_in_transaction_session_timeout patch from last year.This version does not suffer the problems the old one did where it would
jump out of SSL code thanks to Andres' patch in commit
4f85fde8eb860f263384fffdca660e16e77c7f76.The basic idea is if a session remains idle in a transaction for longer
than the configured time, that connection will be dropped thus releasing
the connection slot and any locks that may have been held by the broken
client.+1
But, IIRC, one of the problems that prevent the adoption of this feature is
the addition of gettimeofday() call after every SQL command receipt.
Have you already resolved that problem? Or we don't need to care about
it because it's almost harmless?
I guess it would be possible to look at MyBEEntry somehow and pull
st_state_start_timestamp from it to replace the call to
GetCurrentTimestamp(), but I don't know if it's worth doing that.
The extra call only happens if the timeout is enabled anyway, so I don't
think it matters enough to be a blocker.
--
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
On 2/4/16 5:00 AM, Alvaro Herrera wrote:
David Steele wrote:
<...> But what I think really happens is
some badly-written Java application loses track of a connection
someplace and just never finds it again. <...>I've seen that also, plenty of times.
That's what I've seen over and over again. And then sometimes it's not
a badly-written Java application, but me, and in that case I definitely
want the connection killed. Without logging, if you please.So the way to escape audit logging is to open a transaction, steal some
data, then leave the connection open so that it's not logged when it's
killed?
Well, of course I was joking, but even so I only meant the disconnect
shouldn't be logged to save me embarrassment.
But you are probably joking as well. Oh, what a tangled web.
--
-David
david@pgmasters.net
On 2016-02-04 22:24:50 +0900, Fujii Masao wrote:
But, IIRC, one of the problems that prevent the adoption of this feature is
the addition of gettimeofday() call after every SQL command receipt.
Have you already resolved that problem? Or we don't need to care about
it because it's almost harmless?
Well, it only happens when the feature is enabled, not
unconditionally. So I don't think that's particularly bad, as long as
the feature is not enabled by default.
If we feel we need to something about the gettimeofday() call at some
point, I think it'd made a lot of sense to introduce a more centralize
"statement stop" time, and an extended pgstat_report_activity() that
allows to specify the timestamp. Because right now we'll essentially do
gettimeofday() calls successively when starting a command, starting a
transaction, committing a transaction, and finishing a comment. That's
pretty pointless.
Andres
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 31/01/2016 14:33, Vik Fearing wrote:
Attached is a rebased and revised version of my
idle_in_transaction_session_timeout patch from last year.This version does not suffer the problems the old one did where it would
jump out of SSL code thanks to Andres' patch in commit
4f85fde8eb860f263384fffdca660e16e77c7f76.The basic idea is if a session remains idle in a transaction for longer
than the configured time, that connection will be dropped thus releasing
the connection slot and any locks that may have been held by the broken
client.Added to the March commitfest.
Hello,
I've looked at this patch, which I'd be able to review as a user, probably not
at a code level.
It seems to me this is a need in a huge number of badly handled idle in
transaction sessions (at application level).
This feature works as I expected it to.
My question would be regarding the value 0 assigned to the GUC parameter to
disable it. Wouldn't be -1 a better value, similar to
log_min_duration_statement or similar GUC parameter?
(I understand you can't put a 0ms timeout duration, but -1 seems more
understandable).
Best regards,
--
Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
Loxodata - Conseil, expertise et formations
06.17.11.37.42
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 4 February 2016 at 09:04, Robert Haas <robertmhaas@gmail.com> wrote:
I have the same uneasy feeling about it as JD. However, you could
certainly argue that if the client application has lost its marbles
to the extent of allowing a transaction to time out, there's no good
reason to suppose that it will wake up any time soon, ...That's exactly what I think. If you imagine a user who starts a
transaction and then leaves for lunch, aborting the transaction seems
nicer than killing the connection. But what I think really happens is
some badly-written Java application loses track of a connection
someplace and just never finds it again. Again, I'm not averse to
having both behavior someday, but my gut feeling is that killing the
connection will be the more useful one.
Applications - and users - must be prepared for the fact that uncommitted
data and session state may be lost at any time. The fact that PostgreSQL
tries not to lose it is quite nice, but gives people a false sense of
security too. Someone trips over a cable, a carrier has a bit of a BGP
hiccup, a NAT conntrack timeout occurs, there's an ECC parity check error
causing a proc kill ... your state can go away.
If you really don't want your session terminated, don't set an idle in
transaction session idle timeout (or override it).
(In some ways I think we're too good at this; I really should write an
extension that randomly aborts some low percentage of xacts with fake
deadlocks or serialization failures and randomly kills occasional
connections so that apps actually use their retry paths...)
Sure, it'd be *nice* to just terminate the xact and have a separate param
for timing out idle sessions whether or not they're in an xact. Cleaner -
terminate the xact if there's an xact-related timeout, terminate the
session if there's a session-related timeout. But nobody's written that
patch and this proposal solves a real world problem well enough.
Terminating the xact without terminating the session is a little tricky as
noted earlier so it's not a simple change to switch to that.
I'd be happy to have this. I won't mind having it if we eventually add an
idle_xact_timeout and idle_session_timeout in 9.something too.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
