Re: Problems with DBI, DBD::Pg and Postgres 7.2.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Our problem is that we get this message while running our software:

> DBD::Pg::db do failed: server closed the connection unexpectedly at
> /usr/local/lib/perl5/5.6.1/Atomic/afv3/urls.pm line 542.

Look at the code around it - it looks to me as if your script is hamstringing
itself by disconnecting from the database without letting itself know about
it. Take a careful look at the code around it. If you are using fork, then
that's your problem - when you fork, whichever dies first (the parent or the
child) will take the connection to the database down with it, via DBI's
cleanup subroutine, which implicitly does a $dbh->disconnect. The relative
will still have what it thinks is a live connection to the database, but
will get the error above when it realizes that there is nobody listening on
the other end anymore. In other words, something like this:

#!/usr/bin/perl

use DBI;
use strict;

## Proof of concept of "server closed" message caused by forking

my $dbh = DBI->connect("dbi:Pg:dbname=test;port=5432",
          'joesizpack', 'tanstaafl', { AutoCommit => 0, RaiseError => 1 } );

if (fork) {
  ## I am the parent
  sleep sleep(1);
  $dbh->do("SELECT VERSION()");
  exit;
}

## I am the child
exit; ## Disconnects from database, $dbh no longer active



When you run the above code, $dbh gets set as normal. After the fork,
while the parent sleeps, the child exits and in the process disconnects
from the database, telling the server that we are done. The parent is
using the same variable but there is nobody on the other end. To the
parent, it looks as if the server has dropped the connection. It has, but
only because its child asked it to. :)

If this is the problem (and obviously I suspect it is or I would not have
done so much typing!) workarounds include (1) opening up a new database
connection for every child (use a different variable than $dbh), (2) waiting
to open the database connection until after the forks (basically achieves the
same effect), or use the "InactiveDestroy" attribute when opening the
initial connection to the database (see perldoc DBI for details). The last
way is probably the best overall solution because it does more of what you
might "expect", but may not work on other databases (e.g. Oracle) if you ever
intend your code to run elsewhere.

HTH,
Greg Sabino Mullane  greg@turnstep.com
PGP Key: 0x14964AC8 200202240835

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8eOxsvJuQZxSWSsgRAuFdAKDojhWpWpQmPJ6cX+hXLiI5RY+PJgCeKGD1
OetaWUVlsIb9UOI7bbA3EH0=
=VXOh
-----END PGP SIGNATURE-----