[fpc-pascal] Database apps on Debian etc.

[Tony Whyman]

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.


On 11/11/17 18:41, Mark Morgan Lloyd wrote:
> 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?
>