There are actually two issues sitting here:
1. Transaction Recovery and
2. Knowing when you've lost a connection and when to restart it.
I am not familiar with PostgreSQL but with a transaction oriented database such as Firebird or Oracle, when you lose a connection, re-connecting doesn't also imply recovering the transaction. Your transaction may be in limbo and require manual rollback or commit depending on what is the most desirable outcome and, until you do this your database may not be in a consistent state - depending on the application. It gets even more complication with transactions across multiple databases when two phase commit issues appear.
It is also not always obvious when you have lost a connection. TCP depends on both retransmission and inactivity timers to detect connection loss and some implementations don't even detect connection loss during periods of inactivity and only detect the loss when no reply is received after several retries. In short, there can be a long time between connection loss and it being noticed by either the server or the client - and those events may be well separated in time. Indeed, the user may already be on the line to the help desk complaining that their computer is no longer responding, long before the lost connection error message gets displayed.
The bottom line is that neither detecting connection loss nor recovering from it is a simple matter. In any serious database application, you need to think about how responsive you need to be to connection loss, and how to recover from it. How quickly you need to detect it and then once detected, what is the recovery strategy. Will it require a database administrator action to rollback or commit outstanding transactions? Is it appropriate to always rollback limbo transactions, or do you need to decide the appropriate recovery on a case by case basis?
There is no "one size fits all" answer to the problem. The ideal is that there are no lost connections, except in extreme circumstances such as hardware failure. Automatic updates may seem a good idea, but sometimes it's better to plan and schedule upgrades during planned outages rather than letting them happen when you least want them.
Mark Morgan Lloyd wrote 24 lines
Graeme started a short thread on databases in "The Other Place" a few
days ago, but I thought this might be of sufficient general relevance
to raise here.
I had a system outage this morning, with all apps suddenly losing
their connectivity to the PostgreSQL server.
It turned out that the cause of that was that Debian had done an
unattended upgrade of the Postgres server, and by restarting it had
killed all persistent connections. There is no "Can we kill your
database when we feel like it?" question during Debian installation.
I anticipate that the same problem will affect other databases or
software to which a client program maintains a persistent session,
unless explicit steps are taken to recognise and recover from a
server-side restart.
Noting that the traditional way of using the data-aware controls
introduced by Delphi etc., is particularly vulnerable, and noting that
the FPC/Lazarus controls do a good job of presenting a common API
irrespective of what backend server is being used, would it be
feasible to have a "reconnect monitor" or similar to help recover from
this sort of thing?
