[pgpool-hackers: 4527] 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.

I have created manual pages.
V3 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: v3_query_cache_invalidation.patch
Type: text/x-patch
Size: 25400 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20241008/145c34c1/attachment-0001.bin>