Finding out a DBI connection is broken?

[Thomas Reat]

I'm using postgresql with DBI, I do a "listen foo" and every minute or
so I do a func('pg_notifies'). If I restart the database server, the
perl script needs to know to reconnect. What is the right way to
determine this? I get the message below when it's restarted, so
obviously something knows about it.

WARNING: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.

 

[Ragnar Hafstað]

I'm using postgresql with DBI, I do a "listen foo" and every minute or
so I do a func('pg_notifies'). If I restart the database server, the
perl script needs to know to reconnect. What is the right way to
determine this? I get the message below when it's restarted, so
obviously something knows about it.

WARNING: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.

how are you restarting the database?

gnari

 

[Darin McBride]

I'm using postgresql with DBI, I do a "listen foo" and every minute or
so I do a func('pg_notifies'). If I restart the database server, the
perl script needs to know to reconnect. What is the right way to
determine this? I get the message below when it's restarted, so
obviously something knows about it.

WARNING: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.

Here's my code from DB2::db - this portion should not, I hope, be
DBD-specific, but be general enough for any DBI.

sub connection
{
my $self = shift;
unless ($self->{dbh} and $self->{dbh}{Active})
{
$self->{dbh} = DBI->connect($self->_data_source,
$self->user_name,
$self->user_pw,
$self->connect_attr);
}
$self->{dbh}
}

You can probably guess what the params are - there's nothing special
here (I have AutoCommit turned off, but that shouldn't be anything
special).

As you can see, the only "special" part is the Active flag. I hope
this gets you what you need.

 

[Craig Manley]

Hi,

The DBI has a ping() method specifically for this purpose.

This comes in useful when using persistent connections such as in mod_perl
scripts. See the dbh() method snippet from a singleton class I created
below:

sub dbh {
my $proto = shift;
my $self = ref($proto) ? $proto : $proto->instance();
my $dbh = $self->{$CLASS_FIELD_DBH};
unless(defined($dbh) && $dbh->ping()) {
my %properties = $self->config()->properties();
$dbh = DBI->connect($properties{'db.dsn'},
$properties{'db.user'},
$properties{'db.password'},
{RaiseError => 1});
unless(defined($dbh)) {
croak($DBI::errstr);
}
$self->{$CLASS_FIELD_DBH} = $dbh;
}
return $dbh;
}



-Craig Manley.