Re: Rose::DB and Apache::DBI

[John Siracusa]

On 10/23/07, Arshavir Grigorian <[EMAIL PROTECTED]> wrote:
> I just started using Rose::DB and have been experiencing certain
> database connectivity issues.
>
> DBD::Pg::st execute failed: ERROR:  prepared statement "dbdpg_1" already 
> exists

A quick google turned up this page which offers a possible work-around:

http://people.planetpostgresql.org/greg/index.php?/archives/110-Using-DBDPg-with-pg_bouncer.html

Still, I think you should investigate until you find the actual
problem.  I've used RDBO wit DBD::Pg and Apache::DBI in the past and
haven't encountered this problem.

> Also, in my main handler, I do
>
> $r->pnotes(dbh => MSS::DB->new->dbh );

I've had some bad experiences with passing database handles around in
pnotes.  You might want to try temporarily replacing that with with a
global variable or something similarly tame just to see if it fixes
the problem.

Finally, I'll offer the init_db() implementation that I'm currently
using with Apache::DBI.  I don't think it will help your problem, but
then I don't really know what's causing it.

I suggest adding some debugging code until you completely understand
the lifecycle of database handles in your particular application.
Solutions to problems like these usually presents themselves clearly
once you see when and where database handles are created, cached,
reset, and destroyed.

-John

---

# An init_db() method for use with Apache::DBI

package My::DB;
use base 'Rose::DB';
....

package My::DB::Object;
use base 'Rose::DB::Object';
...
BEGIN:
{
  # Under mod_perl, cache the My::DB object, while letting Apache::DBI cache
  # the DBI $dbh it contains.  Preventing new My::DB objects from being
  # created then also prevents DBI->connect() from being called; though it
  # does not always cause a literal connect under Apache::DBI, it *does*
  # cause Apache::DBI to reset the $dbh to its initial state (arguably an
  # unwanted "feature" under mod_perl) and this will hose any extant
  # transactions on that handle if the initial state is AutoCommit => 1.
  if($ENV{'MOD_PERL'})
  {
    our $DB;
    *init_db = sub
    {
      # Can't cache in the parent apache process
      if($Apache::Server::Starting)
      {
        return My::DB->new;
      }
      else
      {
        if($DB)
        {
          $DB->dbh(undef); # will auto-reconnect as needed
          return $DB;
        }
        else
        {
          return $DB = My::DB->new;
        }
      }
    };
  }
  else # act "normally" when not under mod_perl
  {
    *init_db = sub { My::DB->new };
  }