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

[Tatsuo Ishii]

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

Yes, currently Pgpool-II redirects all SELECTs to primary if other
than SELECT appears in a transaction because some of SET commands
actually affect to subsequent SELECTs (for example, SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE). Probably we should work a little bit
harder to recognize "safe" SET commands.

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

For a work aroud, can you do the SET command before BEGIN?

> 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