[pgpool-hackers: 4526] Re: 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

Peng pointed out off list that the command can be executed even if
query cache is disabled. I think it's confusing. I modified the patch
so that the pcp command emits an error if query cache is disabled.

pcp_invalidate_query_cache -p 11001
ERROR:  query cache is not enabled

V2 patch attached.

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: v2_query_cache_invalidation.patch
Type: text/x-patch
Size: 16318 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20241008/33d15483/attachment.bin>