From 3f3c165698d8ded0a96d24ee93b32bc6973dab6d Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@sraoss.co.jp>
Date: Sat, 10 Feb 2024 11:50:28 +0900
Subject: [PATCH] Fix statement_level_load_balance with BEGIN etc.

When statement_level_load_balance is enabled,
BEGIN/END/COMMIT/ABORT/SET/SAVEPOINT/RELEASE SAVEPOINT/DEALLOCATE
ALL/DISCARD were sent to primary node and all standby nodes even if
load_balance_mode is off. This is not only plain wrong but caused slow
down if one of the standby nodes are in remote network. Fix this in
that pgpool sends such queries to primary node only when
load_balance_mode is off.

Note that if load_balance_mode is on and statement_level_load_balance
is on, such queries are sent to all nodes as before. This is
necessary. For example, suppose there are 2 PostgreSQL nodes 0 and
1. An explicit transaction starts followed by two read only
SELECTs. The first SELECT is sent to node 0 because the node 0 is
chosen as the load balance node. The second SELECT is sent to node 1
because the node 1 is chosen as the load balance node. If pgpool has
not sent BEGIN to both node 0 and 1 when the transaction started, the
first or the second SELECT will be executed outside the transaction,
which is not an expected behavior. However this may bring slow down
mentioned above. I guess this has been less known to users and I
decided to add some notes to the statement_level_load_balance doc.

Reported: [pgpool-general: 8998] https://www.pgpool.net/pipermail/pgpool-general/2024-January/009059.html
Discussion: [pgpool-hackers: 4422] https://www.pgpool.net/pipermail/pgpool-hackers/2024-February/004423.html
Backpatch-through: v4.1
---
 doc.ja/src/sgml/loadbalance.sgml | 28 ++++++++++++++++++++++++++--
 doc/src/sgml/loadbalance.sgml    | 22 ++++++++++++++++++++--
 src/context/pool_query_context.c | 30 ++++++++++++++++++++++--------
 3 files changed, 68 insertions(+), 12 deletions(-)

diff --git a/doc.ja/src/sgml/loadbalance.sgml b/doc.ja/src/sgml/loadbalance.sgml
index 7d932965f..329f82c13 100644
--- a/doc.ja/src/sgml/loadbalance.sgml
+++ b/doc.ja/src/sgml/loadbalance.sgml
@@ -479,6 +479,11 @@
 	 DEALLOCATE ALL
 	</para>
        </listitem>
+       <listitem>
+	<para>
+	 SAVEPOINT (そしてRELEASE SAVEPOINTのような関連コマンド)
+	</para>
+       </listitem>
       </itemizedlist>
      </para>
     </listitem>
@@ -1603,7 +1608,7 @@ dml_adaptive_object_relationship_list = 'table_1:table_2'
     <listitem>
      <para>
       <!--
-      When set to on, the load balancing node is decided for each read query.
+      When set to on and <xref linkend="guc-load-balance-mode"> is set to on, the load balancing node is decided for each read query.
       When set to off, load balancing node is decided at the session start time
       and will not be changed until the session ends.
       For example, in applications that use connection pooling remain connections
@@ -1613,13 +1618,32 @@ dml_adaptive_object_relationship_list = 'table_1:table_2'
       it is possible to decide load balancing node per query, not per session.
       The default is off.
       -->
-      onに設定すると、参照クエリごとに負荷分散先を決めます。
+      onに設定し、<xref linkend="guc-load-balance-mode">がonに設定されていると、参照クエリごとに負荷分散先を決めます。
       offに設定すると、セッションが始まるときに決められた負荷分散先がセッションが終了するまで変更されません。
       例えば、コネクションプールを利用し、バックエンドサーバに接続したままのようなアプリケーションの場合、
       セッションが長い間保持される可能性があるので、セッションが終了するまで負荷分散先のノードが変わりません。
       このようなアプリケーションでは、<varname>statement_level_load_balance</varname>を有効にすると、
       セッションごとではなく、クエリごとに負荷分散先を決めることが可能です。デフォルトはoffです。
      </para>
+     <note>
+      <para>
+<!--
+       In streaming replication mode, certain kind of queries such as
+       BEGIN/END/COMMIT/ABORT/SET/SAVEPOINT/RELEASE
+       SAVEPOINT/DEALLOCATE ALL/DISCARD are sent to primary node and
+       load balance node. If
+       <xref linkend="guc-statement-level-load-balance"> is on, such
+       queries are sent to all standby nodes as well. This is not
+       usually a problem. But when one of standbys are in remote
+       network, the network latency may cause significant slow down in
+       case of such queries.
+-->
+       streaming replication modeでは、BEGIN/END/COMMIT/ABORT/SET/SAVEPOINT/RELEASE SAVEPOINT/DEALLOCATE ALL/DISCARDのようなクエリはプライマリノードとロードバランスノードに送られます。
+       <xref linkend="guc-statement-level-load-balance">がonなら、そうしたクエリはすべてのスタンバイノードにも送られます
+       これは通常問題になりません。
+       しかしスタンバイノードの一つが遠隔ネットワークにあると、ネットワーク遅延のためにこうしたクエリで大きな速度低下を起こすことがあります。
+      </para>
+     </note>
      <para>
       <!--
       This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
diff --git a/doc/src/sgml/loadbalance.sgml b/doc/src/sgml/loadbalance.sgml
index fd0e0d170..c09a5dbf2 100644
--- a/doc/src/sgml/loadbalance.sgml
+++ b/doc/src/sgml/loadbalance.sgml
@@ -388,6 +388,11 @@
 	 DEALLOCATE ALL
 	</para>
        </listitem>
+       <listitem>
+	<para>
+	 SAVEPOINT (and related commands such as RELEASE SAVEPOINT)
+	</para>
+       </listitem>
       </itemizedlist>
      </para>
     </listitem>
@@ -1155,16 +1160,29 @@ dml_adaptive_object_relationship_list = 'table_1:table_2'
     </term>
     <listitem>
      <para>
-      When set to on, the load balancing node is decided for each read query.
+      When set to on and <xref linkend="guc-load-balance-mode"> is set to on, the load balancing node is decided for each read query.
       When set to off, load balancing node is decided at the session start time
       and will not be changed until the session ends.
       For example, in applications that use connection pooling remain connections
       open to the backend server, because the session may be held for a long time,
       the load balancing node does not change until the session ends.
-      In such applications, When <varname>statement_level_load_balance</varname> is enabled,
+      In such applications, when <varname>statement_level_load_balance</varname> is enabled,
       it is possible to decide load balancing node per query, not per session.
       The default is off.
      </para>
+
+     <note>
+      <para>
+       In streaming replication mode, certain kind of queries such as
+       BEGIN/END/COMMIT/ABORT/SET/SAVEPOINT/RELEASE SAVEPOINT/DEALLOCATE ALL/DISCARD are sent to
+       primary node and load balance node. If
+       <xref linkend="guc-statement-level-load-balance"> is on, such
+       queries are sent to all standby nodes as well. This is not
+       usually a problem. But when one of standbys are in remote
+       network, the network latency may cause significant slow down in
+       case of such queries.
+      </para>
+     </note>
      <para>
       This parameter can be changed by reloading the <productname>Pgpool-II</> configurations.
      </para>
diff --git a/src/context/pool_query_context.c b/src/context/pool_query_context.c
index 50d15e33e..3381fb42a 100644
--- a/src/context/pool_query_context.c
+++ b/src/context/pool_query_context.c
@@ -4,7 +4,7 @@
  * pgpool: a language independent connection pool server for PostgreSQL
  * written by Tatsuo Ishii
  *
- * Copyright (c) 2003-2023	PgPool Global Development Group
+ * Copyright (c) 2003-2024	PgPool Global Development Group
  *
  * Permission to use, copy, modify, and distribute this software and
  * its documentation for any purpose and without fee is hereby
@@ -240,14 +240,28 @@ pool_setall_node_to_be_sent(POOL_QUERY_CONTEXT * query_context)
 		if (private_backend_status[i] == CON_UP ||
 			(private_backend_status[i] == CON_CONNECT_WAIT))
 		{
-			/*
-			 * In streaming replication mode, if the node is not primary node
-			 * nor load balance node, there's no point to send query.
-			 */
-			if (SL_MODE && !pool_config->statement_level_load_balance &&
-				i != PRIMARY_NODE_ID && i != sc->load_balance_node_id)
+			if (SL_MODE)
 			{
-				continue;
+				/*
+				 * If load balance mode is disabled, only send to the primary node.
+				 * or send to the main node if primary node does not exist.
+				 */
+				if (!pool_config->load_balance_mode)
+				{
+					if (i == PRIMARY_NODE_ID ||
+						(PRIMARY_NODE_ID < 0 && MAIN_NODE_ID == i))
+						query_context->where_to_send[i] = true;
+					break;
+				}
+				else
+					/*
+					 * If the node is not primary node nor load balance node,
+					 * there's no point to send query except statement load
+					 * balance is enabled.
+					 */
+					if (!pool_config->statement_level_load_balance &&
+						i != PRIMARY_NODE_ID && i != sc->load_balance_node_id)
+						continue;
 			}
 			query_context->where_to_send[i] = true;
 		}
-- 
2.39.5