Database apps on Debian etc.

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Database apps on Debian etc.

Mark Morgan Lloyd-5
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?

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database apps on Debian etc.

Graeme Geldenhuys-6
On 2017-11-11 18:41, Mark Morgan Lloyd wrote:
> 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?

You should always program your software with the assumption that a
network connection could fail at any point. The ability to try and
re-establish the connection should be a standard feature in your
applications.  Programming database apps with tiOPF makes that pretty
easy. Saying that, using Delphi/Lazarus RAD style development with
DB-controls should also be able to allow that - I think.

Regards,
   Graeme

--
fpGUI Toolkit - a cross-platform GUI toolkit using Free Pascal
http://fpgui.sourceforge.net/

My public PGP key:  http://tinyurl.com/graeme-pgp
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database apps on Debian etc.

Mark Morgan Lloyd-5
On 12/11/17 09:30, Graeme Geldenhuys wrote:

> On 2017-11-11 18:41, Mark Morgan Lloyd wrote:> 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?
> You should always program your software with the assumption that a
> network connection could fail at any point. The ability to try and
> re-establish the connection should be a standard feature in your
> applications.  Programming database apps with tiOPF makes that pretty
> easy. Saying that, using Delphi/Lazarus RAD style development with
> DB-controls should also be able to allow that - I think.

It's certainly possible to do it, I've done it. Is
TSQLConnection.KeepConnection intended to automate this?

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Database apps on Debian etc.

Tony Whyman
In reply to this post by Mark Morgan Lloyd-5
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?
>

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal