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

[Tatsuo Ishii]

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

Yeah, I wonder why we need to send BEGIN/COMMIT to standby when
load_balance is turned off.

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

It seems the code tries to do what you said.  There are many places
calling pool_create_sent_message().  In my understanding the function
tries to remember where Parse/Bind messges have been sent. For
example, Parse() calles like this:

		msg = pool_create_sent_message('P', len, contents, 0, name, query_context);

Afterward Bind() tries to extract the data:

	parse_msg = pool_get_sent_message('Q', pstmt_name);

Aafter several lines Bind() calls this.

	pool_where_to_send(query_context, query_context->original_query,
					   query_context->parse_tree);

I think problem is here.

pool_where_to_send() decides to choose node(s) bind messages should be
sent to. The function has been called when Parse() gets called to
determine the destination node as well. pool_where_to_send() makes the
decision by using current condition, inckuding transaction state. And
Bind() does the same job by calling pool_where_to_send(). But what if
the condition has been changed between Parse() and Bind()? It is
possible that desitination node could be different and bind message
might be sent to a node where parse never gets executed. I think at
least part of problems you are having comes from this. Bind() should
not call pool_where_to_send() instead inherits the desitination node
from Parse() in my opinion.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp