The PGPOOL SET CACHE DELETE command deletes the query cache previously created by the query. The query string must be exactly identical to the previous query, including trailing ';'. Nevertheless if the query includes ' (single quore), it needs to be prefixed by ' like:
PGPOOL SET CACHE DELETE 'SELECT ''FOO'';';
This command is particularly useful for queries that are not invalidated by the auto cache invalidation feature because the query does not have any reference to tables.
If you do not remember the previous query, you can use pcp_invalidate_query_cache to delete all the query cache including the cache you want to delete. However it requires the Pgpool-II admin privilege.
Upon successful deletion of the query cache, this command emits a notice message:
NOTICE: query cache deleted. query: "query string"
If the cache does not exist or query cache is not enabled, it emits a notice message:
NOTICE: query cache does not exist for query: "query string"
Accidentally created an unwanted query cache. Delete it by using the command.
test=# /*FORCE QUERY CACHE*/SELECT current_timestamp;
current_timestamp
-------------------------------
2024-10-18 18:25:07.826423+09
(1 row)
test=# PGPOOL SET CACHE DELETE '/*FORCE QUERY CACHE*/SELECT current_timestamp;';
NOTICE: query cache deleted. query: "/*FORCE QUERY CACHE*/SELECT current_timestamp;"
SET