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

[Tatsuo Ishii]

Probably that is not easier than implementing
"table_name_redirect_preference_list" you proposed because you need to
parse/analyze SQL to check whether tables you do not want to redirect
to primary.

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

> Hello,
> 
> Thanks for the info and work-around. Couple of questions more :
> 
> Is there a way to programmatically achieve what is mentioned below ? i.e.
> Can I inject / intercept the sql queries of "interest" so that I can append
> an arbitrary SQL  comment to actual SQL queries to make sure that the
> request will go the primary only ? Reason - I'm aware of "heavy write
> tables" but I cannot possibly instruct the app developers to attach a SQL
> comment to such tables.
> 
> " 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. "
> 
> Regards, Dinesh
> 
> 
> On Wed, Aug 2, 2017 at 5:24 AM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > 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
>>