[pgpool-hackers: 4524] New PCP command to invalidate query cache

[Tatsuo Ishii]

Now that we are able to create a cache entry for an arbitrary
SELECT[1], I think it would be nice for pgpool to provide a way to
invalidate query cache without restarting pgpool because such a query
cache cannot be invalidated (example: "SELECT
current_timestamp"). Attached is the patch to do that (manual or
test patch is not included yet).

New PCP command is called "pcp_invalidate_query_cache". It places a
cache invalidation request on shared memory. It is not possible to
invalidate part of the query cache.  All query cache is invalidated.

The reasons for the PCP process cannot remove cache directly are:

1) the connection handle to memcached server is not managed by PCP
process.

2) removing shared memory query cache needs an interlock using
pool_shmem_ock() which may not work well on PCP process. Also a
function used here (pool_clear_memory_cache()) uses PG_TRY, which is
only usable in pgpool child process.

If pgpool child process finds such a request, the process invalidates
all query cache on the shared memory. If the query cache storage is
memcached, then pgpool issues memcached_flush() so that all query
cache on memcached are flushed immediately.

Note that the timing for pgpool child process to check the
invalidation request is after processing current query or response
from backend. This means that if all pgpool child process sit idle,
the request will not be processed until any of them receives a
messages from either frontend or backend.

Another note is, about query cache statistics shown by "show
pool_cache" command. Since the cache invalidation does not clear the
statistics, some of them (num_cache_hits and num_selects) continue to
increase even after the cache invalidation. Initializing the
statistics at the same could be possible but I am not sure if all
users want to do it.

Comments and/or suggestions are welcome.

[1] https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bdbee93ceb9f1452f9eab56077c8041a68e53ba3

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: query_cache_invalidation.patch
Type: text/x-patch
Size: 15831 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20241006/83b6c63c/attachment.bin>