postgres Database Pg module

[Kemin Zhou <kz...@san.rr.com>]

Hi Friends,
I am using mod_perl 2.  I question about database connections.
I use the Pg module to connect to the backend.  Could you tell me that 
this is not a good idea and
I should have used Apache::DBI? 

When using Pg I run into such problems as when the connection reaches 
the limit, no more connection
can be made.  This can be seen on the server that host the postgres server

ps -ef | grep postgres

will show all the idle postgres processes.

To overcome this problem I simply did this in my perl script

#!/usr/bin/perl -w

use Apache::Request;
use Pg;

# here I am using the same username and password for all of my
# scripts

my $dbuser="dbreader";
my $passwd="a_secret_str";

our $pgdb;
if (!$pgdb) {
    $pgdb = Pg::connectdb("host=localhost dbname=$mydb user=$dbuser 
password=$passwd");
}

This seems to work, which means that the number of idle postgres 
processes on the server does not
accumulate to the point that no more connection can be made.  I am not 
sure this solution is
to good to be "true".

Am I doing the right thing?  If I am using the same username and 
password for all of my scripts
on the http server, will this cause problems? 

Apache::DBI seems to take care of all of my problems. 

Thanks.

Kemin

Re: postgres Database Pg module

[Perrin Harkins <pe...@elem.com>]

On Mon, 2005-02-14 at 13:31 -0800, Kemin Zhou wrote:
> I am using mod_perl 2.  I question about database connections.
> I use the Pg module to connect to the backend.  Could you tell me that
> this is not a good idea and
> I should have used Apache::DBI?  


Apache::DBI is used to make your Pg connection persistent.  It's not a
choice between one or the other.

> When using Pg I run into such problems as when the connection reaches
> the limit, no more connection
> can be made.

You'll need to either raise the limit or reduce the number of
connections.  Make sure you are not serving static pages or images
through mod_perl by running a reverse proxy.  There are details about
this in the mod_perl documentation.

> our $pgdb;
> if (!$pgdb) {
>     $pgdb = Pg::connectdb("host=localhost dbname=$mydb user=$dbuser
> password=$passwd");
> }

That's what Apache::DBI does for you.  It also does an automatic
rollback of any uncommitted work at the end of each request.  This will
save you from getting stuck if your code dies part-way through a
transaction.

- Perrin