diff options
| -rw-r--r-- | doc.ja/src/sgml/memcache.sgml | 87 | ||||
| -rw-r--r-- | doc/src/sgml/memcache.sgml | 46 | ||||
| -rw-r--r-- | src/include/query_cache/pool_memqcache.h | 3 | ||||
| -rw-r--r-- | src/query_cache/pool_memqcache.c | 8 | ||||
| -rwxr-xr-x | src/test/regression/tests/006.memqcache/test.sh | 6 |
5 files changed, 129 insertions, 21 deletions
diff --git a/doc.ja/src/sgml/memcache.sgml b/doc.ja/src/sgml/memcache.sgml index a6f690306..1e981c8e5 100644 --- a/doc.ja/src/sgml/memcache.sgml +++ b/doc.ja/src/sgml/memcache.sgml @@ -22,30 +22,93 @@ <note> <para> <!-- - Basically following SELECTs will not be cached: + First of all if the query starts with SQL comment: <programlisting> - SELECTs including non immutable functions - SELECTs including temp tables, unlogged tables - SELECT result is too large (memqcache_maxcache) - SELECT FOR SHARE/UPDATE - SELECT starting with "/*NO QUERY CACHE*/" comment - SELECT including system catalogs - SELECT uses TABLESAMPLE - </programlisting> + /*FORCE QUERY CACHE*/ + </programlisting> + is checked (case insensitive). If so, the result of the query is + cached unconditionally as long as it is not SELECT or WITH + SELECT. + However you must be very careful to use this feature. For example, + <programlisting> + /*FORCE QUERY CACHE*/SELECT now(); + </programlisting> + will return the same timestamp until pgpool restarts, once the + query is cached. The query cache will not be discarded even with a + query: + <programlisting> + /*NO QUERY CACHE*/SELECT now(); + </programlisting> + Because it just prevents to create a cache entry for the query, and + does not affect the query using the FORCE QUERY CACHE comment. + </programlisting> + Because it just prevents to create a cache entry for the query, and + does not affect the query using the FORCE QUERY CACHE comment. + --> + まず、クエリが以下のSQLコメントで始まるかどうかがチェックされます（大文字小文字は区別されません）。 + <programlisting> + /*FORCE QUERY CACHE*/ + </programlisting> + もしこのコメントで始まっているなら、そのクエリがSELECTあるいはWITH + SELECTである限り無条件にキャッシュされます。 + しかし、この機能は注意深く使う必要があります。 + たとえば、 + <programlisting> + /*FORCE QUERY CACHE*/SELECT now(); + </programlisting> + このクエリがキャッシュされると、pgpoolが再起動するまで同じタイムスタンプを返し続けます。 + このクエリキャッシュは、以下のクエリによってさえも削除されません。 + <programlisting> + /*NO QUERY CACHE*/SELECT now(); + </programlisting> + なぜなら、これはこのクエリのキャッシュエントリが作成されないようにするだけで、FORCE QUERY CACHEコメントを使ったクエリには影響を与えないからです。 + </para> + <para> + <!-- + Note that for following query: + <programlisting> + /*FORCE QUERY CACHE*/SELECT * FROM t1; + </programlisting> + usual cache validation due to an update to the table (in this case + t1) works. + --> + 次のクエリ + <programlisting> + /*FORCE QUERY CACHE*/SELECT * FROM t1; + </programlisting> + に対しては、テーブル（この場合はt1）の更新によるキャッシュの削除は機能することに注意してください。 + </para> + <para> + <!-- + If the query does not start with FORCE QUERY CACHE comment, + following checks are performed. If one of followings is satisfied, + SELECT will not be cached. + <programlisting> + SELECT including non immutable functions + SELECT including temp tables, unlogged tables + SELECT including TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE + SELECT including CAST to TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE + SELECT including SQLValueFunction (CURRENT_TIME, CURRENT_USER etc.) + SELECT result is too large (memqcache_maxcache) + SELECT FOR SHARE/UPDATE + SELECT starting with "/*NO QUERY CACHE*/" comment (case insensitive) + SELECT including system catalogs + SELECT using TABLESAMPLE + SELECT uses row security enabled tables + </programlisting> However, VIEWs and SELECTs accessing unlogged tables can be cached by specifying in the <xref linkend="guc-cache-safe-memqcache-table-list">. --> - 基本的に以下のSELECTはキャッシュされません。 + クエリがFORCE QUERY CACHEコメントで始まっていなければ、以下のチェックが行われます。 + 以下の一つでも該当すれば、SELECTはキャッシュされません。 <programlisting> immutableでない関数を含むSELECT + 一時テーブル、unloggedテーブルを使ったSELECT TIMESTAMP WITH TIMEZONE、TIME WITH TIMEZONEを返す関数を使っているSELECT TIMESTAMP WITH TIMEZONE、TIME WITH TIMEZONEへのキャストを含むSELECT SQLValueFunction (CURRENT_TIME, CURRENT_USERなど)を含むSELECT - 一時テーブル、unloggedテーブルを使ったSELECT 検索結果が memqcache_maxcache を越えるようなSELECT SELECT FOR SHARE/UPDATE - /*NO QUERY CACHE*/コメントで始まるSELECT + /*NO QUERY CACHE*/コメント（大文字小文字は区別されません）で始まるSELECT システムカタログを使用しているSELECT TABLESAMPLEを使っているSELECT 行セキュリティが設定されているテーブルを使っているSELECT diff --git a/doc/src/sgml/memcache.sgml b/doc/src/sgml/memcache.sgml index 171a4a28a..9ee211b44 100644 --- a/doc/src/sgml/memcache.sgml +++ b/doc/src/sgml/memcache.sgml @@ -22,18 +22,48 @@ <note> <para> - Basically following SELECTs will not be cached: + First of all if the query starts with SQL comment: <programlisting> - SELECTs including non immutable functions - SELECTs including temp tables, unlogged tables - SELECTs including TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE - SELECTs including CAST to TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE - SELECTs including SQLValueFunction (CURRENT_TIME, CURRENT_USER etc.) + /*FORCE QUERY CACHE*/ + </programlisting> + is checked (case insensitive). If so, the result of the query is + cached unconditionally as long as it is not SELECT or WITH + SELECT. + However you must be very careful to use this feature. For example, + <programlisting> + /*FORCE QUERY CACHE*/SELECT now(); + </programlisting> + will return the same timestamp until pgpool restarts, once the + query is cached. The query cache will not be discarded even with a + query: + <programlisting> + /*NO QUERY CACHE*/SELECT now(); + </programlisting> + Because it just prevents to create a cache entry for the query, and + does not affect the query using the FORCE QUERY CACHE comment. + </para> + <para> + Note that for following query: + <programlisting> + /*FORCE QUERY CACHE*/SELECT * FROM t1; + </programlisting> + usual cache validation due to an update to the table (in this case + t1) works. + </para> + <para> + If the query does not start with FORCE QUERY CACHE comment, + following checks are performed. If one of followings is satisfied, + SELECT will not be cached. + <programlisting> + SELECT including non immutable functions + SELECT including temp tables, unlogged tables + SELECT including TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE + SELECT including CAST to TIMESTAMP WITH TIMEZONE or TIME WITH TIMEZONE + SELECT including SQLValueFunction (CURRENT_TIME, CURRENT_USER etc.) SELECT result is too large (memqcache_maxcache) SELECT FOR SHARE/UPDATE - SELECT starting with "/*NO QUERY CACHE*/" comment + SELECT starting with "/*NO QUERY CACHE*/" comment (case insensitive) SELECT including system catalogs - SELECT uses TABLESAMPLE + SELECT using TABLESAMPLE SELECT uses row security enabled tables </programlisting> However, VIEWs and SELECTs accessing unlogged tables can be diff --git a/src/include/query_cache/pool_memqcache.h b/src/include/query_cache/pool_memqcache.h index 9607fb835..6e94d3479 100644 --- a/src/include/query_cache/pool_memqcache.h +++ b/src/include/query_cache/pool_memqcache.h @@ -29,6 +29,9 @@ #include "pool.h" #include <sys/time.h> +#define FORCE_QUERY_CACHE "/*FORCE QUERY CACHE*/" +#define FORCE_QUERY_CACHE_COMMENT_SZ (sizeof(FORCE_QUERY_CACHE)-1) + #define NO_QUERY_CACHE "/*NO QUERY CACHE*/" #define NO_QUERY_CACHE_COMMENT_SZ (sizeof(NO_QUERY_CACHE)-1) diff --git a/src/query_cache/pool_memqcache.c b/src/query_cache/pool_memqcache.c index ad1501c0c..7ff8a80d9 100644 --- a/src/query_cache/pool_memqcache.c +++ b/src/query_cache/pool_memqcache.c @@ -925,7 +925,13 @@ pool_is_allow_to_cache(Node *node, char *query) SelectContext ctx; /* - * If NO QUERY CACHE comment exists, do not cache. + * If FORCE QUERY CACHE comment exists, cache it unconditionally. + */ + if (!strncasecmp(query, FORCE_QUERY_CACHE, FORCE_QUERY_CACHE_COMMENT_SZ)) + return true; + + /* + * If NO QUERY CACHE comment exists, do not cache unconditionally. */ if (!strncasecmp(query, NO_QUERY_CACHE, NO_QUERY_CACHE_COMMENT_SZ)) return false; diff --git a/src/test/regression/tests/006.memqcache/test.sh b/src/test/regression/tests/006.memqcache/test.sh index 05f9e26a1..e7e2353b9 100755 --- a/src/test/regression/tests/006.memqcache/test.sh +++ b/src/test/regression/tests/006.memqcache/test.sh @@ -91,6 +91,10 @@ SELECT '2022-07-05 10:00:00'::TIMETZ; SELECT '2022-07-05 10:00:00'::TIMETZ; SELECT to_timestamp(0); SELECT to_timestamp(0); +/*FORCE QUERY CACHE*/SELECT now(); +/*FORCE QUERY CACHE*/SELECT now(); +/*NO QUERY CACHE*/SELECT 1; +/*NO QUERY CACHE*/SELECT 1; EOF success=true @@ -110,6 +114,8 @@ EOF 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 + grep "fetched from cache" log/pgpool.log | grep 'FORCE QUERY CACHE' > /dev/null || success=false + grep "fetched from cache" log/pgpool.log | grep 'NO QUERY CACHE' > /dev/null && success=false if [ $success = false ];then ./shutdownall |