Handle DBI connection lost

bash has asked for the wisdom of the Perl Monks concerning the following question:

Hello,
I have script that is connected to PostgreSQL and run 24x7.
Some times we need to restart PostgreSQL or some connection problem can occur, so script is needed to reconnect on this situations.

There are two ways:
1) check connection before every statement execution
2) check connection on errors

I decided to go by way 2, because I don't want to add extra overhead by calling ping() every time we execute statement
So my problem is how to be sure that DBI error is about "connection".
Here is my prototype based on 'Active' attribute and on state() method.

eval {
    $dbh->do(...);
};
if (my $e = $@) {
    if ($dbh->{Active} != 1 || $dbh->state =~ /08\d{3}/){
        # reconnect and retry
    } else {
        # not connection related error
    }
}
[download]

Is it correct way?
And how to emulate connection lost for testing (maybe fork and close handler from child)?

update 2017-02-23
I emulated connection lost via fork:

unless (fork()) {
    $dbh->{InactiveDestroy} = 0;
    exit;
}
wait();
[download]

My observation shows that:
$dbh->{Active} == 1 even if dbh return "DBD::Pg::db selectrow_array failed: no connection to the server".
$dbh->{Active} == '' only if you explicitly call $dbh->disconnect;
But $dbh->state works like expected.

Still interesting in any production experience with robust handling for connection lost.

PS. To be sure that ping is really slow down I performed simple test

sub query {
    $dbh->selectrow_array(q{ SELECT now() });
}

sub ping_and_query {
    $dbh->selectrow_array(q{ SELECT now() }) if $dbh->ping;
}

cmpthese(-2, {
    'query'      => \&query,
    'ping+query' => \&ping_and_query,
});

---
              Rate ping+query      query
ping+query  6349/s         --       -39%
query      10327/s        63%         --
[download]

Comment on Handle DBI connection lost Select or Download Code

Replies are listed 'Best First'.
Re: Handle DBI connection lost by RonW (Parson) on Feb 22, 2017 at 23:24 UTC
Reconnect and retry is better suited for automatic recovery from a network "glitch" than from a DB server restart. According to one of the IT people I know at work, when a DB server needs to be restarted, they notify clients to shutdown their apps, first, then do the DB restart, then notify clients they may restart there apps.	[reply]
Re: Handle DBI connection lost by Mr. Muskrat (Canon) on Feb 23, 2017 at 16:04 UTC
I have a non-Perl answer but it's probably the best answer that you will receive. Install pgbouncer and point the app at it. Before you restart the database, connect to pgbouncer and pause all connections to that database. Then you can restart the database. Once the database comes back up, unpause the connections in pgbouncer. PostgreSQL restart with PgBouncer without losing client connections (YouTube)	[reply]
Re^2: Handle DBI connection lost by bash (Scribe) on Feb 27, 2017 at 12:45 UTC
PgBouncer is good advice for server restart. Thank you. But it doesn't solve network issues.	[reply]
Re: Handle DBI connection lost by Your Mother (Archbishop) on Feb 24, 2017 at 15:46 UTC
This is what I used to solve an issue with a customer job that takes days to run on installations which have occasional DB restarts (for no good reason but that's another story): DBIx::RetryOverDisconnects.	[reply]
Re^2: Handle DBI connection lost by bash (Scribe) on Feb 27, 2017 at 12:59 UTC
Interesting package... especially some "hacks" like dbh-> swap_inner_handle	[reply]
Re: Handle DBI connection lost by Anonymous Monk on Feb 22, 2017 at 19:38 UTC
Restart your script when you restart the database.	[reply]
Re^2: Handle DBI connection lost by bash (Scribe) on Feb 22, 2017 at 21:40 UTC
If you have many database requests per second then it's very hard to restart script and database synchronously, especially when you have many instances of this script spread across cluster.	[reply]
Re^3: Handle DBI connection lost by Laurent_R (Canon) on Feb 22, 2017 at 22:22 UTC
Still, it does not seem to make sense to let your script continue to run when the database is down.	[reply]
Re^4: Handle DBI connection lost by bash (Scribe) on Feb 23, 2017 at 01:40 UTC
Re: Handle DBI connection lost by gsiems (Deacon) on Feb 24, 2017 at 19:14 UTC
I've been pretty happy using DBIx::Connector.	[reply]
Re^2: Handle DBI connection lost by bash (Scribe) on Feb 27, 2017 at 12:47 UTC
Yep, i've looked at this module. Unfortunately it goes by way 1, i.e. ping'ing server before every request.	[reply]
Re^3: Handle DBI connection lost by Anonymous Monk on Feb 28, 2017 at 00:12 UTC
You sure? "In fixup mode, DBIx::Connector executes the block without pinging the database. But in the event the block throws an exception, if DBIx::Connector finds that the database handle is no longer connected, it will reconnect to the database and re-execute the block."	[reply]
Re^4: Handle DBI connection lost by bash (Scribe) on Feb 28, 2017 at 06:14 UTC


"be consistent"
	PerlMonks