From 83906d1d5024e5f68ef9dd9dff9fda7f5720be4e Mon Sep 17 00:00:00 2001 From: Bo Peng Date: Thu, 14 Jun 2018 14:27:53 +0900 Subject: [PATCH] Add new feature to enable specifying SQL patterns lists that should not be load-balanced. Even though currently we can do this by adding the /*NO LOAD BALANCE*/ comment to queries, this requires modifying application codes and this is not always possible. This feature enables specifying SQL patterns lists that should not be load-balanced. ------------- New parameter ------------- black_query_pattern_list = '' You can specify a semicolon separated list of SQL patterns that should be sent to primary node only. SQL that matched patterns specified in this list are not load balanced. Only Maste Slave mode is supported. You can use regular expression to match SQL patterns, to which ^ and $ are automatically added. When using characters such as "'", ";" or "*" in SQL patterns, you need to escape them using "\". --- doc.ja/src/sgml/loadbalance.sgml | 94 +++++++++++++++ doc/src/sgml/loadbalance.sgml | 65 ++++++++++ src/config/pool_config.l | 31 ++++- src/config/pool_config_variables.c | 111 +++++++++++++++++- src/context/pool_query_context.c | 12 +- src/include/pool_config.h | 7 ++ src/sample/pgpool.conf.sample | 7 +- src/sample/pgpool.conf.sample-logical | 6 + src/sample/pgpool.conf.sample-master-slave | 6 + src/sample/pgpool.conf.sample-replication | 6 + src/sample/pgpool.conf.sample-stream | 6 + .../regression/tests/001.load_balance/test.sh | 45 +++++++ src/utils/pool_process_reporting.c | 13 ++ src/utils/pool_select_walker.c | 6 + 14 files changed, 410 insertions(+), 5 deletions(-) diff --git a/doc.ja/src/sgml/loadbalance.sgml b/doc.ja/src/sgml/loadbalance.sgml index 96c790692..0e6100bb0 100644 --- a/doc.ja/src/sgml/loadbalance.sgml +++ b/doc.ja/src/sgml/loadbalance.sgml @@ -743,6 +743,100 @@ black_function_list = 'nextval,setval,lastval,currval' +このパラメータはPgpool-IIの設定を再読み込みすることで変更可能です。 + + + + + + black_query_pattern_list (string) + + + black_query_pattern_list 設定パラメータ + + + + + +特定の SQL をプライマリノードに送信するように を設定します。 +SQL パターンをセミコロン区切りで指定します。 +マスタースレーブモードのみで動作します。 + + + +SQL のマッチングに正規表現を使うことができます。 +正規表現には自動的に^と$が付与されます。 +"'"、";" あるいは "*" などの文字を SQL パターンの中で利用する場合、"\" でエスケープする必要があります。 + + + + + 正規表現の使用 + + +もし以下の SQL をプライマリノードに送信したい場合、を以下のように設定可能です。 + + + + + SELECT * FROM table_name1; + + + + + SELECT * FROM table_name2 WHERE col LIKE '%a%'; + + + + + + table_name3 という文字列を含む SQL + + + + + + + +black_query_pattern_list = 'SELECT \* FROM table_name1\;;SELECT col1, col2 FROM table_name2 WHERE col1 LIKE \\'%a%\\'\;;.*table_name3.*' + + + + + + + +との両方にマッチした場合、 +の設定が無視され、プライマリノードのみに送信されます。 + + + + + このパラメータはPgpool-IIの設定を再読み込みすることで変更可能です。 diff --git a/doc/src/sgml/loadbalance.sgml b/doc/src/sgml/loadbalance.sgml index 18db5af81..90a7cb39c 100644 --- a/doc/src/sgml/loadbalance.sgml +++ b/doc/src/sgml/loadbalance.sgml @@ -583,6 +583,71 @@ black_function_list = 'nextval,setval,lastval,currval' + + black_query_pattern_list (string) + + black_query_pattern_list configuration parameter + + + + + Specifies a semicolon separated list of SQL patterns that + should be sent to primary node. + SQL that matched patterns specified in this list are + not load balanced. + Only Maste Slave mode is supported. + + + You can use regular expression to match SQL patterns, + to which ^ and $ are automatically added. + When using characters such as "'", ";" or "*" in SQL patterns, you need to escape them by using "\". + + + + Using regular expression + + If the following SQL should be sent to the primary node only, You can + set the like below: + + + + SELECT * FROM table_name1; + + + + + SELECT * FROM table_name2 WHERE col LIKE '%a%'; + + + + + SQL including table_nama3 + + + + + + + +black_query_pattern_list = 'SELECT \* FROM table_name1\;;SELECT col1, col2 FROM table_name2 WHERE col1 LIKE \\'%a%\\'\;;.*table_name3.*' + + + + + + + If SQL matches both and + , + setting is ignored and the SQL should be sent only to the primary node. + + + + + This parameter can be changed by reloading the Pgpool-II configurations. + + + + database_redirect_preference_list (string) diff --git a/src/config/pool_config.l b/src/config/pool_config.l index 082637c21..ddff3af2e 100644 --- a/src/config/pool_config.l +++ b/src/config/pool_config.l @@ -135,7 +135,8 @@ int add_regex_pattern(const char *type, char *s) regex_flags |= REG_EXTENDED; /* Fill the pattern type */ if (strcmp(type, "black_function_list") == 0 || - strcmp(type, "black_memqcache_table_list") == 0) + strcmp(type, "black_query_pattern_list") == 0 || + strcmp(type, "black_memqcache_table_list") == 0) { currItem.type = BLACKLIST; } @@ -194,6 +195,13 @@ int add_regex_pattern(const char *type, char *s) (errmsg("unable to add regex pattern for \"%s\", unable to allocate new pattern", type))); return 0; } + else if (strcmp(type, "black_query_pattern_list") == 0 && + growQueryPatternArray(currItem) < 0) + { + ereport(WARNING, + (errmsg("unable to add regex pattern for \"%s\", unable to allocate new pattern", type))); + return 0; + } return 1; } @@ -245,6 +253,27 @@ int growMemqcacheTablePatternArray(RegPattern item) return(pool_config->memqcache_table_pattc); } +int growQueryPatternArray(RegPattern item) +{ + void *_tmp = NULL; + if (pool_config->query_pattc == pool_config->current_query_pattern_size) + { + pool_config->current_query_pattern_size += PATTERN_ARR_SIZE; + _tmp = repalloc(pool_config->lists_query_patterns, + (pool_config->current_query_pattern_size * sizeof(RegPattern))); + if (!_tmp) + { + return(-1); + } + + pool_config->lists_query_patterns = (RegPattern*)_tmp; + } + pool_config->lists_query_patterns[pool_config->query_pattc] = item; + pool_config->query_pattc++; + + return(pool_config->query_pattc); +} + /* * Free a single ConfigVariable */ diff --git a/src/config/pool_config_variables.c b/src/config/pool_config_variables.c index 38b07ff75..d726b1867 100644 --- a/src/config/pool_config_variables.c +++ b/src/config/pool_config_variables.c @@ -17,6 +17,7 @@ #include "utils/pool_stream.h" #include "utils/palloc.h" #include "utils/memutils.h" +#include "watchdog/wd_utils.h" #else #include "utils/fe_ports.h" @@ -71,6 +72,7 @@ static bool MakeDBRedirectListRegex (char* newval, int elevel); static bool MakeAppRedirectListRegex (char* newval, int elevel); static bool check_redirect_node_spec(char *node_spec); static char **get_list_from_string(const char *str, const char *delimi, int *n); +static char **get_list_from_string_regex_delim(const char *str, const char *delimi, int *n); /*show functions */ @@ -1050,6 +1052,19 @@ static struct config_string_list ConfigureNamesStringList[] = NULL, NULL, NULL }, + { + {"black_query_pattern_list", CFGCXT_RELOAD, CONNECTION_POOL_CONFIG, + "list of query patterns that should be sent to primary node.", + CONFIG_VAR_TYPE_STRING_LIST,false, 0 + }, + &g_pool_config.black_query_pattern_list, + &g_pool_config.num_black_query_pattern_list, + NULL, + ";", + true, + NULL, NULL, NULL + }, + { {"wd_monitoring_interfaces_list", CFGCXT_INIT, WATCHDOG_CONFIG, "List of network device names, to be monitored by the watchdog process for the network link state.", @@ -2400,7 +2415,15 @@ initialize_variables_with_default(struct config_generic * gconf) } else { - *conf->variable = get_list_from_string(newval,conf->seperator, conf->list_elements_count); + if (strcmp(gconf->name, "black_query_pattern_list") == 0) + { + *conf->variable = get_list_from_string_regex_delim(newval, conf->seperator, conf->list_elements_count); + } + else + { + *conf->variable = get_list_from_string(newval,conf->seperator, conf->list_elements_count); + } + if (conf->compute_regex) { int i; @@ -2468,6 +2491,82 @@ static char **get_list_from_string(const char *str, const char *delimi, int *n) return tokens; } +/* + * Extract tokens separated by delimiter from str. Allow to + * use regex of delimiter. Return value is an array of + * pointers in pallocd strings. number of elements are set + * to n. + */ +static char **get_list_from_string_regex_delim(const char *input, const char *delimi, int *n) +{ +#ifndef POOL_PRIVATE + int j = 0; + char *output; + char *str; + char *buf; + char **tokens; + const int MAXTOKENS = 256; + *n = 0; + + if (input == NULL || *input == '\0') + return NULL; + + tokens = palloc(MAXTOKENS * sizeof(char *)); + if (*(input + strlen(input) - 1) != *delimi) + { + int len = strlen(input) + 2; + str = palloc(len); + snprintf(str, len, "%s;", input); + } + else + { + str = pstrdup(input); + } + + buf = str; + + while(*str != '\0') + { + if (*str == '\\') + { + j += 2; + str++; + } + else if (*str == *delimi) + { + output = (char *) palloc(j + 1); + StrNCpy(output, buf, j + 1); + + /* replace escape character of "'"*/ + tokens[*n] = pstrdup(string_replace(output, "\\'", "'")); + + ereport(DEBUG3, + (errmsg("initializing pool configuration"), + errdetail("extracting string tokens [token[%d]: %s]", *n, tokens[*n]))); + + (*n)++; + buf = str + 1; + j = 0; + + if ( ((*n) % MAXTOKENS ) == 0) + tokens = repalloc(tokens, (MAXTOKENS * sizeof(char *) * (((*n)/MAXTOKENS) + 1) )); + } + else + { + j++; + } + str++; + } + + if (*n > 0) + tokens = repalloc(tokens, (sizeof(char *) * (*n) )); + + return tokens; +#else + return NULL; +#endif +} + /* * Memory of the array type variables must be initialized befor calling this function */ @@ -3246,7 +3345,15 @@ setConfigOptionVar(struct config_generic *record, const char* name, int index_va pfree(*conf->variable); } - *conf->variable = get_list_from_string(newval, conf->seperator, conf->list_elements_count); + if (strcmp(name, "black_query_pattern_list") == 0) + { + *conf->variable = get_list_from_string_regex_delim(newval, conf->seperator, conf->list_elements_count); + } + else + { + *conf->variable = get_list_from_string(newval, conf->seperator, conf->list_elements_count); + } + if (conf->compute_regex) { /* TODO clear the old regex array please */ diff --git a/src/context/pool_query_context.c b/src/context/pool_query_context.c index 14456665d..0d4a7eb56 100644 --- a/src/context/pool_query_context.c +++ b/src/context/pool_query_context.c @@ -392,7 +392,17 @@ void pool_where_to_send(POOL_QUERY_CONTEXT *query_context, char *query, Node *no /* * Zap out DB node map */ - pool_clear_node_to_be_sent(query_context); + pool_clear_node_to_be_sent(query_context); + + /* + * When query match the query patterns in black_query_pattern_list, + * we send only to master node. + */ + if (MASTER_SLAVE && pattern_compare(query, BLACKLIST, "black_query_pattern_list") == 1) + { + pool_set_node_to_be_sent(query_context, MASTER_SLAVE ? PRIMARY_NODE_ID : REAL_MASTER_NODE_ID); + return; + } /* * If there is "NO LOAD BALANCE" comment, we send only to master node. diff --git a/src/include/pool_config.h b/src/include/pool_config.h index 22e24d0a2..05c3a3f1c 100644 --- a/src/include/pool_config.h +++ b/src/include/pool_config.h @@ -181,6 +181,7 @@ typedef struct { char **reset_query_list; /* comma separated list of queries to be issued at the end of session */ char **white_function_list; /* list of functions with no side effects */ char **black_function_list; /* list of functions with side effects */ + char **black_query_pattern_list; /* list of query patterns that should be sent to primary node */ char *log_line_prefix; /* printf-style string to output at beginning of each log line */ int log_error_verbosity; /* controls how much detail about error should be emitted */ int client_min_messages; /* controls which message should be sent to client */ @@ -247,6 +248,7 @@ typedef struct { int num_black_function_list; /* number of functions in black_function_list */ int num_white_memqcache_table_list; /* number of functions in white_memqcache_table_list */ int num_black_memqcache_table_list; /* number of functions in black_memqcache_table_list */ + int num_black_query_pattern_list; /* number of query patterns in black_query_pattern_list */ int num_wd_monitoring_interfaces_list; /* number of items in wd_monitoring_interfaces_list */ /* ssl configuration */ @@ -266,6 +268,10 @@ typedef struct { int pattc; /* number of regexp pattern */ int current_pattern_size; /* size of the regex pattern array */ + RegPattern *lists_query_patterns; /* Precompiled regex patterns for black query pattern lists */ + int query_pattc; /* number of regexp pattern */ + int current_query_pattern_size; /* size of the regex pattern array */ + bool memory_cache_enabled; /* if true, use the memory cache functionality, false by default */ MemCacheMethod memqcache_method; /* Cache store method. Either 'shmem'(shared memory) or 'memcached'. 'shmem' by default */ char *memqcache_memcached_host; /* Memcached host name. Mandatory if memqcache_method=memcached. */ @@ -388,5 +394,6 @@ extern char* backend_status_to_str(BackendInfo *bi); extern int add_regex_pattern(const char *type, char *s); extern int growFunctionPatternArray(RegPattern item); extern int growMemqcacheTablePatternArray(RegPattern item); +extern int growQueryPatternArray(RegPattern item); #endif /* POOL_CONFIG_H */ diff --git a/src/sample/pgpool.conf.sample b/src/sample/pgpool.conf.sample index a191c178c..c991219f1 100644 --- a/src/sample/pgpool.conf.sample +++ b/src/sample/pgpool.conf.sample @@ -296,11 +296,16 @@ black_function_list = 'nextval,setval,nextval,setval' # that write to database # Regexp are accepted +black_query_pattern_list = '' + # Semicolon separated list of query patterns + # that should be sent to primary node + # Regexp are accepted + # valid for streaming replicaton mode only. + database_redirect_preference_list = '' # comma separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' # valid for streaming replicaton mode only. - app_name_redirect_preference_list = '' # comma separated list of pairs of app name and node id. # example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby' diff --git a/src/sample/pgpool.conf.sample-logical b/src/sample/pgpool.conf.sample-logical index 062fe5d2e..3429a07d1 100644 --- a/src/sample/pgpool.conf.sample-logical +++ b/src/sample/pgpool.conf.sample-logical @@ -297,6 +297,12 @@ black_function_list = 'currval,lastval,nextval,setval' # that write to database # Regexp are accepted +black_query_pattern_list = '' + # Semicolon separated list of query patterns + # that should be sent to primary node + # Regexp are accepted + # valid for streaming replicaton mode only. + database_redirect_preference_list = '' # comma separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' diff --git a/src/sample/pgpool.conf.sample-master-slave b/src/sample/pgpool.conf.sample-master-slave index a6128452a..430480024 100644 --- a/src/sample/pgpool.conf.sample-master-slave +++ b/src/sample/pgpool.conf.sample-master-slave @@ -296,6 +296,12 @@ black_function_list = 'currval,lastval,nextval,setval' # that write to database # Regexp are accepted +black_query_pattern_list = '' + # Semicolon separated list of query patterns + # that should be sent to primary node + # Regexp are accepted + # valid for streaming replicaton mode only. + database_redirect_preference_list = '' # comma separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' diff --git a/src/sample/pgpool.conf.sample-replication b/src/sample/pgpool.conf.sample-replication index 7b0961b31..b4d1adae1 100644 --- a/src/sample/pgpool.conf.sample-replication +++ b/src/sample/pgpool.conf.sample-replication @@ -295,6 +295,12 @@ black_function_list = 'nextval,setval' # that write to database # Regexp are accepted +black_query_pattern_list = '' + # Semicolon separated list of query patterns + # that should be sent to primary node + # Regexp are accepted + # valid for streaming replicaton mode only. + database_redirect_preference_list = '' # comma separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' diff --git a/src/sample/pgpool.conf.sample-stream b/src/sample/pgpool.conf.sample-stream index fcdad7454..3531f4be4 100644 --- a/src/sample/pgpool.conf.sample-stream +++ b/src/sample/pgpool.conf.sample-stream @@ -297,6 +297,12 @@ black_function_list = 'currval,lastval,nextval,setval' # that write to database # Regexp are accepted +black_query_pattern_list = '' + # Semicolon separated list of query patterns + # that should be sent to primary node + # Regexp are accepted + # valid for streaming replicaton mode only. + database_redirect_preference_list = '' # comma separated list of pairs of database and node id. # example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2' diff --git a/src/test/regression/tests/001.load_balance/test.sh b/src/test/regression/tests/001.load_balance/test.sh index 2575d6bc4..1e78c5ccb 100755 --- a/src/test/regression/tests/001.load_balance/test.sh +++ b/src/test/regression/tests/001.load_balance/test.sh @@ -31,6 +31,7 @@ do $PSQL test <> etc/pgpool.conf + ./startall + wait_for_pgpool_startup + +queries=`cat << EOF +SELECT * FROM t1; +SELECT 'a'; +SELECT 1; +SELECT ';'; +SELECT * FROM t2; +SELECT f1(1); +EOF +` + echo "$queries" | while read query; do + $PSQL test -c "$query" + + # If master-slave mode, all queries are sent to primary node only. + # If query match both black_query_pattern_list and white_function_list, + # white_function_list will be ignored, and query is sent to primary node only. + # + # If replication node, all queries are load-blanced. + if [[ $mode = "s" ]];then + node_id=0 + else + node_id=1 + fi + fgrep "${query}" log/pgpool.log | grep "DB node id: "`echo $node_id` > /dev/null 2>&1 + + if [ $? != 0 ];then + # expected result not found + echo "fail: black query: ${query} is load-blanced." + ./shutdownall + exit 1 + fi + done + + if [ $? -eq 1 ]; then + exit 1 + fi + echo ok: black query pattern list works. + + # in replication mode if load_balance_mode = off, SELECT query inside # an explicit transaction should be sent to master only. if [ $mode = "r" ];then diff --git a/src/utils/pool_process_reporting.c b/src/utils/pool_process_reporting.c index fc8f6f32f..b755c261d 100644 --- a/src/utils/pool_process_reporting.c +++ b/src/utils/pool_process_reporting.c @@ -451,6 +451,19 @@ POOL_REPORT_CONFIG* get_config(int *nrows) StrNCpy(status[i].desc, "functions those write to database", POOLCONFIG_MAXDESCLEN); i++; + StrNCpy(status[i].name, "black_query_pattern_list", POOLCONFIG_MAXNAMELEN); + *(status[i].value) = '\0'; + for (j=0;jnum_black_query_pattern_list;j++) + { + len = POOLCONFIG_MAXVALLEN - strlen(status[i].value); + strncat(status[i].value, pool_config->black_query_pattern_list[j], len); + len = POOLCONFIG_MAXVALLEN - strlen(status[i].value); + if (j != pool_config->num_black_query_pattern_list-1) + strncat(status[i].value, ";", len); + } + StrNCpy(status[i].desc, "query patterns that should be sent to primary node", POOLCONFIG_MAXDESCLEN); + i++; + StrNCpy(status[i].name, "disable_load_balance_on_write", POOLCONFIG_MAXNAMELEN); snprintf(status[i].value, POOLCONFIG_MAXVALLEN, "%d", pool_config->disable_load_balance_on_write); StrNCpy(status[i].desc, "Load balance behavior when write query is received", POOLCONFIG_MAXDESCLEN); diff --git a/src/utils/pool_select_walker.c b/src/utils/pool_select_walker.c index 8350293d3..bc3dac573 100644 --- a/src/utils/pool_select_walker.c +++ b/src/utils/pool_select_walker.c @@ -203,6 +203,12 @@ int pattern_compare(char *str, const int type, const char *param_name) lists_patterns = pool_config->lists_memqcache_table_patterns; pattc = &pool_config->memqcache_table_pattc; + } + else if (strcmp(param_name, "black_query_pattern_list") == 0) + { + lists_patterns = pool_config->lists_query_patterns; + pattc = &pool_config->query_pattc; + } else { ereport(WARNING, (errmsg("pattern_compare: unknown paramname %s", param_name))); -- 2.39.5