[pgpool-hackers: 4534] Re: New PGPOOL SET command to delete query cache

[Tatsuo Ishii]

> Now that we have pcp_invalidate_query_cache command to invalidate
> entire query cache without restarting pgpool. But this is an overkill
> for a user who just wants to delete a query cache created by the
> user. The only way to delete the cache is asking the admin to help the
> user. Of couse the user can wait untill the table is modified: that
> will delete all query cache entries related to the table. But what if
> the query cache does not refer to any table? For example,
> 
> /*FORCE QUERY CACHE*/SELECT current_timestamp;
> 
> this query will create a query cache entry which cannot be deleted by
> the auto cache invalidation triggered by a table modification. We can
> blame the user because of issuing such a stupid query. But we know
> that people make mistakes. We should be kind to users. So I would like
> to propose a new SQL (like) command to delete a query cache (the
> command does not access PostgreSQL).
> 
> PGPOOL SET CACHE DELETE 'query';
> 
> "query" is the query which was used to create a create cache. Here is
> an example session.
> 
> - force to create a query cache
> 
> test=# /*FORCE QUERY CACHE*/SELECT current_timestamp;
>        current_timestamp       
> -------------------------------
>  2024-10-18 18:25:07.826423+09
> (1 row)
> 
> - current_timestamp returns the same result as above because of the
>   query cache
> 
> test=# /*FORCE QUERY CACHE*/SELECT current_timestamp;
>        current_timestamp       
> -------------------------------
>  2024-10-18 18:25:07.826423+09
> (1 row)
> 
> - remove the query cache
> 
> test=# PGPOOL SET CACHE DELETE '/*FORCE QUERY CACHE*/SELECT current_timestamp;';
> NOTICE:  query cache deleted. query: "/*FORCE QUERY CACHE*/SELECT current_timestamp;"
> SET
> 
> - now the query result is refreshed because the query cache is gone.
> 
> test=# /*FORCE QUERY CACHE*/SELECT current_timestamp;
>       current_timestamp       
> ------------------------------
>  2024-10-18 18:26:02.89493+09
> (1 row)
> 
> Note that PGPOOL SET CACHE DELETE does not prevent from creating query
> cache in the future. If you repeat the query again, a query cache will
> be created.
> 
> Patch attached.
> 
> Comments and suggestions are welcome.

I have pushed the patch with documents.

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