pgpool2.git / summary / log / commit / refs
commit 47fd46cbcf31b46d93a8ade62fda43b82c39b4e6
Author: Tatsuo Ishii <ishii@sraoss.co.jp>
Date: Wed Jul 06 12:31:08 2022 +0000
Fix to not cache SELECT having functions with return types are timestamptz or timetz.
Functions with return type is timestamptz or timetz is affected by time zone setting.
Consider following scenario:
1) SELECT having such functions gets called and cache created.
2) time zone is changed.
3) same SELECT is called and the cache is used. The cache value is
correct any more because of the time zone change.
Discussion: https://www.pgpool.net/pipermail/pgpool-general/2022-July/008367.html
src/test/regression/tests/006.memqcache/test.sh | 3 +
src/utils/pool_select_walker.c | 113 ++++++++++++++++++++++--
2 files changed, 108 insertions(+), 8 deletions(-)
diff --git a/src/test/regression/tests/006.memqcache/test.sh b/src/test/regression/tests/006.memqcache/test.sh
index 781849def..486b75887 100755
--- a/src/test/regression/tests/006.memqcache/test.sh
+++ b/src/test/regression/tests/006.memqcache/test.sh
@@ -80,6 +80,8 @@ SELECT '2022-07-05 10:00:00'::TIMESTAMPTZ;
SELECT '2022-07-05 10:00:00'::TIMESTAMPTZ;
SELECT '2022-07-05 10:00:00'::TIMETZ;
SELECT '2022-07-05 10:00:00'::TIMETZ;
+SELECT to_timestamp(0);
+SELECT to_timestamp(0);
EOF
success=true
@@ -98,6 +100,7 @@ EOF
grep "fetched from cache" log/pgpool.log | grep 'DATE;' > /dev/null || success=false
grep "fetched from cache" log/pgpool.log | grep 'TIMESTAMPTZ;' > /dev/null && success=false
grep "fetched from cache" log/pgpool.log | grep 'TIMETZ;' > /dev/null && success=false
+ grep "fetched from cache" log/pgpool.log | grep 'to_timestamp' > /dev/null && success=false
if [ $success = false ];then
./shutdownall
diff --git a/src/utils/pool_select_walker.c b/src/utils/pool_select_walker.c
index 9cb5583bc..18b319818 100644
--- a/src/utils/pool_select_walker.c
+++ b/src/utils/pool_select_walker.c
@@ -53,6 +53,7 @@ static bool select_table_walker(Node *node, void *context);
static bool non_immutable_function_call_walker(Node *node, void *context);
static char *strip_quote(char *str);
static bool function_volatile_property(char *fname, FUNC_VOLATILE_PROPERTY property);
+static bool function_has_return_type(char *fname, char *typename);
/*
* Return true if this SELECT has function calls *and* supposed to
@@ -1028,17 +1029,26 @@ non_immutable_function_call_walker(Node *node, void *context)
ctx->has_non_immutable_function_call = true;
return false;
}
+
+ /* check return type is timestamptz */
+ if (function_has_return_type(fname, "timestamptz"))
+ {
+ /* timestamptz should not be cached */
+ ctx->has_non_immutable_function_call = true;
+ return false;
+ }
+
+ /* return type is timetz */
+ if (function_has_return_type(fname, "timetz"))
+ {
+ /* timetz should not be cached */
+ ctx->has_non_immutable_function_call = true;
+ return false;
+ }
}
}
- /* Before Pgpool-II 3.7 there was no SQLValueFunction in the parser. For
- * the older versions (3.6 or before), we need to check type cast and
- * typename instead. If they are some type casts described below, we
- * assume that this SELECT cannot be cached since they might be a
- * transformed CURRENT_TIMESTAMP etc. Of course this could be overkill as
- * "SELECT '2022-07-04 09:00:00'::TIMESTAMP" could be regarded as
- * non-cachable for example. But there's nothing we can do here.
- */
+ /* Check type cast */
else if (IsA(node, TypeCast))
{
/* TIMESTAMP WITH TIME ZONE and TIME WITH TIME ZONE should not be cached. */
@@ -1484,3 +1494,90 @@ make_table_name_from_rangevar(RangeVar *rangevar)
return tablename;
}
+
+/*
+ * Return whether given function has the given type name. If one or more
+ * functions match, return true.
+ */
+static
+bool function_has_return_type(char *fname, char *typename)
+{
+/*
+ * Query to count the number of records matching given function name and type name.
+ */
+#define FUNCTION_RETURN_TYPE_MATCHEING_QUERY "SELECT count(*) FROM pg_type AS t, pg_catalog.pg_proc AS p, pg_catalog.pg_namespace AS n WHERE p.proname = '%s' AND n.oid = p.pronamespace AND n.nspname %s '%s' AND p.prorettype = t.oid AND t.typname = '%s';"
+ bool result;
+ char query[1024];
+ char *rawstring = NULL;
+ List *names = NIL;
+ POOL_CONNECTION_POOL *backend;
+ static POOL_RELCACHE *relcache;
+
+ /* We need a modifiable copy of the input string. */
+ rawstring = pstrdup(fname);
+
+ /* split "schemaname.funcname" */
+ if(!SplitIdentifierString(rawstring, '.', (Node **) &names) ||
+ names == NIL)
+ {
+ pfree(rawstring);
+ list_free(names);
+
+ ereport(WARNING,
+ (errmsg("invalid function name %s", fname)));
+
+ return false;
+ }
+
+ if (list_length(names) == 2)
+ {
+ /* with schema qualification */
+ snprintf(query, sizeof(query), FUNCTION_RETURN_TYPE_MATCHEING_QUERY, (char *) llast(names),
+ "=", (char *) linitial(names), typename);
+ }
+ else
+ {
+ snprintf(query, sizeof(query), FUNCTION_RETURN_TYPE_MATCHEING_QUERY, (char *) llast(names),
+ "~", ".*", typename);
+ }
+
+ backend = pool_get_session_context(false)->backend;
+
+ if (!relcache)
+ {
+ /*
+ * We pass "%s" as a template query so that pool_search_relcache
+ * passes whole query.
+ */
+ relcache = pool_create_relcache(pool_config->relcache_size, "%s",
+ int_register_func, int_unregister_func,
+ false);
+ if (relcache == NULL)
+ {
+ pfree(rawstring);
+ list_free(names);
+
+ ereport(WARNING,
+ (errmsg("unable to create relcache, while checking the function volatile property")));
+ return false;
+ }
+ ereport(DEBUG1,
+ (errmsg("checking the function matches the given type name"),
+ errdetail("relcache created")));
+ }
+
+ /*
+ * We pass whole query as "table" parameter of pool_search_relcache so
+ * that each relcache entry is distinguished by actual query string.
+ */
+ result = (pool_search_relcache(relcache, backend, query) == 0) ? 0 : 1;
+
+ pfree(rawstring);
+ list_free(names);
+
+ ereport(DEBUG1,
+ (errmsg("checking the function matches the given type name"),
+ errdetail("search result = %d (function name: %s type name: %s)", result, fname, typename)));
+
+ return result;
+}
[parent: 859ca1734e0d]