Oliver Jowett wrote:
> 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