Thread: Feature thought: idle in transaction timeout
Hello,
I ran into an interesting problem with a customer today. They are
running Jabber XCP (not the one we use). Unfortunately, the product has
a bug that causes it to leave connections persistent in a transaction
state. This is what it does:
BEGIN; SELECT 1;
Basically it is verifying that the connection is live. However, it never
calls commit. So what happens? We can't vacuum ;).
Anyway, my thought is, we know when a transaction is idle, why not have
an idle timeout where we will explicitly close the connection or
rollback or something? User configurable of course.
Sincerely,
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Joshua D. Drake wrote: > Hello, > > I ran into an interesting problem with a customer today. They are > running Jabber XCP (not the one we use). Unfortunately, the product > has a bug that causes it to leave connections persistent in a > transaction state. This is what it does: > > BEGIN; SELECT 1; > > Basically it is verifying that the connection is live. However, it > never calls commit. So what happens? We can't vacuum ;). > > Anyway, my thought is, we know when a transaction is idle, why not > have an idle timeout where we will explicitly close the connection or > rollback or something? User configurable of course. I agree with this, it reduces the long running transaction problem a little where the user forgot to commit/rollback their session. I may be worth having a transaction_timeout as well, and setting it to link a few hours by default. That way you can't have really long running transactions unless you specifically set that. > > Sincerely, > > Joshua D. Drake > > >
Russell Smith wrote:
> Joshua D. Drake wrote:
>> Hello,
>>
>> I ran into an interesting problem with a customer today. They are
>> running Jabber XCP (not the one we use). Unfortunately, the product
>> has a bug that causes it to leave connections persistent in a
>> transaction state. This is what it does:
>>
>> BEGIN; SELECT 1;
>>
>> Basically it is verifying that the connection is live. However, it
>> never calls commit. So what happens? We can't vacuum ;).
>>
>> Anyway, my thought is, we know when a transaction is idle, why not
>> have an idle timeout where we will explicitly close the connection or
>> rollback or something? User configurable of course.
> I agree with this, it reduces the long running transaction problem a
> little where the user forgot to commit/rollback their session. I may be
> worth having a transaction_timeout as well, and setting it to link a few
> hours by default. That way you can't have really long running
> transactions unless you specifically set that.
We would certainly need to be able to disable on the fly too just with
SET as well.
Joshua D. Drake
>>
>> Sincerely,
>>
>> Joshua D. Drake
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Russell Smith wrote:
>> I agree with this, it reduces the long running transaction problem a
>> little where the user forgot to commit/rollback their session. I may be
>> worth having a transaction_timeout as well, and setting it to link a few
>> hours by default. That way you can't have really long running
>> transactions unless you specifically set that.
> We would certainly need to be able to disable on the fly too just with
> SET as well.
AFAICS, a *transaction* timeout per se has no use whatever except as a
foot-gun. How will you feel when you start a 12-hour restore, go home
for the evening, and come back in the morning to find it aborted because
you forgot to disable your 4-hour timeout?
Furthermore, if you have to set transaction_timeout to multiple hours
in the (vain) hope of not killing something important, what use is it
really? If you want to keep VACUUM able to work in a busy database,
you need it to be a lot less than that.
An *idle* timeout seems less risky, as well as much easier to pick a
sane value for.
regards, tom lane
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> Russell Smith wrote: >>> I agree with this, it reduces the long running transaction problem a >>> little where the user forgot to commit/rollback their session. I may be >>> worth having a transaction_timeout as well, and setting it to link a few >>> hours by default. That way you can't have really long running >>> transactions unless you specifically set that. > >> We would certainly need to be able to disable on the fly too just with >> SET as well. > > AFAICS, a *transaction* timeout per se has no use whatever except as a > foot-gun. How will you feel when you start a 12-hour restore, go home > for the evening, and come back in the morning to find it aborted because > you forgot to disable your 4-hour timeout? Well of course that would be bad. That is why I said, idle in transaction. If you are idle, you are doing nothing yes? Joshua D. Drake > > Furthermore, if you have to set transaction_timeout to multiple hours > in the (vain) hope of not killing something important, what use is it > really? If you want to keep VACUUM able to work in a busy database, > you need it to be a lot less than that. > > An *idle* timeout seems less risky, as well as much easier to pick a > sane value for. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> Russell Smith wrote: >>> I agree with this, it reduces the long running transaction problem a >>> little where the user forgot to commit/rollback their session. I may be >>> worth having a transaction_timeout as well, and setting it to link a few >>> hours by default. That way you can't have really long running >>> transactions unless you specifically set that. > >> We would certainly need to be able to disable on the fly too just with >> SET as well. I should have read what you posted more thoroughly. I apologize. A transaction timeout is surely a bad idea as Tom says below. Heck, not just from what he says below, but what about the scenario that killing a transaction could cause a massive rollback and thus increase the initial problem that I posted about :) > An *idle* timeout seems less risky, as well as much easier to pick a > sane value for. Yeah, it could as high as something like 60 minutes if we really wanted. Sincerely, Joshua D. Drake > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Added to TODO: * Add idle_timeout GUC so locks are not held for log periods of time --------------------------------------------------------------------------- Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: > > Russell Smith wrote: > >> I agree with this, it reduces the long running transaction problem a > >> little where the user forgot to commit/rollback their session. I may be > >> worth having a transaction_timeout as well, and setting it to link a few > >> hours by default. That way you can't have really long running > >> transactions unless you specifically set that. > > > We would certainly need to be able to disable on the fly too just with > > SET as well. > > AFAICS, a *transaction* timeout per se has no use whatever except as a > foot-gun. How will you feel when you start a 12-hour restore, go home > for the evening, and come back in the morning to find it aborted because > you forgot to disable your 4-hour timeout? > > Furthermore, if you have to set transaction_timeout to multiple hours > in the (vain) hope of not killing something important, what use is it > really? If you want to keep VACUUM able to work in a busy database, > you need it to be a lot less than that. > > An *idle* timeout seems less risky, as well as much easier to pick a > sane value for. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Added to TODO: > > * Add idle_timeout GUC so locks are not held for log periods of time > That should actually be transaction_idle_timeout. It is o.k. for us to be IDLE... it is not o.k. for us to be IDLE in Transaction Joshua D. Drake > > --------------------------------------------------------------------------- > > Tom Lane wrote: >> "Joshua D. Drake" <jd@commandprompt.com> writes: >>> Russell Smith wrote: >>>> I agree with this, it reduces the long running transaction problem a >>>> little where the user forgot to commit/rollback their session. I may be >>>> worth having a transaction_timeout as well, and setting it to link a few >>>> hours by default. That way you can't have really long running >>>> transactions unless you specifically set that. >>> We would certainly need to be able to disable on the fly too just with >>> SET as well. >> AFAICS, a *transaction* timeout per se has no use whatever except as a >> foot-gun. How will you feel when you start a 12-hour restore, go home >> for the evening, and come back in the morning to find it aborted because >> you forgot to disable your 4-hour timeout? >> >> Furthermore, if you have to set transaction_timeout to multiple hours >> in the (vain) hope of not killing something important, what use is it >> really? If you want to keep VACUUM able to work in a busy database, >> you need it to be a lot less than that. >> >> An *idle* timeout seems less risky, as well as much easier to pick a >> sane value for. >> >> regards, tom lane >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 7: You can help support the PostgreSQL project by donating at >> >> http://www.postgresql.org/about/donate > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Bruce Momjian wrote: > Added to TODO: > > * Add idle_timeout GUC so locks are not held for log periods of time > > > ITYM long periods. cheers andrew
fixed. --------------------------------------------------------------------------- Joshua D. Drake wrote: > Bruce Momjian wrote: > > Added to TODO: > > > > * Add idle_timeout GUC so locks are not held for log periods of time > > > > That should actually be transaction_idle_timeout. It is o.k. for us to > be IDLE... it is not o.k. for us to be IDLE in Transaction > > > Joshua D. Drake > > > > > > > --------------------------------------------------------------------------- > > > > Tom Lane wrote: > >> "Joshua D. Drake" <jd@commandprompt.com> writes: > >>> Russell Smith wrote: > >>>> I agree with this, it reduces the long running transaction problem a > >>>> little where the user forgot to commit/rollback their session. I may be > >>>> worth having a transaction_timeout as well, and setting it to link a few > >>>> hours by default. That way you can't have really long running > >>>> transactions unless you specifically set that. > >>> We would certainly need to be able to disable on the fly too just with > >>> SET as well. > >> AFAICS, a *transaction* timeout per se has no use whatever except as a > >> foot-gun. How will you feel when you start a 12-hour restore, go home > >> for the evening, and come back in the morning to find it aborted because > >> you forgot to disable your 4-hour timeout? > >> > >> Furthermore, if you have to set transaction_timeout to multiple hours > >> in the (vain) hope of not killing something important, what use is it > >> really? If you want to keep VACUUM able to work in a busy database, > >> you need it to be a lot less than that. > >> > >> An *idle* timeout seems less risky, as well as much easier to pick a > >> sane value for. > >> > >> regards, tom lane > >> > >> ---------------------------(end of broadcast)--------------------------- > >> TIP 7: You can help support the PostgreSQL project by donating at > >> > >> http://www.postgresql.org/about/donate > > > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL Replication: http://www.commandprompt.com/products/ -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Fixed. --------------------------------------------------------------------------- Andrew Dunstan wrote: > Bruce Momjian wrote: > > Added to TODO: > > > > * Add idle_timeout GUC so locks are not held for log periods of time > > > > > > > > ITYM long periods. > > > cheers > > > andrew -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Bruce Momjian wrote:
>> Added to TODO:
>> * Add idle_timeout GUC so locks are not held for log periods of time
> That should actually be transaction_idle_timeout. It is o.k. for us to
> be IDLE... it is not o.k. for us to be IDLE in Transaction
Or "idle_in_transaction_timeout"? Anyway I agree that using
"idle_timeout" for this is unwise. We've been asked often enough for a
flat-out idle timeout (ie kill session after X seconds of no client
interaction), and while I disagree with the concept, someday we might
cave and implement it. We should reserve the name for the behavior
that people would expect a parameter named like that to have.
regards, tom lane
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Bruce Momjian wrote:
>>> Added to TODO:
>>> * Add idle_timeout GUC so locks are not held for log periods of time
>
>> That should actually be transaction_idle_timeout. It is o.k. for us to
>> be IDLE... it is not o.k. for us to be IDLE in Transaction
>
> Or "idle_in_transaction_timeout"?
Yeah that would work and it is what I originally typed before
backspacing. I was trying to avoid the _in_ but either way.
> Anyway I agree that using
> "idle_timeout" for this is unwise. We've been asked often enough for a
> flat-out idle timeout (ie kill session after X seconds of no client
> interaction), and while I disagree with the concept, someday we might
Well I agree that we shouldn't kill sessions just because they are idle,
I can imagine all the lovely... my pgpool sessions keep getting killed!
comments.
> cave and implement it. We should reserve the name for the behavior
> that people would expect a parameter named like that to have.
Agreed.
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Bruce Momjian <bruce@momjian.us> writes:
> Added to TODO:
> * Add idle_timeout GUC so locks are not held for log periods of time
BTW, before I forget it: there's a non-obvious consideration here, which
is not breaking the query protocol. I suspect that we cannot send an
unsolicited ERROR message without getting out-of-sync with the client,
which will likely take the error as the response to its next command
and thenceforth be very confused. What we'll probably have to do to
make this work is abort the transaction upon timeout (so that VACUUM et
al can get on with things) but not report the error to the client until
its next command. And if said next command happens to be ROLLBACK then
there's nothing to complain of at all.
Doable, probably, but seems a bit messy.
regards, tom lane
PS: the only case where we currently send an unsolicited ERROR is during
SIGTERM or SIGQUIT shutdown; where it doesn't matter if we're out of
sync because we're killing the session anyway.
TODO updated: * Add idle_in_transaction_timeout GUC so locks are not held for long periods of time --------------------------------------------------------------------------- Joshua D. Drake wrote: > Tom Lane wrote: > > "Joshua D. Drake" <jd@commandprompt.com> writes: > >> Bruce Momjian wrote: > >>> Added to TODO: > >>> * Add idle_timeout GUC so locks are not held for log periods of time > > > >> That should actually be transaction_idle_timeout. It is o.k. for us to > >> be IDLE... it is not o.k. for us to be IDLE in Transaction > > > > Or "idle_in_transaction_timeout"? > > Yeah that would work and it is what I originally typed before > backspacing. I was trying to avoid the _in_ but either way. > > > Anyway I agree that using > > "idle_timeout" for this is unwise. We've been asked often enough for a > > flat-out idle timeout (ie kill session after X seconds of no client > > interaction), and while I disagree with the concept, someday we might > > Well I agree that we shouldn't kill sessions just because they are idle, > I can imagine all the lovely... my pgpool sessions keep getting killed! > comments. > > > cave and implement it. We should reserve the name for the behavior > > that people would expect a parameter named like that to have. > > Agreed. > > Sincerely, > > Joshua D. Drake > > > > > regards, tom lane > > > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL Replication: http://www.commandprompt.com/products/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Ühel kenal päeval, E, 2007-04-02 kell 19:36, kirjutas Joshua D. Drake: > Tom Lane wrote: > > "Joshua D. Drake" <jd@commandprompt.com> writes: > >> Bruce Momjian wrote: > >>> Added to TODO: > >>> * Add idle_timeout GUC so locks are not held for log periods of time > > > >> That should actually be transaction_idle_timeout. It is o.k. for us to > >> be IDLE... it is not o.k. for us to be IDLE in Transaction > > > > Or "idle_in_transaction_timeout"? > > Yeah that would work and it is what I originally typed before > backspacing. I was trying to avoid the _in_ but either way. > > > Anyway I agree that using > > "idle_timeout" for this is unwise. We've been asked often enough for a > > flat-out idle timeout (ie kill session after X seconds of no client > > interaction), and while I disagree with the concept, someday we might > > Well I agree that we shouldn't kill sessions just because they are idle, As the projects to have some out-of-band capabilities in pg wire protocol which could be used to implement keepalives seem to be going nowhere, having an idle_session_timeout to have a at least some protection against server not noticing that client has left (due to network problems for example) may still be a good thing. At least it beats running psql -c "select 'kill '||procpid from pg_stat_activity where current_query = '<IDLE>' and current_timestamp - query_start > '00:01:00';" | bash from postgres users cron each minute to kill stale connections idle_session_timeout is something that should be off by default and would be used only in OLTP production environments where not noticing stale connections can lead to exhausting connection pool by reconnecting clients. > I can imagine all the lovely... my pgpool sessions keep getting killed! > comments. pgpool could do 'select 1' often enough to keep timeout from happening; > > cave and implement it. We should reserve the name for the behavior > > that people would expect a parameter named like that to have. > > Agreed. > > Sincerely, > > Joshua D. Drake > > > > > regards, tom lane > > > > -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com