Yes you can minimize risk by design of your application, but you can never
guarantee that your connection won't be broken in the middle of a
transaction, so I thing that there should be a method how to find blocking
backend (transaction).
Vak
On Wed, 10 Apr 2002, Martijn van Oosterhout wrote:
> On Wed, Apr 10, 2002 at 10:20:08AM +0200, Vaclav Kulakovsky wrote:
> >
> > Hi all,
> >
> > I've the following problem:
> >
> > We have client server application. Some clients are connecter via dialup
> > modem, so theirs connections are not so stable. Sometimes happens that
> > dialup connection hangs and if it is in the middle of a transaction tables
> > (rows) stay locked.
> >
> > I'm tunneling postgres through secure tunnel, so I set up keep alive on
> > that connection. If connection is really broken backend is killed by keep
> > alive packet and locks are released. But this doesn't solve my problem
> > entirely. Sometimes when client hangs (it's on M$ windows so it is normal)
> > and a user is just having coffee, connection is ok so tables/rows stay
> > locked.
>
> This is an application problem. You should write your application so that it
> does not need to hold the transaction for more than a few seconds. Surely
> killing the hung client will kill off the connection?
>
> > Problem is that I'm not able to get rid of these "dead" backends, even I
> > don't know method how to find them.
>
> The implication is that you can kill these backends. Killing them will
> rollback any changes they've made. If this doesn't bother you, why are the
> applications in a transaction at all.
>
> > Question:
> >
> > Is there any general advice how to solve/avoid this problem ?
> >
> > Is there any options how to set keep alive option on PG connections ?
> >
> > Is possible to find backend which is blocking ?
>
> You need to avoid the problem completely. Look at your system and decide
> what needs to be changed. What exactly are you trying to acheive?
>
> HTH,
> --
> Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> > Ignorance continues to thrive when intelligent people choose to do
> > nothing. Speaking out against censorship and ignorance is the imperative
> > of all intelligent people.
>