[pgpool-general: 2384] Re: Error when use pgpool2 with JDBC

[Lachezar Dobrev]

  In-line…

2014/1/8 Denis Thomas <dthomas at telecomdesign.fr>:
> Hello,
>
> thanks for your help.
> I finally managed to use pgpool2 with JDBC. I replaced version 3.1.1 from
> Ubuntu package by 3.3.2 I compiled, and I add in connection parameters
> protocol version 2.

  Did you try the 3.3.2 version without protocolVersion=2?

> My program is not a web application with Tomcat or another servlet
> container, but a little test program written just to try pgpool. I use
> org.apache.commons.dbcp.BasicDataSource to connect and Spring templates to
> send query to database. Here my datasource bean :

  That's fine, I just assumed too much. This should work.

> <bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
>     <property name="username" value="XXX" />
>     <property name="password" value="XXX" />
>     <property name="url" value="jdbc:postgresql://server/database" />
>     <property name="driverClassName" value="org.postgresql.Driver" />
>     <property name="connectionProperties" value="protocolVersion=2" />
> </bean>

  I've hit that problem: pgpool does *NOT* support protocol version 2.
I've needed it for a different issue, but was unable to work this
around.

> By putting protocolVersion=2, that works, but now I 'm facing another
> problems : I have no load balancing, and for transaction, I cannot use
> read-only transaction with propagation required (I have to use
> propagation="SUPPORTS"). But may be these problems come from my program, and
> when I'll test later with a real web application they will disappear.

  This is something you should care about: when using a transaction
all queries are sent to the master node. This is needed in order to
preserve data integrity and isolation. If you want to use the slaves
for read-only queries you must not do so in a transaction.
  For Springframwork I found out, that for read-only methods one
should use @Transactional(propagation = Propagation.SUPPORTS, readOnly
= true). This way Springframework creates the necessary infrastructure
objects (Hibernate or JPA session) and will poll a connection to the
database, but does not actually start a database transaction, which
allows queries to go to a slave. This is a result of me talking to the
Springframework guys a while back when we were trying Slony-I. I was
advised to use this transactional demarcation to make use of read-only
slaves.

  Please anyone feel free to correct me if you believe any of the
above is wrong.

  Apparently when you use protocol version 2 the JDBC Driver uses a
transaction (BEGIN; ... COMMIT) to execute the SET which *probably*
makes it run on the master server only. I am not sure if this is a
good thing. At best this seems to hide the problem, which might become
apparent later.

  I currently have no test-bed with a running pgpool, but maybe you
could create a simple test case: open a JDBC connection (no polling,
no protocol specified, straight JDBC) to the pgpool, and observe the
log files on both back-ends. Tatsuo Ishii noted, that the pgpool
status shows that both back-ends behave differently when the SET is
executed. There might be something there.