<div dir="ltr"><div class="gmail_quote"><div dir="ltr">Hi,<br><br>We've noticed a strange issue where some of our transactions were never loadbalanced. We're using master/slave mode using stream replication. We want to balance our read queries between our read replicas. The following query is load balanced randomly according to weight :<div><br></div>BEGIN;<br>SELECT 1+1;<br>COMMIT;<br><br>But this one is not, and always hits the primary (even with weight 0) :<br><br>BEGIN;<br>SET TIME ZONE 'UTC';<br>SELECT 1+1;<br>COMMIT;<div><br></div>Yet the documentation says : "In pgpool-II 3.0 or later, SELECT will be load balanced even in a transaction if operated in the master/slave mode."
<div><br></div><div>It seems that setting session variables like this one above makes pgpool send everything to the primary. But this session variable has no write impact on the server. We've tried playing a bit with different values of white_function_list and black_function_list, but without any success.<br><br>We do need to use those session variables to set specific time zones at times when manipulating date objects.</div><div><br></div><div>We're using pgpool-II version 3.6.4 (subaruboshi)</div><div><br></div><div>Relevant part of the configuration : </div><div><br></div><div>master_slave_mode = on<br></div><div>master_slave_sub_mode = 'stream'<br></div><div>log_per_node_statement = on (To check which nodes get the queries)<br></div><div>connection_cache = on<br></div><div>replication_mode = off<br></div><div>load_balance_mode = on<br></div><div><br></div><div>For white/black function list we've tried various options and all the examples given in the doc, but we still can't fix this.</div><div><br></div><div>Is that an internal issue or are we missing something ? </div><div><br></div><div>Ideally, session variables should not make pgpool decide to only use the primary.</div><div><br></div><div>Thanks</div></div>
</div><br></div>