[pgpool-hackers: 4492] Re: 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?

Another problem is, if we add this command, a user can invalidate any
query cache created by someone else. This looks like a security
hazard. To solve the problem, probably we need to add username (and
possibly database name) info to cache. This will solve (3) too.

> (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.

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