[Pgpool-general] jdbc connection connection question
Gary Fu
gfu at saicmodis.com
Mon Jun 9 20:44:41 UTC 2008
Hi,
I have pgpool2 2.0.1 running on linux with two backend postgresql 8.2.6
servers,
one with the pgpool2 and the other one is on another linux box.
I tried pgadmin from a pc to connect the pgpool through the ssh
tunneling without
problem on select/update/insert sql. A simple java program (see below)
with the
jdbc connection has no problem to make select sql, but fails on
upadte/insert sql.
I checked the pgpool log (with debug mode, see below) and notice some
difference:
- SSLRequest log from pgadm, but not from the java code
- client_encoding value is LATIN1 from pgadm, but UNICODE from the java code
- I can see the log of sql statement from pgadmin, but not any sql log
from the java code
(but the java code does print out the select result, I did not include
this)
Can any one explain what's the problem and how to make the java code to
work (update/insert)?
Thanks,
Gary
************ log from pgadm
2008-06-09 16:06:22 LOG: pid 18837: pgpool successfully started
2008-06-09 16:06:22 DEBUG: pid 18886: I am PCP 18886
2008-06-09 16:06:22 DEBUG: pid 18837: starting health checking
2008-06-09 16:06:22 DEBUG: pid 18837: health_check: 0 the DB node status: 1
2008-06-09 16:06:22 DEBUG: pid 18837: health_check: 1 the DB node status: 1
2008-06-09 16:06:35 DEBUG: pid 18885: I am 18885 accept fd 0
2008-06-09 16:06:35 DEBUG: pid 18885: Protocol Major: 1234 Minor: 5679
database: user:
2008-06-09 16:06:35 DEBUG: pid 18885: SSLRequest: sent N; retry
startup <--------------------- ????
2008-06-09 16:06:35 DEBUG: pid 18885: Protocol Major: 3 Minor: 0
database: nppsd3 user: nppsd3
2008-06-09 16:06:35 DEBUG: pid 18885: new_connection: connecting 0 backend
2008-06-09 16:06:35 DEBUG: pid 18885: new_connection: connecting 1 backend
2008-06-09 16:06:35 DEBUG: pid 18885: pool_read_message_length: slot: 0
length: 8
2008-06-09 16:06:35 DEBUG: pid 18885: pool_read_message_length: slot: 1
length: 8
2008-06-09 16:06:35 DEBUG: pid 18885: trying clear text password
authentication
2008-06-09 16:06:35 DEBUG: pid 18885: trying clear text password
authentication
2008-06-09 16:06:35 DEBUG: pid 18885: pool_read_message_length2: master
slot: 0 length: 27
2008-06-09 16:06:35 DEBUG: pid 18885: pool_read_message_length2: master
slot: 1 length: 27
2008-06-09 16:06:35 DEBUG: pid 18885: 0 th backend: name:
client_encoding value: LATIN1 <-------------- ????
2008-06-09 16:06:35 DEBUG: pid 18885: 1 th backend: name:
client_encoding value: LATIN1
2008-06-09 16:06:35 DEBUG: pid 18885: pool_read_message_length2: master
slot: 0 length: 23
2008-06-09 16:06:35 DEBUG: pid 18885: pool_read_message_length2: master
slot: 1 length: 23
************* log from the java program
2008-06-09 15:59:09 LOG: pid 18599: pgpool successfully started
2008-06-09 15:59:09 DEBUG: pid 18648: I am PCP 18648
2008-06-09 15:59:09 DEBUG: pid 18599: starting health checking
2008-06-09 15:59:09 DEBUG: pid 18599: health_check: 0 the DB node status: 1
2008-06-09 15:59:09 DEBUG: pid 18599: health_check: 1 the DB node status: 1
2008-06-09 15:59:18 DEBUG: pid 18616: I am 18616 accept fd 0
2008-06-09 15:59:18 DEBUG: pid 18616: Protocol Major: 3 Minor: 0
database: nppsd3 user: nppsd3
2008-06-09 15:59:18 DEBUG: pid 18616: new_connection: connecting 0 backend
2008-06-09 15:59:18 DEBUG: pid 18616: new_connection: connecting 1 backend
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length: slot: 0
length: 8
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length: slot: 1
length: 8
2008-06-09 15:59:18 DEBUG: pid 18616: trying clear text password
authentication
2008-06-09 15:59:18 DEBUG: pid 18616: trying clear text password
authentication
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length2: master
slot: 0 length: 28
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length2: master
slot: 1 length: 28
2008-06-09 15:59:18 DEBUG: pid 18616: 0 th backend: name:
client_encoding value: UNICODE <---------- ????
2008-06-09 15:59:18 DEBUG: pid 18616: 1 th backend: name:
client_encoding value: UNICODE
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length2: master
slot: 0 length: 23
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length2: master
slot: 1 length: 23
....
....
2008-06-09 15:59:18 DEBUG: pid 18616: pool_process_query: kind from
backend: Z
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length: slot: 0
length: 5
2008-06-09 15:59:18 DEBUG: pid 18616: pool_read_message_length: slot: 1
length: 5
2008-06-09 15:59:18 DEBUG: pid 18616: ReadyForQuery: message length: 5
2008-06-09 15:59:18 DEBUG: pid 18616: ReadyForQuery: transaction state: I
2008-06-09 15:59:19 DEBUG: pid 18616: read kind from frontend P(50)
2008-06-09 15:59:19 DEBUG: pid 18616: Parse: portal name
<> <---------------- ???
2008-06-09 15:59:19 DEBUG: pid 18616: pool_read_message_length: slot: 0
length: 5
2008-06-09 15:59:19 DEBUG: pid 18616: pool_read_message_length: slot: 1
length: 5
2008-06-09 15:59:19 DEBUG: pid 18616: ReadyForQuery: message length: 5
2008-06-09 15:59:19 DEBUG: pid 18616: ReadyForQuery: transaction state: I
2008-06-09 15:59:19 DEBUG: pid 18616: waiting for master completing the
query
2008-06-09 15:59:19 DEBUG: pid 18616: waiting for backend completing the
query
2008-06-09 15:59:19 DEBUG: pid 18616: read_kind_from_backend: read kind
from 0 th backend 1 NUM_BACKENDS: 2
2008-06-09 15:59:19 DEBUG: pid 18616: read_kind_from_backend: read kind
from 1 th backend 1 NUM_BACKENDS: 2
2008-06-09 15:59:19 DEBUG: pid 18616: read kind from frontend B(42)
2008-06-09 15:59:19 DEBUG: pid 18616: bind message: portal_name
stmt_name <----------- ???
2008-06-09 15:59:19 DEBUG: pid 18616: read_kind_from_backend: read kind
from 0 th backend 2 NUM_BACKENDS: 2
2008-06-09 15:59:19 DEBUG: pid 18616: read_kind_from_backend: read kind
from 1 th backend 2 NUM_BACKENDS: 2
2008-06-09 15:59:19 DEBUG: pid 18616: read kind from frontend D(44)
************** java code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class testDb {
public static void main(String[] args) {
try {
ResultSet lQueryResult = null;
Statement lSql = null;
Class.forName("org.postgresql.Driver");
Connection lConnection =
DriverManager.getConnection(
"jdbc:postgresql://localhost:9999/nppsd3", "username",
"password"); // connect to the db
lSql =
lConnection.createStatement(ResultSet.CONCUR_UPDATABLE,
ResultSet.TYPE_SCROLL_INSENSITIVE);
lQueryResult = lSql.executeQuery("select name
from config");
while (lQueryResult.next()) {
System.out.println(" Print config: "
+
lQueryResult.getString("value"));
}
lConnection.commit();
lConnection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
More information about the Pgpool-general
mailing list