[Pgpool-general] Problems using pgpool, perl DBI, and prepared
statements
Tatsuo Ishii
ishii at sraoss.co.jp
Fri Jan 27 23:31:03 GMT 2006
> 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
>
More information about the Pgpool-general
mailing list