[Pgpool-general] Trouble with the JDBC drivers...

[Toshihiro Kitagawa]

On Fri, 21 Oct 2011 12:20:19 -0400
Bryan Varner <bvarner at polarislabs.com> wrote:

> >> If I could get master_slave&&  load_balance working with the JDBC
> >> drivers, this would be an incredible tool to have.
> >>
> > Is autocommit setting of your application true?
> > If so, can you set autocommit to false by using setAutoCommit(false)?
> >
> 
> Your suggestion is the opposite of what the pgpool documentation states:
> 
>   "Note: the JDBC driver has an autocommit option. If autocommit is 
> false, the JDBC driver sends "BEGIN" and "COMMIT" by itself. So pgpool 
> cannot do any load balancing. You need to call setAutoCommit(true) to 
> enable autocommit."

That description is in "Replication Mode" section. You are using
master/slave mode. pgpool-II 3.0 or later can do load balancing
in master/slave mode, even if setAutoCommit(false).

> I have tried both methods (with load_balance off) and still get the same 
> results. The standby is only receiving BEGIN and COMMIT/ROLLBACK statements.
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I think that this behavior avoids ""S_xx" does not exist" error in standby.
How about trying setAutoCommit(false) with load_balance on?

> My application is currently using c3p0, and I'm doing 
> setAutoCommit(false/true) with a connection customizer class. Is tere a 
> recommended java connection pool to use outside of a j2ee container with 
> pgpool? Has anyone else reported success getting selects sent to the 
> standby server with dbcp or even the postgres jdbc driver connection pool?
> 
> Regards,
> -Bryan
> 

-- 
Toshihiro Kitagawa
SRA OSS, Inc. Japan