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

Denis Thomas dthomas at telecomdesign.fr
Thu Jan 9 22:19:24 JST 2014


2014/1/8 Lachezar Dobrev <l.dobrev at gmail.com>

>   Did you try the 3.3.2 version without protocolVersion=2?
Yes, and I get same exception than previously.

>   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.
Thanks for these details. Tatsuo Ishii said that protocol version 2 does
not support load balancing. So, I will have to give up it if I will must
use this version.

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

The two servers are configured with the same parameters, except fot
replication of course, and wal archiving, not activated on slave. Is it
possible that the problem come from ?

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20140109/e026ba49/attachment.html>

More information about the pgpool-general mailing list