0

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.

Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
1
  • 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.
    – anon
    Sep 11, 2012 at 12:41
0

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

Improve this answer

You must log in to answer this question.