[pgpool-hackers: 4422] statement_level_load_balance does not honor load_balance_mode

[Tatsuo Ishii]

While taking care of this:
[pgpool-general: 8998] Massive performance and latency issues when remote replica is attached to cluster.
https://www.pgpool.net/pipermail/pgpool-general/2024-January/009059.html

I found that statement_level_load_balance does not honor
load_balance_mode. if statement_level_load_balance is on,
pool_setall_node_to_be_sent(), which is responsible to decide where to
send queries such as SET, BEGIN, END, SAVEPOINT etc., sets
the query_context->where_to_send[] map to all live nodes even if
load_balance_mode is off. When load_balance_mode is off, pgpool only
sends queries to the primary node in streaming replication mode, thus
it's just a waste of resource to send queries to all live
nodes. Unfortunately in the case above, the user has one of the
standby nodes in a remote network, and sending queries to the remote
node causes a slow down due to the network latency.

t-ishii$ psql -p 11000 test
psql (16.0)
Type "help" for help.

test=# show pool_nodes;
 node_id | hostname  | port  | status | pg_status | lb_weight |  role   | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+-----------+-------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | localhost | 11002 | up     | up        | 0.333333  | primary | primary | 0          | true              | 0                 |                   |                        | 2024-02-07 11:29:59
 1       | localhost | 11003 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 | streaming         | async                  | 2024-02-07 11:29:59
 2       | localhost | 11004 | up     | up        | 0.333333  | standby | standby | 0          | false             | 0                 | streaming         | async                  | 2024-02-07 11:29:59
(3 rows)

test=# pgpool show load_balance_mode;
 load_balance_mode 
-------------------
 off
(1 row)

test=# pgpool show statement_level_load_balance;
 statement_level_load_balance 
------------------------------
 on
(1 row)

test=# begin;
NOTICE:  DB node id: 0 statement: begin;
NOTICE:  DB node id: 1 statement: begin;
NOTICE:  DB node id: 2 statement: begin;
BEGIN

Attached is a patch against pool_setall_node_to_be_sent() to add
checking of load_balance_mode. After the patch "begin" is sent to only
primary node even if statement_level_load_balance is on,

test=# begin;
NOTICE:  DB node id: 0 statement: begin;
BEGIN

Comments/suggestions are welcome.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: statement_level_load_balance.patch
Type: text/x-patch
Size: 1421 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20240207/a0252136/attachment.bin>