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

[Bryan Varner]

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

Fair enough, but this still seems misleading when you're 
trouble-shooting things relating to load balancing.

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

If I turn off load_balance, the application, and the JDBC driver work 
fine. But the slave nodes only receive BEGIN / COMMIT queries from 
pgpool, and are never asked to do anything else.

When I turn on load_balance, it's like pgpool forgets that a select 
query created a cursor or portal on one of the slaves, and then once the 
transaction does something requiring things to be pinned to the master 
node, directs all queries to the master -- even if there are open 
cursors still in use by the application on the slaves.

I was looking through the pgpool code on friday, and I remember seeing 
the condition where once a transaction has tried to write, everything 
gets sent to the master. The only way I see to fix this is to keep track 
in pgpool of what nodes have what open cursors, and even if a 
transaction is started, direct future access to those cursors back to 
the node they were created on.

Thanks for the help so far - if nothing else, I've been digging through 
the pgpool code...

Regards,
-Bryan