[pgpool-hackers: 3592] add a feature: dml object level load balance

[sunbiao at highgo.com]

Hi Hackers,
If sql like below:

begin ;
update tb_1 SET id = 1;
select * from tb_1 ;
select * from tb_2 ;
select * from tb_3 ;
select * from tb_4 ;
select * from tb_5 ;
commit ;

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.

i think that “update tb_1 SET id = 1” and “select * from tb_1” should be sent to primary node.
actually, tb_2 tb_3 tb_4 tb_5 can be sent to standby node. if do this, will reduce primary load.

so i made a patch to implement my idea.
when transaction start, i will initialize a list to save table name of write queries.
read queries will check the list, if find the table name in list, read queries will be sent to primary.
when transaction end, i destroy the list.

i add two parameter:

dml_object_level_load_balance = on
dml_object_level_load_balance_token_list= 'tb_t1:tb_t2,insert_tb_f_func():tb_f,tb_v:tb_v_view'

use dml_object_level_load_balance_token_list to set relationships between objects, such as trigger, function, view.
If set dml_object_level_load_balance = on, disable_load_balance_on_write should be off.

Is it possible to add this feature?


sunbiao at highgo.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200424/182e177c/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0001-dml_object_level_load_balance.patch
Type: application/octet-stream
Size: 15010 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200424/182e177c/attachment.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: dml_object_level_load_balance - TestCase.sql
Type: application/octet-stream
Size: 1689 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-hackers/attachments/20200424/182e177c/attachment-0001.obj>