I'm currently trying to find a clean way to deal with network-dead clients that are in a transaction and holding locks etc.
The normal "client closes socket" case works fine. The scenario I'm worried about is when the client machine falls off the network entirely for some reason (ethernet problem, kernel panic, machine catches fire..). From what I can see, if the connection is idle at that point, the server won't notice this until TCP-level SO_KEEPALIVE kicks in, which by default takes over 2 hours on an idle connection. I'm looking for something more like a 30-60 second turnaround if the client is holding locks.
3) implement an idle timeout on the server so that open transactions that are idle for longer than some period are automatically aborted.
(3) seems like a proper solution. I've searched the archives a bit and transaction timeouts have been suggested before, but there seems to be some resistance to them.
Have you come across the pgpool connection-pooling project? http://pgpool.projects.postgresql.org/
Might be easier to put a timeout+disconnect in there.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
