From 66fa78e2b5b97c116fce914f474ef562febbfa10 Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Fri, 19 May 2023 15:27:57 +0900 Subject: [PATCH] Allow to load balance PREPARE/EXECUTE/DEALLOCATE. Previously PREPARE/EXECUTE/DEALLOCATE are not load balanced. In streaming replication/logical replication mode, they were sent to always primary node. In native replication/snapshot isolation mode,they were always sent to all nodes. Now they can be load balanced if the SQL statement prepared by PREPARE command is a read only SELECT. For this purpose following changes were made: - is_select_query() looks into "query" member if node is PrepareStmt. Also second argument "sql" (query string) is not now mandatory. If sql is NULL, warning is emitted and this function returns false. If allow_sql_comments is off and node is PrepareStmt and query is SelectStmt, is_select_query() does not return false anymore. - pool_has_function_call() looks into "query" member if node is PrepareStmt. - Add PREPARE/EXECUTE/DEALLOCATE test cases to 001.load_balance test. - Add send_prepare() function which is similar to parse_before_bind in extended query protocol case to keep up disable_load_balance_on_write rule. send_prepare() is called by SimpleQuery() when EXECUTE message is sent by frontend in SL mode so that it sends PREPARE message to primary node if it has not sent to primary because of load balance. Note that send_prepare() does nothing if the clustering mode is other than SL mode. In native replication mode or snapshot isolation mode, all backend has the same data, and there's no point to keep up disable_load_balance_on_write rule. - Remove descriptions of restrictions regarding load balance for PREPARE/EXECUTE/DEALLOCATE in "Restrictions" section in the docs. Discussion: https://www.pgpool.net/pipermail/pgpool-hackers/2023-May/004334.html --- doc.ja/src/sgml/restrictions.sgml | 9 -- doc/src/sgml/restrictions.sgml | 10 -- src/context/pool_query_context.c | 19 ++- src/protocol/pool_process_query.c | 33 +++-- src/protocol/pool_proto_modules.c | 134 ++++++++++++++++++ .../001.load_balance/expected/expected1-r | 6 + .../001.load_balance/expected/expected1-s | 4 + .../001.load_balance/expected/expected6-r | 91 +++++++++++- .../001.load_balance/expected/expected6-s | 87 +++++++++++- .../001.load_balance/expected/expected7-r | 6 - .../tests/001.load_balance/sql/6.sql | 27 ++++ .../regression/tests/001.load_balance/test.sh | 19 +-- src/utils/pool_select_walker.c | 7 + 13 files changed, 402 insertions(+), 50 deletions(-) create mode 100644 src/test/regression/tests/001.load_balance/sql/6.sql diff --git a/doc.ja/src/sgml/restrictions.sgml b/doc.ja/src/sgml/restrictions.sgml index daf7d0a39..862dc7390 100644 --- a/doc.ja/src/sgml/restrictions.sgml +++ b/doc.ja/src/sgml/restrictions.sgml @@ -530,15 +530,6 @@ - - PREPARE/EXECUTE/DEALLOCATE - - - たとえPREPAREが読み取り専用のSELECTを処理の対象にしていても、PREPARE/EXECUTE/DEALLOCATEは常にプライマリサーバに送られます。 - - - - diff --git a/doc/src/sgml/restrictions.sgml b/doc/src/sgml/restrictions.sgml index afbefe777..f1f5eb687 100644 --- a/doc/src/sgml/restrictions.sgml +++ b/doc/src/sgml/restrictions.sgml @@ -365,16 +365,6 @@ - - PREPARE/EXECUTE/DEALLOCATE - - - PREPARE/EXECUTE/DEALLOCATE are always sent to primary server - even if the query PREPARE is processing is read only SELECT. - - - - diff --git a/src/context/pool_query_context.c b/src/context/pool_query_context.c index 61dc12d20..c51ff7303 100644 --- a/src/context/pool_query_context.c +++ b/src/context/pool_query_context.c @@ -2151,6 +2151,13 @@ where_to_send_native_replication(POOL_QUERY_CONTEXT * query_context, char *query session_context = pool_get_session_context(false); backend = session_context->backend; + /* + * Check to see if we can load balance the SELECT (or any read only query + * from syntactical point of view). + */ + elog(DEBUG1, "Maybe: load balance mode: %d is_select_query: %d", + pool_config->load_balance_mode, is_select_query(node, query)); + if (pool_config->load_balance_mode && is_select_query(node, query) && MAJOR(backend) == PROTO_MAJOR_V3) @@ -2209,9 +2216,11 @@ where_to_send_native_replication(POOL_QUERY_CONTEXT * query_context, char *query } /* - * If a writing function call is used or replicate_select is true, - * we prefer to send to all nodes. + * If a writing function call is used or replicate_select is true, we + * have to send to all nodes since the function may modify database. */ + elog(DEBUG1, "Maybe sent to all node: pool_has_function_call: %d pool_config->replicate_select: %d", + pool_has_function_call(node), pool_config->replicate_select); if (pool_has_function_call(node) || pool_config->replicate_select) { pool_setall_node_to_be_sent(query_context); @@ -2228,11 +2237,17 @@ where_to_send_native_replication(POOL_QUERY_CONTEXT * query_context, char *query !pool_is_failed_transaction() && pool_get_transaction_isolation() != POOL_SERIALIZABLE)) { + elog(DEBUG1, "load balance TSTATE: %c pool_is_writing_transaction: %d pool_is_failed_transaction: %d pool_get_transaction_isolation: %d", + TSTATE(backend, MAIN_NODE_ID), + pool_is_writing_transaction(), + pool_is_failed_transaction(), + pool_get_transaction_isolation()); set_load_balance_info(query_context); } else { /* only send to main node */ + elog(DEBUG1, "unable to load balance"); pool_set_node_to_be_sent(query_context, REAL_MAIN_NODE_ID); } } diff --git a/src/protocol/pool_process_query.c b/src/protocol/pool_process_query.c index d893eca74..172b764c4 100644 --- a/src/protocol/pool_process_query.c +++ b/src/protocol/pool_process_query.c @@ -1112,6 +1112,7 @@ reset_backend(POOL_CONNECTION_POOL * backend, int qcnt) * * For followings this function returns true: * - SELECT/WITH without FOR UPDATE/SHARE + * (for PREPARE, this function checks its "query" member of PrepareStmt) * - COPY TO STDOUT * - EXPLAIN * - EXPLAIN ANALYZE and query is SELECT not including writing functions @@ -1122,20 +1123,16 @@ reset_backend(POOL_CONNECTION_POOL * backend, int qcnt) bool is_select_query(Node *node, char *sql) { + bool prepare = false; + if (node == NULL) return false; - /* - * 2009/5/1 Tatsuo says: This test is not bogus. As of 2.2, pgpool sets - * Portal->sql_string to NULL for SQL command PREPARE. Usually this is ok, - * since in most cases SQL command EXECUTE follows anyway. Problem is, - * some applications mix PREPARE with extended protocol command "EXECUTE" - * and so on. Execute() seems to think this never happens but it is not - * real. Someday we should extract actual query string from - * PrepareStmt->query and set it to Portal->sql_string. - */ if (sql == NULL) + { + elog(WARNING, "is_select_query called without SQL string"); return false; + } if (!pool_config->allow_sql_comments && pool_config->ignore_leading_white_space) { @@ -1144,6 +1141,17 @@ is_select_query(Node *node, char *sql) sql++; } + /* + * If it's PREPARE, then we check the actual query specified in "AS" + * clause. + */ + if (IsA(node, PrepareStmt)) + { + PrepareStmt *prepare_statement = (PrepareStmt *) node; + prepare = true; + node = (Node *) (prepare_statement->query); + } + if (IsA(node, SelectStmt)) { SelectStmt *select_stmt; @@ -1168,11 +1176,16 @@ is_select_query(Node *node, char *sql) } } + /* + * If SQL comment is not allowed, the query must start with cetain characters. + * However if it's PREPARE, we should skip the check. + */ if (!pool_config->allow_sql_comments) /* '\0' and ';' signify empty query */ return (*sql == 's' || *sql == 'S' || *sql == '(' || *sql == 'w' || *sql == 'W' || *sql == 't' || *sql == 'T' || - *sql == '\0' || *sql == ';'); + *sql == '\0' || *sql == ';' || + prepare); else return true; } diff --git a/src/protocol/pool_proto_modules.c b/src/protocol/pool_proto_modules.c index c1237e043..751c3da5c 100644 --- a/src/protocol/pool_proto_modules.c +++ b/src/protocol/pool_proto_modules.c @@ -92,6 +92,9 @@ static POOL_STATUS parse_before_bind(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend, POOL_SENT_MESSAGE * message, POOL_SENT_MESSAGE * bind_message); +static POOL_STATUS send_prepare(POOL_CONNECTION * frontend, + POOL_CONNECTION_POOL * backend, + POOL_SENT_MESSAGE * message); static int *find_victim_nodes(int *ntuples, int nmembers, int main_node, int *number_of_nodes); static POOL_STATUS close_standby_transactions(POOL_CONNECTION * frontend, POOL_CONNECTION_POOL * backend); @@ -719,6 +722,9 @@ SimpleQuery(POOL_CONNECTION * frontend, if (node) { + /* + * Take care of PREPARE/EXECUTE. + */ POOL_SENT_MESSAGE *msg = NULL; if (IsA(node, PrepareStmt)) @@ -727,9 +733,29 @@ SimpleQuery(POOL_CONNECTION * frontend, } else if (IsA(node, ExecuteStmt)) { + /* + * EXECUTE needs to refer to information of PREPARE. + */ msg = pool_get_sent_message('Q', ((ExecuteStmt *) node)->name, POOL_SENT_MESSAGE_CREATED); if (!msg) msg = pool_get_sent_message('P', ((ExecuteStmt *) node)->name, POOL_SENT_MESSAGE_CREATED); + else + { + /* + * Take care the case when the previous PREPARE + * message has been sent to other than primary node in + * SL mode. In this case, we send a PREPARE message to + * the primary node to keep up the + * disable_load_balance_on_write rule. + */ + if (SL_MODE && pool_config->load_balance_mode && pool_is_writing_transaction() && + TSTATE(backend, MAIN_REPLICA ? PRIMARY_NODE_ID : REAL_MAIN_NODE_ID) == 'T' && + pool_config->disable_load_balance_on_write != DLBOW_OFF) + { + if (send_prepare(frontend, backend, msg) != POOL_CONTINUE) + return POOL_END; + } + } } /* rewrite `now()' to timestamp literal */ @@ -3814,6 +3840,114 @@ static POOL_STATUS parse_before_bind(POOL_CONNECTION * frontend, return POOL_CONTINUE; } +/* + * Send PREPARE message to primary node and wait for reply if particular + * message is not yet PREPAREd on the primary node but PREPAREd on other + * node. Caller must provide the PREPARED message information as "message" + * argument. + */ +static POOL_STATUS send_prepare(POOL_CONNECTION * frontend, + POOL_CONNECTION_POOL * backend, + POOL_SENT_MESSAGE * message) +{ + int node_id; + bool backup[MAX_NUM_BACKENDS]; + POOL_QUERY_CONTEXT *qc, *new_qc; + char qbuf[1024]; + POOL_SELECT_RESULT *res; + + elog(DEBUG1, "send_prepare called"); + + if (!SL_MODE) + { + elog(DEBUG1, "send_prepare: not SL_MODE"); + return POOL_CONTINUE; + } + + /* set target backend node id */ + node_id = PRIMARY_NODE_ID; + + /* create copy of where_to_send map */ + qc = message->query_context; + memcpy(backup, qc->where_to_send, sizeof(qc->where_to_send)); + + if (message->kind != 'Q' || qc->where_to_send[node_id]) + { + ereport(DEBUG1, + (errmsg("send_prepare"), + errdetail("no need to re-send PREPARE kind: %c where_to_send: %d", message->kind, + qc->where_to_send[node_id]))); + return POOL_CONTINUE; + } + + /* + * we are in streaming replication mode and the PREPARE message has + * not been sent to primary yet. + */ + + /* + * Prepare modified query context This is a copy of original PREPARE + * query context except the query sending destination is changed to + * primary node. + */ + new_qc = pool_query_context_shallow_copy(qc); + memset(new_qc->where_to_send, 0, sizeof(new_qc->where_to_send)); + new_qc->where_to_send[node_id] = 1; + new_qc->virtual_main_node_id = node_id; + new_qc->load_balance_node_id = node_id; + + /* named statement? */ + if (message->name[0] != '\0') + { + /* + * Before sending the PREPARE message to the primary, we need to + * DEALLOCATE the named statement. Otherwise we will get an error + * from backend if an identical named statement already exists. + */ + + /* check to see if the named statement exists on primary node */ + snprintf(qbuf, sizeof(qbuf), "SELECT count(*) FROM pg_prepared_statements WHERE name = '%s'", + message->name); + + elog(DEBUG1, "send_prepare: %s to backend: %d", qbuf, node_id); + do_query(CONNECTION(backend, node_id), qbuf, &res, MAJOR(backend)); + + if (res && res->data[0] && strcmp(res->data[0], "0")) + { + free_select_result(res); + /* + * The same named statement exists, We need to send DEALLOCATE + * message + */ + snprintf(qbuf, sizeof(qbuf), "DEALLOCATE %s", message->name); + + /* send DEALLOCATE message to primary node */ + elog(DEBUG1, "send_prepare: %s to backend: %d", qbuf, node_id); + do_query(CONNECTION(backend, node_id), qbuf, &res, MAJOR(backend)); + } + free_select_result(res); + } + + /* send PREPARE message to primary node */ + elog(DEBUG1, "send_prepare: sending PREPARE to primary node"); + do_query(CONNECTION(backend, node_id), message->query_context->original_query, + &res, MAJOR(backend)); + free_select_result(res); + + /* replace the query context of PREPARE message with new query context */ + message->query_context = new_qc; + + /* + * Replace query contex in the session context with the new query context + * so that subsequent EXECUTE will be sent to primary node. + */ + pool_get_session_context(true)->query_context = new_qc; + + /* recover where_to_send map */ + memcpy(qc->where_to_send, backup, sizeof(backup)); + return POOL_CONTINUE; +} + /* * Find victim nodes by "decide by majority" rule and returns array * of victim node ids. If no victim is found, return NULL. diff --git a/src/test/regression/tests/001.load_balance/expected/expected1-r b/src/test/regression/tests/001.load_balance/expected/expected1-r index f9bc840ae..c4128e3c2 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected1-r +++ b/src/test/regression/tests/001.load_balance/expected/expected1-r @@ -10,6 +10,12 @@ NOTICE: DB node id: 1 statement: CREATE FUNCTION f1(INTEGER) returns INTEGER AS CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL; NOTICE: DB node id: 0 statement: CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL; NOTICE: DB node id: 1 statement: CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL; +CREATE FUNCTION f3(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +NOTICE: DB node id: 0 statement: CREATE FUNCTION f3(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +NOTICE: DB node id: 1 statement: CREATE FUNCTION f3(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +CREATE FUNCTION f4(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +NOTICE: DB node id: 0 statement: CREATE FUNCTION f4(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +NOTICE: DB node id: 1 statement: CREATE FUNCTION f4(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; SELECT * FROM t1; -- this load balances NOTICE: DB node id: 1 statement: SELECT * FROM t1; i diff --git a/src/test/regression/tests/001.load_balance/expected/expected1-s b/src/test/regression/tests/001.load_balance/expected/expected1-s index 029f2a25f..38b4e53c5 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected1-s +++ b/src/test/regression/tests/001.load_balance/expected/expected1-s @@ -6,6 +6,10 @@ CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL; NOTICE: DB node id: 0 statement: CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL; CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL; NOTICE: DB node id: 0 statement: CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL; +CREATE FUNCTION f3(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +NOTICE: DB node id: 0 statement: CREATE FUNCTION f3(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +CREATE FUNCTION f4(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; +NOTICE: DB node id: 0 statement: CREATE FUNCTION f4(INTEGER) returns INTEGER AS 'SELECT $1' LANGUAGE SQL STABLE; SELECT * FROM t1; -- this load balances NOTICE: DB node id: 1 statement: SELECT * FROM t1; i diff --git a/src/test/regression/tests/001.load_balance/expected/expected6-r b/src/test/regression/tests/001.load_balance/expected/expected6-r index 4ed86119a..3b76a29f0 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected6-r +++ b/src/test/regression/tests/001.load_balance/expected/expected6-r @@ -1,4 +1,4 @@ -SELECT f1(1); +SELECT f1(1); -- no load balance because volatile function NOTICE: DB node id: 0 statement: SELECT f1(1); NOTICE: DB node id: 1 statement: SELECT f1(1); f1 @@ -6,7 +6,7 @@ NOTICE: DB node id: 1 statement: SELECT f1(1); 1 (1 row) -SELECT public.f2(1); +SELECT public.f2(1); -- no load balance because volatile function NOTICE: DB node id: 0 statement: SELECT public.f2(1); NOTICE: DB node id: 1 statement: SELECT public.f2(1); f2 @@ -14,3 +14,90 @@ NOTICE: DB node id: 1 statement: SELECT public.f2(1); 1 (1 row) +SELECT f3(1); -- load balance because statble function +NOTICE: DB node id: 1 statement: SELECT f3(1); + f3 +---- + 1 +(1 row) + +SELECT public.f4(1); -- load balance because stable function +NOTICE: DB node id: 1 statement: SELECT public.f4(1); + f4 +---- + 1 +(1 row) + +PREPARE p1 AS SELECT f1(1); -- no load balance because volatile function +NOTICE: DB node id: 0 statement: PREPARE p1 AS SELECT f1(1); +NOTICE: DB node id: 1 statement: PREPARE p1 AS SELECT f1(1); +EXECUTE p1; -- no load balance because volatile function +NOTICE: DB node id: 0 statement: EXECUTE p1; +NOTICE: DB node id: 1 statement: EXECUTE p1; + f1 +---- + 1 +(1 row) + +DEALLOCATE p1; -- no load balance because volatile function +NOTICE: DB node id: 0 statement: DEALLOCATE p1; +NOTICE: DB node id: 1 statement: DEALLOCATE p1; +PREPARE p2 AS SELECT f3(1); -- load balance because stable function +NOTICE: DB node id: 1 statement: PREPARE p2 AS SELECT f3(1); +EXECUTE p2; -- load balance because stable function +NOTICE: DB node id: 1 statement: EXECUTE p2; + f3 +---- + 1 +(1 row) + +DEALLOCATE p2; -- load balance because stable function +NOTICE: DB node id: 1 statement: DEALLOCATE p2; +-- PREPARE in transaction test +BEGIN; +NOTICE: DB node id: 0 statement: BEGIN; +NOTICE: DB node id: 1 statement: BEGIN; +PREPARE p3 AS SELECT 1; -- load balance +NOTICE: DB node id: 1 statement: PREPARE p3 AS SELECT 1; +EXECUTE p3; -- load balance +NOTICE: DB node id: 1 statement: EXECUTE p3; + ?column? +---------- + 1 +(1 row) + +DEALLOCATE p3; -- load balance +NOTICE: DB node id: 1 statement: DEALLOCATE p3; +END; +NOTICE: DB node id: 1 statement: END; +NOTICE: DB node id: 0 statement: END; +-- PREPARE in writing transaction test +BEGIN; +NOTICE: DB node id: 0 statement: BEGIN; +NOTICE: DB node id: 1 statement: BEGIN; +PREPARE p3 AS SELECT 1; -- load balance +NOTICE: DB node id: 1 statement: PREPARE p3 AS SELECT 1; +SELECT f1(1); -- no load balance. writing transaction is set +NOTICE: DB node id: 0 statement: SELECT f1(1); +NOTICE: DB node id: 1 statement: SELECT f1(1); + f1 +---- + 1 +(1 row) + +-- PREPARE is re-execute and EXECUTE no load balance in SL_MODE. +-- in other mode, load balance +EXECUTE p3; +NOTICE: DB node id: 1 statement: EXECUTE p3; + ?column? +---------- + 1 +(1 row) + +-- no load balance in SL_MODE. +-- in other mode, load balance +DEALLOCATE p3; +NOTICE: DB node id: 1 statement: DEALLOCATE p3; +END; +NOTICE: DB node id: 1 statement: END; +NOTICE: DB node id: 0 statement: END; diff --git a/src/test/regression/tests/001.load_balance/expected/expected6-s b/src/test/regression/tests/001.load_balance/expected/expected6-s index bc50fecee..ff6cd776d 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected6-s +++ b/src/test/regression/tests/001.load_balance/expected/expected6-s @@ -1,14 +1,97 @@ -SELECT f1(1); +SELECT f1(1); -- no load balance because volatile function NOTICE: DB node id: 0 statement: SELECT f1(1); f1 ---- 1 (1 row) -SELECT public.f2(1); +SELECT public.f2(1); -- no load balance because volatile function NOTICE: DB node id: 0 statement: SELECT public.f2(1); f2 ---- 1 (1 row) +SELECT f3(1); -- load balance because statble function +NOTICE: DB node id: 1 statement: SELECT f3(1); + f3 +---- + 1 +(1 row) + +SELECT public.f4(1); -- load balance because stable function +NOTICE: DB node id: 1 statement: SELECT public.f4(1); + f4 +---- + 1 +(1 row) + +PREPARE p1 AS SELECT f1(1); -- no load balance because volatile function +NOTICE: DB node id: 0 statement: PREPARE p1 AS SELECT f1(1); +EXECUTE p1; -- no load balance because volatile function +NOTICE: DB node id: 0 statement: EXECUTE p1; + f1 +---- + 1 +(1 row) + +DEALLOCATE p1; -- no load balance because volatile function +NOTICE: DB node id: 0 statement: DEALLOCATE p1; +PREPARE p2 AS SELECT f3(1); -- load balance because stable function +NOTICE: DB node id: 1 statement: PREPARE p2 AS SELECT f3(1); +EXECUTE p2; -- load balance because stable function +NOTICE: DB node id: 1 statement: EXECUTE p2; + f3 +---- + 1 +(1 row) + +DEALLOCATE p2; -- load balance because stable function +NOTICE: DB node id: 1 statement: DEALLOCATE p2; +-- PREPARE in transaction test +BEGIN; +NOTICE: DB node id: 0 statement: BEGIN; +NOTICE: DB node id: 1 statement: BEGIN; +PREPARE p3 AS SELECT 1; -- load balance +NOTICE: DB node id: 1 statement: PREPARE p3 AS SELECT 1; +EXECUTE p3; -- load balance +NOTICE: DB node id: 1 statement: EXECUTE p3; + ?column? +---------- + 1 +(1 row) + +DEALLOCATE p3; -- load balance +NOTICE: DB node id: 1 statement: DEALLOCATE p3; +END; +NOTICE: DB node id: 1 statement: END; +NOTICE: DB node id: 0 statement: END; +-- PREPARE in writing transaction test +BEGIN; +NOTICE: DB node id: 0 statement: BEGIN; +NOTICE: DB node id: 1 statement: BEGIN; +PREPARE p3 AS SELECT 1; -- load balance +NOTICE: DB node id: 1 statement: PREPARE p3 AS SELECT 1; +SELECT f1(1); -- no load balance. writing transaction is set +NOTICE: DB node id: 0 statement: SELECT f1(1); + f1 +---- + 1 +(1 row) + +-- PREPARE is re-execute and EXECUTE no load balance in SL_MODE. +-- in other mode, load balance +EXECUTE p3; +NOTICE: DB node id: 0 statement: EXECUTE p3; + ?column? +---------- + 1 +(1 row) + +-- no load balance in SL_MODE. +-- in other mode, load balance +DEALLOCATE p3; +NOTICE: DB node id: 0 statement: DEALLOCATE p3; +END; +NOTICE: DB node id: 1 statement: END; +NOTICE: DB node id: 0 statement: END; diff --git a/src/test/regression/tests/001.load_balance/expected/expected7-r b/src/test/regression/tests/001.load_balance/expected/expected7-r index 7dc29441f..84719ff1b 100644 --- a/src/test/regression/tests/001.load_balance/expected/expected7-r +++ b/src/test/regression/tests/001.load_balance/expected/expected7-r @@ -44,11 +44,9 @@ NOTICE: DB node id: 1 statement: SAVEPOINT a; -- PREPARE PREPARE foo AS SELECT 2; NOTICE: DB node id: 0 statement: PREPARE foo AS SELECT 2; -NOTICE: DB node id: 1 statement: PREPARE foo AS SELECT 2; -- EXECUTE EXECUTE foo; NOTICE: DB node id: 0 statement: EXECUTE foo; -NOTICE: DB node id: 1 statement: EXECUTE foo; ?column? ---------- 2 @@ -57,7 +55,6 @@ NOTICE: DB node id: 1 statement: EXECUTE foo; -- DEALLOCATE DEALLOCATE foo; NOTICE: DB node id: 0 statement: DEALLOCATE foo; -NOTICE: DB node id: 1 statement: DEALLOCATE foo; -- ROLLBACK TO ROLLBACK TO a; NOTICE: DB node id: 1 statement: ROLLBACK TO a; @@ -83,11 +80,9 @@ NOTICE: DB node id: 1 statement: SAVEPOINT a; -- PREPARE PREPARE foo AS SELECT 2; NOTICE: DB node id: 0 statement: PREPARE foo AS SELECT 2; -NOTICE: DB node id: 1 statement: PREPARE foo AS SELECT 2; -- EXECUTE EXECUTE foo; NOTICE: DB node id: 0 statement: EXECUTE foo; -NOTICE: DB node id: 1 statement: EXECUTE foo; ?column? ---------- 2 @@ -96,7 +91,6 @@ NOTICE: DB node id: 1 statement: EXECUTE foo; -- DEALLOCATE DEALLOCATE foo; NOTICE: DB node id: 0 statement: DEALLOCATE foo; -NOTICE: DB node id: 1 statement: DEALLOCATE foo; -- ROLLBACK TO ROLLBACK TO a; NOTICE: DB node id: 1 statement: ROLLBACK TO a; diff --git a/src/test/regression/tests/001.load_balance/sql/6.sql b/src/test/regression/tests/001.load_balance/sql/6.sql new file mode 100644 index 000000000..6a53b05a1 --- /dev/null +++ b/src/test/regression/tests/001.load_balance/sql/6.sql @@ -0,0 +1,27 @@ +SELECT f1(1); -- no load balance because volatile function +SELECT public.f2(1); -- no load balance because volatile function +SELECT f3(1); -- load balance because statble function +SELECT public.f4(1); -- load balance because stable function +PREPARE p1 AS SELECT f1(1); -- no load balance because volatile function +EXECUTE p1; -- no load balance because volatile function +DEALLOCATE p1; -- no load balance because volatile function +PREPARE p2 AS SELECT f3(1); -- load balance because stable function +EXECUTE p2; -- load balance because stable function +DEALLOCATE p2; -- load balance because stable function +-- PREPARE in transaction test +BEGIN; +PREPARE p3 AS SELECT 1; -- load balance +EXECUTE p3; -- load balance +DEALLOCATE p3; -- load balance +END; +-- PREPARE in writing transaction test +BEGIN; +PREPARE p3 AS SELECT 1; -- load balance +SELECT f1(1); -- no load balance. writing transaction is set +-- PREPARE is re-execute and EXECUTE no load balance in SL_MODE. +-- in other mode, load balance +EXECUTE p3; +-- no load balance in SL_MODE. +-- in other mode, load balance +DEALLOCATE p3; +END; diff --git a/src/test/regression/tests/001.load_balance/test.sh b/src/test/regression/tests/001.load_balance/test.sh index 2854e4bf6..20262a178 100755 --- a/src/test/regression/tests/001.load_balance/test.sh +++ b/src/test/regression/tests/001.load_balance/test.sh @@ -67,6 +67,8 @@ CREATE TABLE t1(i INTEGER); CREATE TABLE t2(i INTEGER); CREATE FUNCTION f1(INTEGER) returns INTEGER AS 'SELECT \$1' LANGUAGE SQL; CREATE FUNCTION f2(INTEGER) returns INTEGER AS 'SELECT \$1' LANGUAGE SQL; +CREATE FUNCTION f3(INTEGER) returns INTEGER AS 'SELECT \$1' LANGUAGE SQL STABLE; +CREATE FUNCTION f4(INTEGER) returns INTEGER AS 'SELECT \$1' LANGUAGE SQL STABLE; SELECT * FROM t1; -- this load balances SELECT f1(1); -- this does not load balance SELECT public.f2(1); -- this does not load balance @@ -115,7 +117,7 @@ EOF echo "read_only_function_list = ''" >> etc/pgpool.conf echo "write_function_list = ''" >> etc/pgpool.conf echo "statement_level_load_balance = on" >> etc/pgpool.conf - echo "log_min_messages = debug1" >> etc/pgpool.conf + echo "log_min_messages = debug5" >> etc/pgpool.conf ./startall sleep $st @@ -171,18 +173,18 @@ EOF # function is regarded doing writes. # Since f1() and f2() were declared without volatility property, they are regarded # as volatile functions. +# +# Also check PREPARE/EXECUTE/DEALLOCATE in this test. # ------------------------------------------------------------------------------- echo "load_balance_mode = on" >> etc/pgpool.conf echo "write_function_list = ''" >> etc/pgpool.conf echo "read_only_function_list = ''" >> etc/pgpool.conf - ./pgpool_reload - sleep $st + ./shutdownall + ./startall + wait_for_pgpool_startup - $PSQL $PSQLOPTS > result6 2>&1 < result6 2>&1 + check_result 6 echo "=== test7 started ===" # ------------------------------------------------------------------------------- @@ -193,7 +195,6 @@ EOF # So following does not work. #echo "primary_routing_query_pattern_list = ''" >> etc/pgpool.conf sed -i '/^primary_routing_query_pattern_list/d' etc/pgpool.conf - #echo "log_min_messages = debug5" >> etc/pgpool.conf ./shutdownall ./startall diff --git a/src/utils/pool_select_walker.c b/src/utils/pool_select_walker.c index 01fcd52a6..6dfaeb602 100644 --- a/src/utils/pool_select_walker.c +++ b/src/utils/pool_select_walker.c @@ -59,12 +59,19 @@ static bool function_has_return_type(char *fname, char *typename); * Return true if this SELECT has function calls *and* supposed to * modify database. We check write/read_only function list to determine * whether the function modifies database. + * If node is PrepareStmt, we look into PrepareStmt->query instead. */ bool pool_has_function_call(Node *node) { SelectContext ctx; + if (IsA(node, PrepareStmt)) + { + PrepareStmt *prepare_statement = (PrepareStmt *) node; + node = (Node *) (prepare_statement->query); + } + if (!IsA(node, SelectStmt)) return false; -- 2.39.5