[Catalyst] Shared database handles under mod_perl 2.0 / Catalyst 5.33?

[Brandon Black]

On 11/10/05, Brandon Black <blblack at gmail.com> wrote:
> On 11/10/05, radium <radium at gmail.com> wrote:
> > On 11/10/05, Brandon Black <blblack at gmail.com> wrote:
> > > handle, which of course causes occasional prepare_cached -related
> > > errors as they step all over each other.  I'm pretty sure the shared
> > >
> >
> >  Are you sure you are reading to the end of your statement handles?  If not,
> > you may need to fix that or call finish() on them.
> >
> [....]
>

I've fixed the problem.  I don't know if this is the right place to
fix it, but it does fix it.  Attached is a genericized version of what
my base Model class looks like now:

-------------cut here-----------------

package MyApp::M::MyDB;

use strict;
use NEXT;
use base 'Catalyst::Model::CDBI';

__PACKAGE__->config(
    dsn           => 'dbi:Pg:dbname=mydb',
    user          => 'postgres',
    password      => 'pass',
    relationships => 1,
    options       => { AutoCommit => 1 },
);

sub new {
    my $class = shift;
    my $self  = $class->NEXT::new( @_ );
    ($self->loader->classes)[0]->db_Main->disconnect;
    return $self;
}

1;
------------------- end -------------------------------------

The main change over the stock default setup is to "use NEXT", and
then "new" method override at the bottom (which calls the stock
CDBI->new and then forcibly disconnects the database handle it
apparently left open from some related class or other (which, if you
preload your Catalyst app at pre-fork time for efficiency, means
trouble lies ahead)).

On a related note, I highly recommend those using PostgreSQL w/
Catalyst default AutoCommit to "on" as I have, rather than letting
Class::DBI default it off.  Class::DBI's decision to default it off
for Oracle and PostgreSQL while defaulting it on for other drivers is
almost malicious, even when documented.  Unless you plan to wrap
*every* database call / sql statement you ever make (even simple
SELECTs invoked by something like a $db_class->search() function) in a
real transaction wrapper with rollback/commit at the end, turning off
AutoCommit is a mistake.  And I highly doubt anyone is going to litter
their Controller code with commit/rollback blocks for readonly
operations.

At least under PostgreSQL, even a readonly SELECT statement is part of
a transaction, and if you issue a SELECT via DBI without AutoCommit
and then idle the connection for a while (which would be a common
mod_perl usage pattern), the connection will not be "idle", it will be
"idle in transaction", taking up locks on the backend and all sorts of
nasty stuff that's bad for efficiency and sanity.

-- Brandon