<html><head><meta http-equiv="content-type" content="text/html; charset=UTF-8"><style>body { line-height: 1.5; }blockquote { margin-top: 0px; margin-bottom: 0px; margin-left: 0.5em; }div.FoxDiv20200615102122028595 { }body { font-size: 10.5pt; font-family: 'Microsoft YaHei UI'; color: rgb(0, 0, 0); line-height: 1.5; }</style></head><body>
<div><div>Hi, Usama</div><div>I found a problem in patch v2.</div><div>I can not use “pool show” to show new added parameter.</div><div>So i made patch v3.</div><div><br></div><div>I make disable_load_balance_on_write to accept new value.</div><div>Set disable_load_balance_on_write = 'dml_load_balance' to enable this feature.</div><div>This new patch contains a test script in path ‘src/test/dml-load-balance-test’.</div><div>If pg installed by default in /usr/local/pgsql, just execute test.sh.</div><div>If pg is in other dir, execute ‘test.sh -p /path_to_pg_dir/’.</div><div>It will show “success: dml load balance test pass.” , when test pass.</div><div><br></div><div>this script will test below sql:</div><div>show pool_nodes;</div><div><br></div><div>-- test DML</div><div>begin ;</div><div>insert into tb_dml_insert values (1);</div><div>select * from tb_dml_insert ;</div><div>commit ;</div><div><br></div><div>begin ;</div><div>update tb_dml_update SET a = 2;</div><div>select * from tb_dml_update ;</div><div>commit ;</div><div><br></div><div>begin ;</div><div>delete from tb_dml_delete;</div><div>select * from tb_dml_delete;</div><div>commit ;</div><div><br></div><div>-- test trigger</div><div>begin ;</div><div>insert into tb_t1 values (1);</div><div>select * from tb_t2 ;</div><div>commit ;</div><div><br></div><div>-- test function</div><div>begin ;</div><div>select insert_tb_f_func(6);</div><div>select * from tb_f ;</div><div>commit ;</div><div><br></div><div>-- test view</div><div>begin ;</div><div>insert into tb_v values (8);</div><div>select * from tb_v_view ;</div><div>commit ;</div><div><br></div><div><div>Thanks</div><div>Best Regards</div></div><span></span></div><hr style="width: 210px; height: 1px;" color="#b5c4df" size="1" align="left">
<div><span><div style="MARGIN: 10px; FONT-FAMILY: verdana; FONT-SIZE: 10pt"><div>sunbiao@highgo.com</div></div></span></div>
<blockquote style="margin-Top: 0px; margin-Bottom: 0px; margin-Left: 0.5em; margin-Right: inherit"><div> </div><div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm"><div style="PADDING-RIGHT: 8px; PADDING-LEFT: 8px; FONT-SIZE: 12px;FONT-FAMILY:tahoma;COLOR:#000000; BACKGROUND: #efefef; PADDING-BOTTOM: 8px; PADDING-TOP: 8px"><div><b>From:</b> <a href="mailto:m.usama@gmail.com">Muhammad Usama</a></div><div><b>Date:</b> 2020-06-12 18:08</div><div><b>To:</b> <a href="mailto:sunbiao@highgo.com">sunbiao@highgo.com</a></div><div><b>CC:</b> <a href="mailto:pgpool-hackers@pgpool.net">pgpool-hackers</a></div><div><b>Subject:</b> Re: [pgpool-hackers: 3592] add a feature: dml object level load balance</div></div></div><div><div class="FoxDiv20200615102122028595"><div dir="ltr">Hi Sunbiao,<div><br></div><div>Can you kindly resend the <span style="color:rgb(0,0,0);white-space:pre-wrap">0001-dml-load-balance-patch-v2.patch </span>patch? I am not able to download the patch file.</div><div><br></div><div>Thanks</div><div>Best regards</div><div>Muhammad Usama</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Apr 24, 2020 at 9:55 PM Muhammad Usama <<a href="mailto:m.usama@gmail.com">m.usama@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi Sunbiao,<br><br>Thanks for the patch and it looks line an interesting feature. I have just skimmed<br>through the patch and I have a few of small comments.<br><br>1 - Wouldn't it be better to add a new mode existing disable_load_balance_on_write parameter<br>instead of adding a new configuration parameter i.e dml_object_level_load_balance ?<br>You can make disable_load_balance_on_write to accept new value like<br>disable_load_balance_on_write = 'object' to enable this feature.<br><br>2- The patch contains some invalid changes in pgpool.conf.sample-stream file<br>i.e it changes the default values of black_function_list and disable_load_balance_on_write<br>configuration parameters which I am sure were not intended<br><br>3- The default value for new configuration parameter dml_object_level_load_balance_token_list<br>better be an empty<br><br>4- Instead of attaching a separate test script you could include a proper test case for the feature.<br><br>Thanks<br>Best Regards<br><br><div><font color="#666666">Muhammad Usama<br>Highgo Software (Canada/China/Pakistan) <br>URL : <a href="http://www.highgo.ca" target="_blank">http://www.highgo.ca</a> <br>ADDR: 10318 WHALLEY BLVD, Surrey, BC </font><div><br></div></div></div><br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Apr 24, 2020 at 3:43 PM <a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a> <<a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div>
<div><span></span><div>Hi Hackers,</div><div>If sql like below:</div><div><br></div><div>begin ;</div><div>update tb_1 SET id = 1;</div><div>select * from tb_1 ;</div><div>select * from tb_2 ;</div><div>select * from tb_3 ;</div><div>select * from tb_4 ;</div><div>select * from tb_5 ;</div><div>commit ;</div><div><br></div><div>when set disable_load_balance_on_write = 'transaction'. write queries appear in an explicit transaction, subsequent read queries are not load balanced until the transaction ends. so all sql will be sent to primary node.</div><div><br></div><div>i think that “update tb_1 SET id = 1” and “select * from tb_1” should be sent to primary node.</div><div>actually, tb_2 tb_3 tb_4 tb_5 can be sent to standby node. if do this, will reduce primary load.</div><div><br></div><div>so i made a patch to implement my idea.</div><div>when transaction start, i will initialize a list to save table name of write queries.</div><div>read queries will check the list, if find the table name in list, read queries will be sent to primary.</div><div>when transaction end, i destroy the list.</div><div><br></div><div>i add two parameter:</div><div><br></div><div>dml_object_level_load_balance = on</div><div>dml_object_level_load_balance_token_list= 'tb_t1:tb_t2,insert_tb_f_func():tb_f,tb_v:tb_v_view'</div><div><br></div><div>use dml_object_level_load_balance_token_list to set relationships between objects, such as trigger, function, view.</div><div>If set dml_object_level_load_balance = on, disable_load_balance_on_write should be off.</div><div><br></div><div>Is it possible to add this feature?</div></div><hr style="width:210px;height:1px" color="#b5c4df" size="1" align="left">
<div><span><div style="margin:10px;font-family:verdana;font-size:10pt"><div><a href="mailto:sunbiao@highgo.com" target="_blank">sunbiao@highgo.com</a></div></div></span></div>
</div>_______________________________________________<br>
pgpool-hackers mailing list<br>
<a href="mailto:pgpool-hackers@pgpool.net" target="_blank">pgpool-hackers@pgpool.net</a><br>
<a href="http://www.pgpool.net/mailman/listinfo/pgpool-hackers" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-hackers</a><br>
</blockquote></div>
</blockquote></div>
</div></div></blockquote>
</body></html>