Feature thought: idle in transaction timeout

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.

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.

---------------------------------------------------------------------------

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. +

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. +

"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