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

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: delete_cache.patch
Type: text/x-patch
Size: 4093 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20241018/153a0418/attachment.bin>