[pgpool-hackers: 4481] Invalidating query cache item

[Tatsuo Ishii]

In today's off list pgpool developer meeting, I proposed that
invalidating query cache item is necessary if forcing query comment
feature [1] is implemented, because it is possible to create a query
cache item that cannot be invalidated without restarting pgpool.
For example, following query will create a query cache item.

/*FORCE QUERY CACHE*/select now();

Since there's no table involved by the cache item, it is not possible
to invalidate the cache item by modifying tables.

So question is, what user interface can be provided to user to
invalidate query cache items? Before jumping into the question, let's
look back how each query item is formed.

Basically a query cache item is assigned an md5 hash, the unique key
to the cache item. The md5 hash i.e. cache key is created by:

pool_md5_hash("user_name+database_name+query_string");

For extended query message, the query string includes bind parameters
(encoded into hex string) at the end of ther query string. Notice that
no original query is included in the cache item.

Now let's discuss the original issue: how to invalidate query cache item.

(1) Provide a new SQL level command to invalidate query cache item by
specifying the cache key.

PGPOOL CACHE INVALIDATE cache_key;

cache_key is a 32 byte cache key. Example cache kye is:

11577997e4e89900212c09b3b7909837

Problem is, how can a user know the cache key? Currently the only way
to know that is, enabling DEBUG option of log_client_message and
looking into the pgpool log file. Any better idea?

(2) Provide a new SQL level command to invalidate query cache item by
specifying the original query.

PGPOOL CACHE INVALIDATE query_string;

This will create a cache key from current user name, database name and
the query_string, then search the cache item using the cache id.
Note that if the query has any parameter, this form cannot be used.

(3) Invalidate query cache by database name/user name

Initially I thought this is possible but actually is not. Because
there's no database/user name information associated with cache items.

Comments and/or suggestions are welcome!

[1]  [pgpool-hackers: 4479] Comment to force query cache
https://www.pgpool.net/pipermail/pgpool-hackers/2024-July/004480.html
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp