From c5c1ca6b37fdc0b25b5507abfbdadd40bc2e81a7 Mon Sep 17 00:00:00 2001 From: Tatsuo Ishii Date: Mon, 21 Oct 2024 19:17:56 +0900 Subject: [PATCH] Feature: new PGPOOL SET CACHE DELETE command. The new PGPOOl SET command allows to delete query cache by specifying the previous query used to create the query cache entry. example usage is: PGPOOL SET CACHE DELETE 'SELECT * FROM t1;' This command is particularly useful for queries that are not invalidated by the auto cache invalidation feature because the query does not have any reference to tables. --- doc.ja/src/sgml/ref/allfiles.sgml | 1 + doc.ja/src/sgml/ref/pgpool_set_cache.sgml | 131 ++++++++++++++++++++++ doc.ja/src/sgml/reference.sgml | 1 + doc/src/sgml/ref/allfiles.sgml | 1 + doc/src/sgml/ref/pgpool_set_cache.sgml | 97 ++++++++++++++++ doc/src/sgml/reference.sgml | 1 + src/include/parser/nodes.h | 1 + src/include/query_cache/pool_memqcache.h | 2 + src/parser/gram.y | 14 +++ src/protocol/pool_proto_modules.c | 13 +++ src/query_cache/pool_memqcache.c | 53 +++++++++ 11 files changed, 315 insertions(+) create mode 100644 doc.ja/src/sgml/ref/pgpool_set_cache.sgml create mode 100644 doc/src/sgml/ref/pgpool_set_cache.sgml diff --git a/doc.ja/src/sgml/ref/allfiles.sgml b/doc.ja/src/sgml/ref/allfiles.sgml index 6764e8f9c..89be154bc 100644 --- a/doc.ja/src/sgml/ref/allfiles.sgml +++ b/doc.ja/src/sgml/ref/allfiles.sgml @@ -30,6 +30,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc.ja/src/sgml/ref/pgpool_set_cache.sgml b/doc.ja/src/sgml/ref/pgpool_set_cache.sgml new file mode 100644 index 000000000..82f02359b --- /dev/null +++ b/doc.ja/src/sgml/ref/pgpool_set_cache.sgml @@ -0,0 +1,131 @@ + + + + + PGPOOL SET + + + + PGPOOL SET CACHE + 1 + SQL - Language Statements + + + + PGPOOL SET CACHE + + クエリキャッシュを削除する + + + + + PGPOOL SET CACHE DELETE 'query' + + + + + + 説明 + + + + PGPOOL SET CACHE DELETEコマンドは、以前のクエリで作成されたクエリキャッシュを削除します。 + クエリ文字列は、末尾の';'も含めて以前のクエリと完全に一致していなければなりません。 + にもかかわらず、' (単一引用符)がクエリに含まれている場合は、以下のように'を前に追加する必要があります。 + +PGPOOL SET CACHE DELETE 'SELECT ''FOO'';'; + + + このコマンドはとりわけ、クエリ中にテーブル参照を含んでいないために自動キャッシュ削除機能で削除されないクエリに有用です。 + + + + 以前のクエリが思い出せない時は、を使って、削除したいクエリキャッシュも含み、すべてのクエリキャッシュを削除することができます。 + ただし、これはPgpool-IIの管理者権限が必要になります。 + + + + クエリキャッシュの削除に成功すると、以下のNOTICEメッセージを表示します。 + +NOTICE: query cache deleted. query: "query string" + + + クエリキャッシュが存在しないか、クエリキャッシュが無効の場合には、以下のNOTICEメッセージを表示します。 + +NOTICE: query cache does not exist for query: "query string" + + + + + + + 例 + + + + 間違って希望しないクエリキャッシュを作成してしまいました。 + 以下のコマンドで削除します。 + +test=# /*FORCE QUERY CACHE*/SELECT current_timestamp; + current_timestamp +------------------------------- + 2024-10-18 18:25:07.826423+09 +(1 row) + +test=# PGPOOL SET CACHE DELETE '/*FORCE QUERY CACHE*/SELECT current_timestamp;'; +NOTICE: query cache deleted. query: "/*FORCE QUERY CACHE*/SELECT current_timestamp;" +SET + + + + + + + + 関連項目 + + + + + + + diff --git a/doc.ja/src/sgml/reference.sgml b/doc.ja/src/sgml/reference.sgml index 548b05f24..adfe8e1e1 100644 --- a/doc.ja/src/sgml/reference.sgml +++ b/doc.ja/src/sgml/reference.sgml @@ -222,6 +222,7 @@ &pgpoolShow &pgpoolSet + &pgpoolSetCache &pgpoolReset &showPoolStatus &showPoolNodes diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 6764e8f9c..89be154bc 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -30,6 +30,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/pgpool_set_cache.sgml b/doc/src/sgml/ref/pgpool_set_cache.sgml new file mode 100644 index 000000000..8ae8d42f5 --- /dev/null +++ b/doc/src/sgml/ref/pgpool_set_cache.sgml @@ -0,0 +1,97 @@ + + + + + PGPOOL SET + + + + PGPOOL SET CACHE + 1 + SQL - Language Statements + + + + PGPOOL SET CACHE + delete query cache + + + + + PGPOOL SET CACHE DELETE 'query' + + + + + Description + + + The PGPOOL SET CACHE DELETE command deletes the + query cache + previously created by the query. The query string must be exactly + identical to the previous query, including + trailing ';'. Nevertheless if the query + includes ' (single quore), it needs to be + prefixed by ' like: + +PGPOOL SET CACHE DELETE 'SELECT ''FOO'';'; + + This command is particularly useful for queries that are not + invalidated by + the auto + cache invalidation feature because the query does not have + any reference to tables. + + + If you do not remember the previous query, you can + use to delete all the + query cache including the cache you want to delete. However it + requires the Pgpool-II admin privilege. + + + Upon successful deletion of the query cache, this command emits a + notice message: + +NOTICE: query cache deleted. query: "query string" + + If the cache does not exist or query cache is not enabled, it emits + a notice message: + +NOTICE: query cache does not exist for query: "query string" + + + + + + Examples + + + Accidentally created an unwanted query cache. Delete it by using + the command. + +test=# /*FORCE QUERY CACHE*/SELECT current_timestamp; + current_timestamp +------------------------------- + 2024-10-18 18:25:07.826423+09 +(1 row) + +test=# PGPOOL SET CACHE DELETE '/*FORCE QUERY CACHE*/SELECT current_timestamp;'; +NOTICE: query cache deleted. query: "/*FORCE QUERY CACHE*/SELECT current_timestamp;" +SET + + + + + + + See Also + + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index ffc7ddb86..2b2db4947 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -155,6 +155,7 @@ &pgpoolShow &pgpoolSet + &pgpoolSetCache &pgpoolReset &showPoolStatus &showPoolNodes diff --git a/src/include/parser/nodes.h b/src/include/parser/nodes.h index 734faa48f..1d5bb3ed6 100644 --- a/src/include/parser/nodes.h +++ b/src/include/parser/nodes.h @@ -34,6 +34,7 @@ typedef enum NodeTag /* pgpool Extension */ T_PgpoolVariableSetStmt, T_PgpoolVariableShowStmt, + T_PgpoolQueryCacheStmt, #include "nodetags.h" } NodeTag; diff --git a/src/include/query_cache/pool_memqcache.h b/src/include/query_cache/pool_memqcache.h index 5a0a1fce9..042121fc4 100644 --- a/src/include/query_cache/pool_memqcache.h +++ b/src/include/query_cache/pool_memqcache.h @@ -307,4 +307,6 @@ extern void pool_init_whole_cache_blocks(void); extern void clear_query_cache(void); +extern bool query_cache_delete_by_stmt(char *query, POOL_CONNECTION_POOL * backend); + #endif /* POOL_MEMQCACHE_H */ diff --git a/src/parser/gram.y b/src/parser/gram.y index a39f98991..61663cce2 100644 --- a/src/parser/gram.y +++ b/src/parser/gram.y @@ -1655,6 +1655,13 @@ VariableSetStmt: n->is_local = false; $$ = (Node *) n; } + | PGPOOL set_rest_more + { + VariableSetStmt *n = $2; + n->type = T_PgpoolQueryCacheStmt; /* Hack to keep changes minimum */ + n->is_local = false; + $$ = (Node *) n; + } | SET set_rest { VariableSetStmt *n = $2; @@ -1834,6 +1841,13 @@ set_rest_more: /* Generic SET syntaxes: */ n->args = list_make1(makeStringConst($3, @3)); $$ = n; } + /* PGPOOL CACHE DELETE */ + | SET CACHE DELETE_P Sconst + { + VariableSetStmt *n = makeNode(VariableSetStmt); + n->name = $4; /* query to delete query cache */ + $$ = n; + } ; var_name: ColId { $$ = $1; } diff --git a/src/protocol/pool_proto_modules.c b/src/protocol/pool_proto_modules.c index df8d27e0c..4988325dc 100644 --- a/src/protocol/pool_proto_modules.c +++ b/src/protocol/pool_proto_modules.c @@ -450,7 +450,20 @@ SimpleQuery(POOL_CONNECTION * frontend, pool_set_skip_reading_from_backends(); return POOL_CONTINUE; } + if (IsA(node, PgpoolQueryCacheStmt)) + { + VariableSetStmt *vnode = (VariableSetStmt *) node; + if (query_cache_delete_by_stmt(vnode->name, backend)) + elog(NOTICE, "query cache deleted. query: \"%s\"", vnode->name); + else + elog(NOTICE, "query cache does not exist for query: \"%s\"", vnode->name); + pool_ps_idle_display(backend); + send_complete_and_ready(frontend, backend, "SET", -1); + pool_query_context_destroy(query_context); + pool_set_skip_reading_from_backends(); + return POOL_CONTINUE; + } if (IsA(node, PgpoolVariableSetStmt)) { VariableSetStmt *vnode = (VariableSetStmt *) node; diff --git a/src/query_cache/pool_memqcache.c b/src/query_cache/pool_memqcache.c index 4c55f0fc4..7f824fe22 100644 --- a/src/query_cache/pool_memqcache.c +++ b/src/query_cache/pool_memqcache.c @@ -4731,3 +4731,56 @@ InvalidateQueryCache(int tableoid, int dboid) pool_semaphore_unlock(QUERY_CACHE_STATS_SEM); POOL_SETMASK(&oldmask); } + +/* + * Public API to invalidate query cache specified by query string. Returns + * true for successfully query cache invalidation. If the query cache was not + * found, returns false. Note that this function does not remove any entry in + * a table oid file. That may leave a garbage in the file (which is ignored + * by the auto cache invalidation) but it's not worth the trouble to remove + * the entry since it's relatively expensive. It needs to rewrite the whole + * file in the worst case. + */ +bool query_cache_delete_by_stmt(char *query, POOL_CONNECTION_POOL * backend) +{ + bool rtn = true; + pool_sigset_t oldmask; + + char key[MAX_KEY]; + POOL_CACHEID *cacheid; + + POOL_SETMASK2(&BlockSig, &oldmask); + pool_shmem_lock(POOL_MEMQ_EXCLUSIVE_LOCK); + + /* encode md5key */ + encode_key(query, key, backend); + + if (pool_is_shmem_cache()) + + { + POOL_QUERY_HASH hashkey; + + memcpy(hashkey.query_hash, key, POOL_MD5_HASHKEYLEN); + cacheid = pool_hash_search(&hashkey); + if (cacheid == NULL) + rtn = false; + else + pool_delete_item_shmem_cache(cacheid); + } +#ifdef USE_MEMCACHED + else + { + if (delete_cache_on_memcached(key) == 0) + rtn = false; + } +#else + { + ereport(WARNING, + (errmsg("failed to delete query cache on memcached, memcached support is not enabled"))); + } +#endif + pool_semaphore_unlock(QUERY_CACHE_STATS_SEM); + POOL_SETMASK(&oldmask); + + return rtn; +} -- 2.39.5