pgpool2.git / summary / log / commit / refs
commit 47f28521cfe8bd3f8f67667d442de2390441e77b
Author: Tatsuo Ishii <ishii@postgresql.org>
Date: Sat Aug 23 13:50:13 2014 +0000
Add new parameter "allow_sql_comments".
SQL comments (/* ... */) effectively prevent load balancing and query
cache because pgpool-II strictly checks if the query starts with
"SELECT...". The new parameter removes the restriction and allows to
use SQL comments without disturbing load balancing and query
cache. For backward compatibility sake, the default is off.
doc/pgpool-en.html | 17 ++++-
doc/pgpool-ja.html | 17 ++++-
src/config/pool_config.l | 17 +++++
src/include/pool_config.h | 7 ++
src/include/protocol/pool_proto_modules.h | 4 +-
src/protocol/pool_process_query.c | 40 ++++++-----
src/sample/pgpool.conf.sample | 5 ++
src/sample/pgpool.conf.sample-master-slave | 5 ++
src/sample/pgpool.conf.sample-replication | 5 ++
src/sample/pgpool.conf.sample-stream | 5 ++
src/test/regression/tests/008.sql_comments/test.sh | 78 ++++++++++++++++++++++
src/utils/pool_process_reporting.c | 5 ++
12 files changed, 184 insertions(+), 21 deletions(-)
diff --git a/doc/pgpool-en.html b/doc/pgpool-en.html
index 702c6fb91..0e921583f 100644
--- a/doc/pgpool-en.html
+++ b/doc/pgpool-en.html
@@ -1184,6 +1184,19 @@ local0.* /var/log/pgpool.log
<p>
You need to reload pgpool.conf if you change this value.
</p>
+ </dd>
+
+ <dt id="ALLOW_SQL_COMMENTS">allow_sql_comments <span class="version">V3.4 〜</span></dt>
+ <dd>
+ <p>
+ If on, ignore SQL comments when judging if load balance or
+ query cache is possible.
+ If off, SQL comments effectively prevent the judgment
+ (pre 3.4 behavior).
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
</dd>
</dl>
@@ -1994,7 +2007,9 @@ comments just in front of the SELECT query:
/*REPLICATION*/ SELECT ...
</pre>
</p>
-
+<p>
+If you want to use comments without supressing load balancing, you can set <a href="#ALLOW_SQL_COMMENTS">allow_sql_comments</a> to on.
+</p>
<p>
Please refer to <a href="#replicate_select">replicate_select</a> as well.
See also a <a href="where_to_send_queries.pdf">flow chart</a>.
diff --git a/doc/pgpool-ja.html b/doc/pgpool-ja.html
index 6e2224cac..8c563a3bc 100644
--- a/doc/pgpool-ja.html
+++ b/doc/pgpool-ja.html
@@ -1175,6 +1175,18 @@ local0.* /var/log/pgpool.log
このパラメータを変更した時には設定ファイルを再読み込みしてください。
</p>
</dd>
+
+ <dt id="ALLOW_SQL_COMMENTS">allow_sql_comments <span class="version">V3.4 〜</span></dt>
+ <dd>
+ <p>
+ onならば、負荷分散やクエリキャッシュができるかどうかの判定の際にSQLコメントを無視します。
+ offならば、その判定に影響を与えます(3.4より前のバージョンの動作です)。
+ </p>
+ <p>
+ You need to reload pgpool.conf if you change this value.
+ </p>
+ </dd>
+
</dl>
<h3>Backends</h3>
@@ -2041,7 +2053,10 @@ SELECTなどの問い合わせがロードバランスされます。
すべてのバックエンドに送信される（レプリケーションされる）ようになります。
副作用がある関数を含む問合わせに対してはこのテクニックが利用できます。
</p>
-
+<p>
+SQLコメントの記述が負荷分散に影響を与えないようにするには、
+<a href="#ALLOW_SQL_COMMENTS">allow_sql_comments</a>をonにします。
+</p>
<p>
<font color="red">
注意: JDBC ドライバなどのように、ドライバ内で autocommit の有効・無効のオプションがある場合、
diff --git a/src/config/pool_config.l b/src/config/pool_config.l
index 86a2aaac1..9faa964f0 100644
--- a/src/config/pool_config.l
+++ b/src/config/pool_config.l
@@ -250,6 +250,8 @@ int pool_init_config(void)
pool_config->redirect_app_names = NULL;
pool_config->app_name_redirect_tokens = NULL;
+ pool_config->allow_sql_comments = 0;
+
/*
* add for watchdog
*/
@@ -3125,6 +3127,21 @@ int pool_get_config(char *confpath, POOL_CONFIG_CONTEXT context)
}
}
}
+
+ else if (!strcmp(key, "allow_sql_comments") &&
+ CHECK_CONTEXT(INIT_CONFIG|RELOAD_CONFIG, context))
+ {
+ int v = eval_logical(yytext);
+
+ if (v < 0)
+ {
+ pool_error("pool_config: invalid value %s for %s", yytext, key);
+ fclose(fd);
+ return(-1);
+ }
+ pool_config->allow_sql_comments = v;
+ }
+
}
fclose(fd);
diff --git a/src/include/pool_config.h b/src/include/pool_config.h
index 4a6485784..61585d06b 100644
--- a/src/include/pool_config.h
+++ b/src/include/pool_config.h
@@ -245,6 +245,13 @@ typedef struct {
RegArray *redirect_app_names; /* Precompiled regex patterns for app name prefrence list */
Left_right_tokens *app_name_redirect_tokens; /* app name redirect for app_name and node string */
+ /*
+ * if on, ignore SQL comments when judging if load balance or query cache
+ * is possible. If off, SQL comments effectively prevent the judgment
+ * (pre 3.4 behavior). For backward compatibilty sake, default is off.
+ */
+ int allow_sql_comments;
+
/*
* add for watchdog
*/
diff --git a/src/include/protocol/pool_proto_modules.h b/src/include/protocol/pool_proto_modules.h
index ecf4a0c68..e1a71e1b9 100644
--- a/src/include/protocol/pool_proto_modules.h
+++ b/src/include/protocol/pool_proto_modules.h
@@ -6,7 +6,7 @@
* pgpool: a language independent connection pool server for PostgreSQL
* written by Tatsuo Ishii
*
- * Copyright (c) 2003-2012 PgPool Global Development Group
+ * Copyright (c) 2003-2014 PgPool Global Development Group
*
* Permission to use, copy, modify, and distribute this software and
* its documentation for any purpose and without fee is hereby
@@ -152,7 +152,7 @@ extern int RowDescription(POOL_CONNECTION *frontend,
extern void wait_for_query_response_with_trans_cleanup(POOL_CONNECTION *frontend, POOL_CONNECTION *backend, int protoVersion, int pid, int key);
extern POOL_STATUS wait_for_query_response(POOL_CONNECTION *frontend, POOL_CONNECTION *backend, int protoVersion);
-extern int is_select_query(Node *node, char *sql);
+extern bool is_select_query(Node *node, char *sql);
extern bool is_commit_query(Node *node);
extern bool is_rollback_query(Node *node);
extern bool is_commit_or_rollback_query(Node *node);
diff --git a/src/protocol/pool_process_query.c b/src/protocol/pool_process_query.c
index 4177faae6..79c2301e9 100644
--- a/src/protocol/pool_process_query.c
+++ b/src/protocol/pool_process_query.c
@@ -1400,20 +1400,23 @@ static int
}
/*
- * returns non 0 if the SQL statement can be load
- * balanced. Followings are statemnts go into this category.
+ * Returns true if the SQL statement is regarded as read SELECT from syntax's
+ * point of view. However callers need to do aditional checking such as if the
+ * SELECT does not have write functions or not to make sure that the SELECT is
+ * sematically read SELECT.
*
+ * For followings this function returns true:
* - SELECT/WITH without FOR UPDATE/SHARE
* - COPY TO STDOUT
* - EXPLAIN
* - EXPLAIN ANALYZE and query is SELECT not including writing functions
*
- * note that for SELECT INTO, this function returns 0
+ * Note that for SELECT this function returns false.
*/
-int is_select_query(Node *node, char *sql)
+bool is_select_query(Node *node, char *sql)
{
if (node == NULL)
- return 0;
+ return false;
/*
* 2009/5/1 Tatsuo says: This test is not bogus. As of 2.2, pgpool
@@ -1426,9 +1429,9 @@ int is_select_query(Node *node, char *sql)
* set it to Portal->sql_string.
*/
if (sql == NULL)
- return 0;
+ return false;
- if (pool_config->ignore_leading_white_space)
+ if (!pool_config->allow_sql_comments && pool_config->ignore_leading_white_space)
{
/* ignore leading white spaces */
while (*sql && isspace(*sql))
@@ -1442,12 +1445,15 @@ int is_select_query(Node *node, char *sql)
select_stmt = (SelectStmt *)node;
if (select_stmt->intoClause || select_stmt->lockingClause)
- return 0;
+ return false;
- /* '\0' and ';' signify empty query */
- return (*sql == 's' || *sql == 'S' || *sql == '(' ||
- *sql == 'w' || *sql == 'W' || *sql == 't' || *sql == 'T' ||
- *sql == '\0' || *sql == ';');
+ 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 == ';');
+ else
+ return true;
}
else if (IsA(node, CopyStmt))
{
@@ -1481,13 +1487,13 @@ int is_select_query(Node *node, char *sql)
* can always load balance.
*/
if (!analyze)
- return 1;
+ return true;
/*
* If ANALYZE, we need to check function calls.
*/
if (pool_has_function_call(query))
- return 0;
- return 1;
+ return false;
+ return true;
}
else
{
@@ -1496,10 +1502,10 @@ int is_select_query(Node *node, char *sql)
* is not specified.
*/
if (!analyze)
- return 1;
+ return true;
}
}
- return 0;
+ return false;
}
#ifdef NOT_USED
diff --git a/src/sample/pgpool.conf.sample b/src/sample/pgpool.conf.sample
index d8666813a..723e4262b 100644
--- a/src/sample/pgpool.conf.sample
+++ b/src/sample/pgpool.conf.sample
@@ -306,6 +306,11 @@ 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'
# valid for streaming replicaton mode only.
+allow_sql_comments = off
+ # if on, ignore SQL comments when judging if load balance or
+ # query cache is possible.
+ # If off, SQL comments effectively prevent the judgment
+ # (pre 3.4 behavior).
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
diff --git a/src/sample/pgpool.conf.sample-master-slave b/src/sample/pgpool.conf.sample-master-slave
index d480544a5..7beb2a042 100644
--- a/src/sample/pgpool.conf.sample-master-slave
+++ b/src/sample/pgpool.conf.sample-master-slave
@@ -306,6 +306,11 @@ 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'
# valid for streaming replicaton mode only.
+allow_sql_comments = off
+ # if on, ignore SQL comments when judging if load balance or
+ # query cache is possible.
+ # If off, SQL comments effectively prevent the judgment
+ # (pre 3.4 behavior).
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
diff --git a/src/sample/pgpool.conf.sample-replication b/src/sample/pgpool.conf.sample-replication
index 5e19afbd8..7f9d72e4c 100644
--- a/src/sample/pgpool.conf.sample-replication
+++ b/src/sample/pgpool.conf.sample-replication
@@ -306,6 +306,11 @@ 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'
# valid for streaming replicaton mode only.
+allow_sql_comments = off
+ # if on, ignore SQL comments when judging if load balance or
+ # query cache is possible.
+ # If off, SQL comments effectively prevent the judgment
+ # (pre 3.4 behavior).
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
diff --git a/src/sample/pgpool.conf.sample-stream b/src/sample/pgpool.conf.sample-stream
index ce37b7c2b..06d46b66f 100644
--- a/src/sample/pgpool.conf.sample-stream
+++ b/src/sample/pgpool.conf.sample-stream
@@ -307,6 +307,11 @@ 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'
# valid for streaming replicaton mode only.
+allow_sql_comments = off
+ # if on, ignore SQL comments when judging if load balance or
+ # query cache is possible.
+ # If off, SQL comments effectively prevent the judgment
+ # (pre 3.4 behavior).
#------------------------------------------------------------------------------
# MASTER/SLAVE MODE
diff --git a/src/test/regression/tests/008.sql_comments/test.sh b/src/test/regression/tests/008.sql_comments/test.sh
new file mode 100755
index 000000000..02eefe47b
--- /dev/null
+++ b/src/test/regression/tests/008.sql_comments/test.sh
@@ -0,0 +1,78 @@
+#!/usr/bin/env bash
+#-------------------------------------------------------------------
+# test script for allow_sql_comments
+#
+source $TESTLIBS
+TESTDIR=testdir
+PSQL=$PGBIN/psql
+
+function getnode()
+{
+ grep $1 log/pgpool.log | grep SELECT | awk '{print $9}'
+}
+
+rm -fr $TESTDIR
+mkdir $TESTDIR
+cd $TESTDIR
+
+# create test environment
+echo -n "creating test environment..."
+$PGPOOL_SETUP -m s -n 2 || exit 1
+echo "done."
+
+source ./bashrc.ports
+
+echo "backend_weight0 = 0" >> etc/pgpool.conf
+echo "backend_weight1 = 1" >> etc/pgpool.conf
+echo "allow_sql_comments = off" >> etc/pgpool.conf
+
+./startall
+
+export PGPORT=$PGPOOL_PORT
+wait_for_pgpool_startup
+
+ok=yes
+
+# should be redirect to node 1
+$PSQL -c "SELECT 'case1'" test
+
+test `getnode "case1"` -eq 1 || ok=ng
+
+# should be redirect to node 0
+$PSQL -c "/* comment */SELECT 'case2'" test
+
+test `getnode "case2"` -eq 0 || ok=ng
+
+# should be redirect to node 0
+$PSQL -c "/*No LOAD BALANCE*/SELECT 'case3'" test
+
+test `getnode "case3"` -eq 0 || ok=ng
+
+echo "allow_sql_comments = on" >> etc/pgpool.conf
+
+./pgpool_reload
+
+wait_for_pgpool_startup
+
+# should be redirect to node 1
+$PSQL -c "SELECT 'case4'" test
+
+test `getnode "case4"` -eq 1 || ok=ng
+
+# should be redirect to node 1
+$PSQL -c "/* comment */SELECT 'case5'" test
+
+test `getnode "case5"` -eq 1 || ok=ng
+
+# should be redirect to node 0
+$PSQL -c "/*No LOAD BALANCE*/SELECT 'case6'" test
+
+test `getnode "case6"` -eq 0 || ok=ng
+
+
+./shutdownall
+
+if [ $ok = "yes" ];then
+ exit 0
+fi
+exit 1
diff --git a/src/utils/pool_process_reporting.c b/src/utils/pool_process_reporting.c
index 619def244..f4b05ba5a 100644
--- a/src/utils/pool_process_reporting.c
+++ b/src/utils/pool_process_reporting.c
@@ -497,6 +497,11 @@ POOL_REPORT_CONFIG* get_config(int *nrows)
StrNCpy(status[i].desc, "redirect by application name", POOLCONFIG_MAXDESCLEN);
i++;
+ StrNCpy(status[i].name, "allow_sql_comments", POOLCONFIG_MAXNAMELEN);
+ snprintf(status[i].value, POOLCONFIG_MAXVALLEN, "%d", pool_config->allow_sql_comments);
+ StrNCpy(status[i].desc, "allow SQL comments", POOLCONFIG_MAXDESCLEN);
+ i++;
+
/* PARALLEL MODE AND QUERY CACHE */
StrNCpy(status[i].name, "parallel_mode", POOLCONFIG_MAXNAMELEN);
[parent: 27488cdaf3e3]