[Dbix-class] Apache::DBI and DBIx::Class

[Brandon Black]

On 4/5/06, Matt S Trout <dbix-class at trout.me.uk> wrote:
> Doran Barton wrote:
> > I'm a relative newbie with DBIx::Class- but recently deployed a new web
> > application using it. The application works great and thanks to DBIx::Class,
> > it took less time to do.
> >
> > The application is implemented as mod_perl content handler. I am using
> > Apache::DBI, as with other mod_perl applications, to maintain persistent
> > connections to our PostgreSQL database. However, since deploying this
> > application, we frequently exhaust our database connections. I think,
> > perhaps, DBIx::Class is not taking advantage of Apache::DBI.
>
> The problem is that if you open a handle before MP fork with Apache::DBI it
> hangs onto it, resulting in all sorts of interesting explosions with
> DBIx::Class, which then ends up with a duff connection handle.
>
> As a result, in its quest to be fork- and thread- safe, DBIx::Class avoids
> using the Apache::DBI connection stuff. I'm not sure how that would result in
> exhausting db connections though since DBIx::Class keeps its connection
> persistent so it should only need one per process/thread using it.

Yes.  Basically, what Apache::DBI does is enable a hook in DBI itself
to cache connections based on connect info.  If you call ->connect
twice with the same arguments, and the old connection is still up, it
will re-use that connection.  The idea (AFAIK) was to help converting
legacy CGI applications which might have been designed to ->connect to
the database on every request, into mod_perl applications that keep
their connection persistent.  In an application designed for mod_perl
(or other persistent) usage that knows to only connect once at process
startup, Apache::DBI doesn't help you.

Also, because Apache::DBI's connection caching never checks to see if
the process and/or thread id changed, it will give you an invalid $dbh
from a previous process/thread if you use a connection, then
thread/fork, then try to re-connect to the same source, which is a Bad
Thing.

DBIx::Class already manages its connections for you, and therefore it
cannot benefit from Apache::DBI under any scenario.  It makes one
connection per-process, and keeps that connection persistent,
reconnecting only if the connection appears to have died, or if you
fork/thread over to another process/thread-id.  The only Apache::DBI
issue in DBIx::Class is that Apache::DBI will actually thwart
DBIx::Class's connection management code, and cause it to use the same
(and invalid) connection in a new process, in cases such as (as
mentioned above) if you make a DBI connection before forking in a
prefork mod_perl server.

To work around this, DBIx::Class has specific code in it to work
around Apache::DBI, nullifying the effects of Apache::DBI on
DBIx::Class.  Essentially, loading Apache::DBI should change nothing
and be rather pointless under DBIx::Class.

The only reason we support it (support working around it) is because
someone might want Apache::DBI loaded up under the same mod_perl as a
DBIx::Class application to support a different legacy application
which does not use DBIx::Class, in which case they share a perl
interpreter and will both have the same set of modules loaded.

At least, the above is how things are supposed to be.   If it doesn't
appear to be working correctly for you, then we have a bug to
investigate.

-- Brandon