Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This morning one of our customer experienced a network failure for several of its workstations. I was surprised by the fact that connexions (and associated processes) were still alive for several minutes on the database server. Unfortunately, ou client application is not very well designed and open several db connexions simultaneously. This caused the total number of connexions to double on the server, while memory pressure was already high.

Is it by desgin ? Is there a server parameter to force the associated process to close if the client connexion is gone ? This behavior could be easily reproduced but unplugging the network cable on a client workstation while connected to the server.

I'm not sure what finally triggered those connexions to close automatically but it take too much time.

share|improve this question
add comment

2 Answers 2

up vote 0 down vote accepted

Network connections are entirely virtual things. They are established by a network command, ended by a network command and kept alive by sending packets in regular intervals. At no point, there is a physical connection that would allow the server to detect immediately that a connection has been lost. It can only decide that waiting x seconds must be enough to declare the connection dead.

So, yes, this waiting period is very much by design, but you can finetune the timeout with the tcp_keepalives_* options in your PostgreSQL server config.

share|improve this answer
    
I've been confused with this answer on StackOverflow. I'm runnig version 9.1 so the config value is used correctly. Tested it with a 3 min delay for keepalive_idle. Works like a charm. –  omatrot Sep 11 '12 at 12:41
add comment

It's by design. What probably happened is that you started a transaction or a query that locked a table. What you'd want to do is check in random intervals whether that connection has failed, is idle for more than a day,etc.

Use keepalive or apply the same logic in your application.

As a last , not recommended resort you can find the connections by doing,

SELECT * FROM pg_stat_activity; 

and killing them

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.