What would happen if a process established multiple PostgreSQL connections and terminated without closing them?

Question

I'm writing a DLL for a purchased software.

The software will perform multi-threaded calculations on certain tasks.

My job is to output the relative result into a database.

However, due to the limited support of the software, it is kind of difficult to do multi-threaded output of the data.

The key problem is that there is no info on the last execution of the DLL function.

Therefore, the database connection will not be closed.

So may I ask if I leave the connection open and terminate the process, what would be the potential problems?

My platform is winserver 2008, and PostgreSQL 10.

Answer 1

I don't understand the background information you are giving, but I can answer the question:

If a PostgreSQL client process dies without closing the database (and TCP) connection, the PostgreSQL server process (“backend process”) that servers this connection will not realize this immediately.

Of course, as soon as the server tries to communicate to the client, e.g. to return some results, TCP it will notice that the partner has gone away and will return an error.

However, often the backend process is idle, waiting for the client to send the next request. In this case, it would never notice that its partner has died. This could eventually cause max_connections to be exhausted with dead connections.

Because this is a common problem in networking, TCP provides the “keepalive” functionality: when a connection has been idle for a while (2 hours by default), the operating system will send a so-called “keepalive packet” and wait for a response from the other side. Sending keepalive packets is repeated several times (5 times by default) in short intervals (1 second by default), and if no response is received, the connection is closed by the operating system, the backend process receives an error message and terminates.

PostgreSQL provides parameters with which you can configure these settings on the server side: tcp_keepalives_idle, tcp_keepalives_count and tcp_keepalives_interval. If you set tcp_keepalives_idle to a shorter value, dead connections will be detected and removed faster, at the cost of some little added network traffic.