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

v4 patch pushed.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp