[pgpool-general: 5528] Using session variables within transactions breaks loadbalancing

[Tristan Foureur]

Hi,

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 :

BEGIN;
SELECT 1+1;
COMMIT;

But this one is not, and always hits the primary (even with weight 0) :

BEGIN;
SET TIME ZONE 'UTC';
SELECT 1+1;
COMMIT;

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

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.

We do need to use those session variables to set specific time zones at
times when manipulating date objects.

We're using pgpool-II version 3.6.4 (subaruboshi)

Relevant part of the configuration :

master_slave_mode = on
master_slave_sub_mode = 'stream'
log_per_node_statement = on (To check which nodes get the queries)
connection_cache = on
replication_mode = off
load_balance_mode = on

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.

Is that an internal issue or are we missing something ?

Ideally, session variables should not make pgpool decide to only use the
primary.

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