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

I have added tests to the 006.memqcache regression test.  I think the
patch is now commitable form. v4 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: v4_query_cache_invalidation.patch
Type: text/x-patch
Size: 26848 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20241011/3f0d39b0/attachment-0001.bin>