are there ways for 'idle timeout'?

Поиск
Список
Период
Сортировка
Искать

are there ways for 'idle timeout'?

От:
"Konstantin Pelepelin" <checat@pochtamt.ru>
Дата:
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

Re: are there ways for 'idle timeout'?

От:
mike g <mike@thegodshalls.com>
Дата:
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?


On Fri, 2004-07-09 at 09:36, Konstantin Pelepelin wrote:
> 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

Re: are there ways for 'idle timeout'?

От:
Konstantin Pelepelin <checat@pochtamt.ru>
Дата:
Hi!

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


> 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


> > 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?

Re: are there ways for 'idle timeout'?

От:
Simon Riggs <simon@2ndquadrant.com>
Дата:
On Mon, 2004-07-12 at 10:26, Konstantin Pelepelin wrote:

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

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.

> > 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?

This might be something you could do...

Best regards, Simon Riggs

Re: are there ways for 'idle timeout'?

От:
"Konstantin Pelepelin" <checat@pochtamt.ru>
Дата:
On Tue, 13 Jul 2004 11:31:37 +0100, Simon Riggs   
wrote:

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


>> > 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?
> 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
FAQ