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

[Tatsuo Ishii]

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

My guess is you set num_init_children to > 5, and you are getting
these errors after some of pgpool processes establish pooled
connections to PostgreSQL.

> Regular,  
> non-prepared, statements also produce the same error.

I heard that recent version of JDBC drivers treat every queries into
prepared ones even if they are not non-prepared statments.

> Adding  
> 'DEALLOCATE S_1', in the application or the reset query list,  
> produces this error: "ERROR: prepared statement "s_1" does not exist".

My guess is that's because you connect to a pgpool process which does
not connect to PostgreSQL yet.

Can you try change your test program:

      for(int i=1; i <= 5; i++) {

to 

      for(int i=1; i <= 1; i++) {

then set num_init_children to 1, see you still see same errors?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> 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
> > >
> 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
>