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

[Paul Rogers]

I'm also having problems using prepared statements with pgPool. The  
JDBC error returned is "ERROR: prepared statement "S_1" already  
exists". However, it only appears after the first few queries. And if  
I retry the query it appears to work after several attempts. Regular,  
non-prepared, statements also produce the same error. Adding  
'DEALLOCATE S_1', in the application or the reset query list,  
produces this error: "ERROR: prepared statement "s_1" does not exist".

Test program output:
Connection: 1
   Prepared statement: one
Connection: 2
   Prepared statement: two
Connection: 3
   ERROR: prepared statement "S_1" already exists
Connection: 4
   ERROR: prepared statement "S_1" already exists
Connection: 5
   Prepared statement: five

Test program source:
import java.sql.*;
class JavaReplicationTest {
   public static void main(String args[]) {
     Connection db = null;
     for(int i=1; i <= 5; i++) {
     try {
       Class.forName("org.postgresql.Driver");
       db = DriverManager.getConnection(
         "jdbc:postgresql://192.168.1.101:9999/replication_test",
         "user",
         "password");
       db.setAutoCommit(false);
       db.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
       System.out.println("Connection: " + i);
       PreparedStatement ps = db.prepareStatement(
         "SELECT description FROM replication_test WHERE id = ?;",
         ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
       ps.clearParameters();
       ps.setInt(1, i);
       ResultSet result = ps.executeQuery();
       result.next();
       System.out.println("  Prepared statement: " + result.getString 
(1));
       result.close();
       ps.close();
       db.close();
     } // END TRY
     catch(SQLException e) {
       System.out.println("  " + e.getMessage());
     }
     } // END FOR
   } // END METHOD main
} // END CLASS


> 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
> >