[Pgpool-general] Problem with DBI and Load Balancing

[Tatsuo Ishii]

> I've been trying out master-slave and load-balancing mode with DBI, the Perl 
> database interface (and DBD::Pg).  All queries seem to go to the master.
> 
> The problem seems to be that DBI (or DBD::Pg, I don't know which), inserts 
> whitespace before the query:
> 
> Dec 18 12:14:56 kutta postgres[29981]: [4-1] LOG:  statement: SELECT 
> version()
> Dec 18 12:14:56 kutta postgres[29981]: [5-1] LOG:  statement: 
> SELECT host from host
> Dec 18 12:14:56 kutta postgres[29981]: [6-1] LOG:  statement:  RESET ALL
> Dec 18 12:14:56 kutta postgres[29981]: [7-1] LOG:  statement:  SET SESSION 
> AUTHORIZATION DEFAULT
> 
> Note the spaces before my statement, "SELECT host from host", which I didn't 
> introduce.  I imagine that's what's causing pgpool to send the query only to 
> the master.  The significance of leading whitespace is clearly documented on 
> the pgpool page and is obvious from the code.
> 
> /*
>  * return non 0 if load balance is possible
>  */
> static int load_balance_enabled(POOL_CONNECTION_POOL *backend, char *sql)
> {
>  if (pool_config.load_balance_mode &&
>   DUAL_MODE &&
>   MAJOR(backend) == PROTO_MAJOR_V3 &&
>   TSTATE(backend) == 'I' &&
>   !strncasecmp(sql, "SELECT", 6))
>   return 1;
>  return 0;
> }
> 
> Would it be possible to have an ignore_leading_whitespace option (or even 
> default to ignoring leading whitespace) for compatibility please?

I will add ignore_leading_whitespace option to next version (probably
V2.7).
--
Tatsuo Ishii
SRA OSS, Inc. Japan