In database PostgreSQL, a client-to-server connection is actually a TCP socket connection, the TCP connection is a virtual connection, the other party exits abnormally (such as a power outage), and the other party continues to maintain the connection. For example, a client computer normally connected to the server, forcibly unplug the power caused by artificial power, restart the computer, and then connected to the server. Using the SQL statement SELECT * FROM pg_stat_activily to view all connections to the server, you will find that the connection to this client is still in addition to this time, before the power outage. Because the server has no knowledge of the client's power-down behavior, it also thinks that the connection is in an idle state. However, this dead connection does not exist forever, and after 2 hours, the connection on the server is automatically cut off, because PostgreSQL supports the tcp_keeplive mechanism. There are three system variables Tcp_keepalives_idle,tcp_keepalives_interval, Tcp_keepalives_count to set how PostgreSQL handles dead connections. For each connection, PostgreSQL will be idle for this connection tcp_keepalives_idle seconds after the active send tcp_keeplive packet to the client, to the detective client is still alive, when sending Tcp_keepalives_count a detective bag, Each detective pack did not respond within tcp_keepalives_interval seconds, and PostgreSQL thought the connection was dead. So cut off the dead connection. In PostgreSQL, these three parameters are set to 0 will use the operating system default value, under Linux, Tcp_keepalives_idle is generally 2 hours, that is, 2 hours after the server can automatically shut down the dead connection. In the actual should be shipped, you can adjust the above parameters on your own. However, it is never enough to rely solely on the server to cut off dead connections in this way. Suppose there is a connection that suddenly loses power in running the following interactive command begin TRANSACTION; Lock table XXX in exclusive mode;--Sudden power outage, this may be small, but there must be。。。 Commit because this connection is still retained, and this transaction is not finished (originally within 1 seconds of the transaction, now becomes at least 2 hours), so the table lock has been there, resulting in a serious decrease in the concurrency of the system. So it is necessary to manually kill the connected statement to cut off this connection to release the lock. Unfortunately, until 8.3,postgresql does not have this statement, the source code is a pg_terminate_query function, because there is a bug, is blocked, must be 8.4. So you can only kill this connection with the command of the operating system, or restart the server. Under Linux, the command to kill this connection is: kill-s SIGTERM process number. This content is a bit old, but the idea is a consistent one, in detail see also: Http://blog.163.com/[email protected]/blog/static/163877040201331041830502/
How PostgreSQL handles Dead connections (GO)
