are there ways for 'idle timeout'?

[Konstantin Pelepelin]

Hi!

In situations where client application is unstable there is very big
chance that it will enter transaction, acquire very restrictive locks and
then hang. This means: connection is not lost, but nothing happens and any
transaction in conflict with those locks will hang too.
statement_timeout can help to detect this situation, but not to solve,
when most of applications fail. Currently the only way I see to solve is
to kill locked postgres backend, or (more difficult) find hanging client
among hundreds of similar.
I work with web-application (Apache-mod_php-Postgres), where PHP hangs
every 100000 requests or so, which means from a day to some weeks between
whole system hangs.
I could set up a cron job which will kill every postgres backend "idle in
transaction" which was started more than 10 minutes ago, but I can have
accident problems with some long-running tasks.
The best solution I see is having an (per session)
idle_transaction_timeout or idle_connection_timeout setting.
But there is no such thing in postgres!
Scanning through list I've not found any workaround.
Looking into TODO I don't see any plans for it.
Is it too difficult implementing or I've missed something or are there
workarounds?

Hoping on your responce,
Konstantin Pelepelin

[mike g]

The best solution would be to fix the web app but....

Could you set the deadlock_timeout in postgresql.conf? According to
documentation that will kill one of the transactions that has issued the
lock on the table or row?

Post by Konstantin Pelepelin
Hi!
In situations where client application is unstable there is very big
chance that it will enter transaction, acquire very restrictive locks and
then hang. This means: connection is not lost, but nothing happens and any
transaction in conflict with those locks will hang too.
statement_timeout can help to detect this situation, but not to solve,
when most of applications fail. Currently the only way I see to solve is
to kill locked postgres backend, or (more difficult) find hanging client
among hundreds of similar.
I work with web-application (Apache-mod_php-Postgres), where PHP hangs
every 100000 requests or so, which means from a day to some weeks between
whole system hangs.
I could set up a cron job which will kill every postgres backend "idle in
transaction" which was started more than 10 minutes ago, but I can have
accident problems with some long-running tasks.
The best solution I see is having an (per session)
idle_transaction_timeout or idle_connection_timeout setting.
But there is no such thing in postgres!
Scanning through list I've not found any workaround.
Looking into TODO I don't see any plans for it.
Is it too difficult implementing or I've missed something or are there
workarounds?
Hoping on your responce,
Konstantin Pelepelin
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Konstantin Pelepelin]

Hi!

Post by mike g
The best solution would be to fix the web app but...

...but it's not a problem in my app, but in development
platform (PHP), which is quite buggy.

Post by mike g
Could you set the deadlock_timeout in postgresql.conf?

It is set. Alas, it doesn't help, because there is no
deadlock condition. (Deadlock means two transactions cannot
continue because each tries to set lock conflicting with
already acquired by another, mutually.)

Thank you,
Konstantin Pelepelin

Post by mike g

Post by Konstantin Pelepelin
In situations where client application is unstable
there is very big chance that it will enter
transaction, acquire very restrictive locks and then
hang. This means: connection is not lost, but nothing
happens and any transaction in conflict with those
locks will hang too. statement_timeout can help to
detect this situation, but not to solve, when most of
applications fail. Currently the only way I see to
solve is to kill locked postgres backend, or (more
difficult) find hanging client among hundreds of
similar.
I work with web-application (Apache-mod_php-Postgres),
where PHP hangs every 100000 requests or so, which
means from a day to some weeks between whole system
hangs.
I could set up a cron job which will kill every
postgres backend "idle in transaction" which was
started more than 10 minutes ago, but I can have
accident problems with some long-running tasks. The
best solution I see is having an (per session)
idle_transaction_timeout or idle_connection_timeout
setting. But there is no such thing in postgres!
Scanning through list I've not found any workaround.
Looking into TODO I don't see any plans for it.
Is it too difficult implementing or I've missed
something or are there workarounds?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Simon Riggs]

The

Post by Konstantin Pelepelin

Post by Konstantin Pelepelin
best solution I see is having an (per session)
idle_transaction_timeout or idle_connection_timeout
setting. But there is no such thing in postgres!
Scanning through list I've not found any workaround.

Have you looked at statement_timeout?
This doesn't do exactly what you want, but is close enough.

If you don't have it at your release, upgrade.

Post by Konstantin Pelepelin
Looking into TODO I don't see any plans for it.

Post by Konstantin Pelepelin
Is it too difficult implementing or I've missed
something or are there workarounds?

This might be something you could do...

Best regards, Simon Riggs


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Konstantin Pelepelin]

Post by Simon Riggs

Post by Konstantin Pelepelin

Post by Konstantin Pelepelin
best solution I see is having an (per session)
idle_transaction_timeout or idle_connection_timeout
setting. But there is no such thing in postgres!

Have you looked at statement_timeout?
This doesn't do exactly what you want, but is close enough.

'statement_timeout' will abort statements locked by "locking idle
transaction", but I need to abort "locking idle transaction" itself.

Post by Simon Riggs

Post by Konstantin Pelepelin
Looking into TODO I don't see any plans for it.

Post by Konstantin Pelepelin
Is it too difficult implementing or I've missed
something or are there workarounds?

This might be something you could do...

If I'd know C and Postgres internals.
(Then there is no assurance that developers will say "Yes, it's good". And
I'm not sure it is easy.)

For now, I need to create some additional monitoring tool and inegrate it
with all our applications.

*Very disappointed*

Konstantin