<div dir="ltr"><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,0)">Hello, <br><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,0)">Thanks for the info and work-around. Couple of questions more :<br><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,0)">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.<br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,0)"><br>" A workaround for the problem is using an SQL comment.<br>
If "allow_sql_comments" is off,<br>
<br>
/*a comment*/select * from table1;<br>
<br>
will be always redirected to primary (inside /* and */ is an arbitrary<br>
string). Also you can use:<br>
<br>
pgpool set allow_sql_comments to off;<br>
<br>
to set the configuration variable temporarily. "<br><br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,0)">Regards, Dinesh<br></div><div class="gmail_default" style="font-family:trebuchet ms,sans-serif;color:rgb(0,0,0)">
<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Aug 2, 2017 at 5:24 AM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">> Hello,<br>
><br>
> I would like to know whether it is possible to configure load balancing<br>
> module in pgpool II so that it will be possible to redirect requests on the<br>
> basis of tables (in the sql query) as well. For e.g., configurations like<br>
> database_redirect_preference_<wbr>list and app_name_redirect_preference_<wbr>list<br>
> gives us the granularity to redirect on the basis of database / clients.<br>
> I'm looking for something like<br>
><br>
> table_name_redirect_<wbr>preference_list =<br>
> 'table1:primary;table2:1,<wbr>table3:standby'.<br>
><br>
><br>
> I couldn't find any such configurations<br>
<br>
</span>That is not supported in Pgpool-II.<br>
<span class=""><br>
> - Is it possible to write some<br>
> extensions (custom code to do the stuff mentioned above ) and attach it to<br>
> pgpool II<br>
<br>
</span>No.<br>
<span class=""><br>
> or will it be required to branch and add that piece of code to<br>
> pgpool source and do a build / install ?<br>
<br>
</span>Yes. BTW I think you should decide what to do if table1 and table2 are<br>
used in a SELECT.<br>
<span class=""><br>
> Reason : - I have set up a postgres cluster with SR (Streaming<br>
> Replication). Due to inherent design, things are asynchronous and there<br>
> will be a read miss if a recently inserted row in master was read from the<br>
> slave. I went through the configuration sr_check_period and<br>
> delay_threshold. What will happen to requests (insert row; read same row;)<br>
> which was done under 1 second (lowest possible value for sr_check_period).<br>
> I do not want to opt for sync replication due to performance reasons and<br>
> also due to the fact that majority of the queries are "reads".<br>
><br>
> I'm aware of tables with heavy writes - Hence such a configuration / module<br>
> (if available in runtime - even better) will help me to circumvent the<br>
> problem. Of course, I'm pretty much new to pgpool II - Please correct me<br>
> I've got it completely wrong.<br>
<br>
</span>A workaround for the problem is using an SQL comment.<br>
If "allow_sql_comments" is off,<br>
<br>
/*a comment*/select * from table1;<br>
<br>
will be always redirected to primary (inside /* and */ is an arbitrary<br>
string). Also you can use:<br>
<br>
pgpool set allow_sql_comments to off;<br>
<br>
to set the configuration variable temporarily.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
</blockquote></div><br></div>