Re: Socket timeouts (probably)

Sergei Georgiev wrote:

> I have a really strange problem here. I've got two Fedora 2 machines -
> one for the database and one for a Java application to be run. When a
> transaction from the application is run against the database and the
> network cable is unplugged (or the machine is shut down) the database
> continues to hold the lock on the tables, which are used in the
> transaction. The lock is released in about 10-12 minutes, which in my
> case is not acceptable, because if another client starts a transaction
> against these locked tables his UI ties his hands until the lock on
> the tables is released and the transaction is complete.

I raised this on -hackers recently. Ideally I'd like to see a
transaction timeout implemented on the server side for exactly this
case. See
http://archives.postgresql.org/pgsql-hackers/2005-02/msg00397.php (the
archive indexes seem to be broken at the moment, but the direct link works).

You may want to try tweaking /proc/sys/net/ipv4/tcp_keepalive_* on the
server to make TCP keepalives notice the dead connection faster (these
are global settings, though). There are corresponding setsockopt() calls
you could make on a per-socket basis if you are prepared to tweak the
server code.

-O