[pgpool-general: 5542] Re: Using session variables within transactions breaks loadbalancing
Tatsuo Ishii
ishii at sraoss.co.jp
Wed May 31 11:21:57 JST 2017
> 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
More information about the pgpool-general
mailing list