Finding out a DBI connection is broken?

Discussion in 'Perl Misc' started by Thomas Reat, Dec 29, 2003.

  1. Thomas Reat

    Thomas Reat Guest

    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.
    Thomas Reat, Dec 29, 2003
    #1
    1. Advertising

  2. "Thomas Reat" <> wrote in message
    news:...
    > 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
    Ragnar Hafstað, Dec 29, 2003
    #2
    1. Advertising

  3. Thomas Reat wrote:

    > 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.
    Darin McBride, Dec 30, 2003
    #3
  4. Thomas Reat

    Craig Manley Guest

    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.


    "Thomas Reat" <> wrote in message
    news:...
    > 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.
    Craig Manley, Jan 4, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ulloa
    Replies:
    1
    Views:
    514
    Juha Laiho
    Jul 22, 2004
  2. aemi
    Replies:
    2
    Views:
    5,474
  3. Jerome Hauss
    Replies:
    0
    Views:
    160
    Jerome Hauss
    Oct 13, 2004
  4. Francis Hwang

    Finding out what row DBI chokes on?

    Francis Hwang, Dec 30, 2004, in forum: Ruby
    Replies:
    4
    Views:
    138
    Francis Hwang
    Jan 2, 2005
  5. Asby

    Mason, DBI, and DBI::Pg

    Asby, Jul 24, 2003, in forum: Perl Misc
    Replies:
    0
    Views:
    167
Loading...

Share This Page