[Pgpool-general] Problems using pgpool, perl DBI, and prepared statements

[Tatsuo Ishii]

What happening here is, pgpool does not close actual DB connection
when $dbh->disconnect gets executed. This is the nature of connection
pooling. You need to DEALLOCATGE before $dbh->disconnect. Instead of
issuing DEALLOCATGE explicitely, you could add "DEALLOCATE dbpg_1" to
"reset_query_list" in pgpool.conf. Example:

reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT; DEALLOCATE dbpg_1'

> I'm trying out pgpool for the first time, and I'm getting errors like 
> this in my Perl application:
> 
> DBD::Pg::st execute failed: ERROR:  prepared statement "dbdpg_1" already 
> exists
> 
> In the sample program below, I get no errors if I create just one DB 
> handle (set $mode to 'one-connection').  However, if the DB handle is 
> connected and disconnected around each query (set $mode to 
> 'multiple-connections'), I get the error on the second iteration.  If 
> the program is changed to do just one iteration, it always succeeds -- 
> until it has been run one more time than there are pgpool connection 
> processes running.  It seems that the query can be prepared only once 
> through any given pgpool process ....
> 
> I'm using pgpool 2.6.5, PostgreSQL 8.1, Perl 5.8.6, DBI 1.48, DBD::Pg 
> 1.43, on Mac OS X 10.4.3.
> 
> I feel that I must be missing something obvious.
> 
> Thanks,
> Kevin Murphy
> 
> # begin
> use strict;
> use warnings;
> use DBI;
> my $mode = 'multiple-connections';
> my ($dbh, $sth);
> if ($mode eq 'one-connection') {
>     $dbh = 
> DBI->connect("dbi:Pg:dbname=testdb;host=127.0.0.1;port=9999",'testuser', 
> 'testpass')
>         or die DBI->errstr;
> }
> foreach my $iteration (1..20) {
>     unless ($mode eq 'one-connection') {
>         $dbh = 
> DBI->connect("dbi:Pg:dbname=testdb;host=127.0.0.1;port=9999",'testuser', 
> 'testpass')
>             or die DBI->errstr;
>     }
>     $sth = $dbh->prepare('select 1 from pg_class where relname = ?') or 
> die $dbh->errstr;
>     $sth->execute('views') or die $sth->errstr;
>     while (my @row = $sth->fetchrow_array) {
>     }
>     $sth->finish;
>     unless ($mode eq 'one-connection') {
>         $dbh->disconnect;
>     }
>     print "Iteration $iteration OK\n";
> }
> if ($mode eq 'one-connection') {
>     $dbh->disconnect;
> }
> #end
> 
> 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
>