Thread: Lost connections
Version: PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC
I was testing what would happen to a connection on the server when a client disconnects from the database via a lost network / internet connection.
To test this scenario out I connected with a Java application and then simply unplugged my network cable. I then shut down my application before reconnecting the network cable.
I then viewed the postgres processes running on the database server, which revealed the following:
postgres 12304 0.0 1.0 7416 1300 ? S Jan20 0:00 /usr/bin/postmaster -p 5432
postgres 12307 0.0 0.0 8280 52 ? S Jan20 0:00 postgres: stats buffer process
postgres 12309 0.0 0.1 7332 232 ? S Jan20 0:00 postgres: stats collector process
postgres 822 0.0 2.5 8100 3292 ? S 18:31 0:00 postgres: test_user test_data 192.168.100.22 idle
As you can see there still existed the connection to the database that I had established from the Java app (from my computer 192.168.100.22).
My questions are:
1) How as an administrator can I tell which connections are lost and which are not so I can shut down the appropriate processes?
2) Is there a way of connecting to the server with client applications that can avoid this situation. That is can the client software do something extra when connecting such as additional parameters, keep alive socket options... to avoid this scenario?
Thanks in advance
Donald Fraser
On Thu, Jan 30, 2003 at 07:10:47PM -0000, Donald Fraser wrote:
> 1) How as an administrator can I tell which connections are lost
> and which are not so I can shut down the appropriate processes?
The dead connection should get cleaned up when the TCP timeout
happens, as I understand it.
> 2) Is there a way of connecting to the server with client
> applications that can avoid this situation. That is can the client
> software do something extra when connecting such as additional
> parameters, keep alive socket options... to avoid this scenario?
Not as far as I know. You could lower your TCP timeout, I guess, but
I believe the TCP/IP authorities say this is a Bad Idea.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
"Donald Fraser" <demolish@cwgsy.net> writes:
> As you can see there still existed the connection to the database that I ha=
> d established from the Java app (from my computer 192.168.100.22).
FWIW, that backend will eventually time out and go away. The delay is
determined by whatever the TCP_KEEPALIVE code in your kernel does before
reporting the connection to be lost. It's probably an hour or so, if
the TCP code is RFC-compliant. I would not necessarily consider it a
good idea to shorten that delay; you'd be giving up robustness against
transient communication problems if you do.
regards, tom lane