[pgpool-general: 5672] Re: How to extend load balancing in pgpool II

[Tatsuo Ishii]

> Hello,
> 
> I would like to know whether it is possible to configure load balancing
> module in pgpool II so that it will be possible to redirect requests on the
> basis of tables (in the sql query) as well. For e.g., configurations like
> database_redirect_preference_list and app_name_redirect_preference_list
> gives us the granularity to redirect on the basis of database / clients.
> I'm looking for something like
> 
> table_name_redirect_preference_list =
> 'table1:primary;table2:1,table3:standby'.
> 
> 
> I couldn't find any such configurations

That is not supported in Pgpool-II.

> - Is it possible to write some
> extensions (custom code to do the stuff mentioned above ) and attach it to
> pgpool II

No.

> or will it be required to branch and add that piece of code to
> pgpool source and do a build / install ?

Yes. BTW I think you should decide what to do if table1 and table2 are
used in a SELECT.

> Reason : - I have set up a postgres cluster with SR (Streaming
> Replication). Due to inherent design, things are asynchronous and there
> will be a read miss if a recently inserted row in master was read from the
> slave. I went through the configuration sr_check_period and
> delay_threshold. What will happen to requests (insert row; read same row;)
> which was done under 1 second (lowest possible value for sr_check_period).
> I do not want to opt for sync replication due to performance reasons and
> also due to the fact that majority of the queries are "reads".
> 
> I'm aware of tables with heavy writes - Hence such a configuration / module
> (if available in runtime - even better) will help me to circumvent the
> problem. Of course, I'm pretty much new to pgpool II - Please correct me
> I've got it completely wrong.

A workaround for the problem is using an SQL comment.
If "allow_sql_comments" is off,

/*a comment*/select * from table1;

will be always redirected to primary (inside /* and */ is an arbitrary
string). Also you can use:

pgpool set allow_sql_comments to off;

to set the configuration variable temporarily.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp