[Pgpool-hackers] About query cache of pgpool-II

[Huang Bambo]

>>> Yes, it's on my TODO list. However, there is a fundamental difficulty
>>> to implement it. For performance reason, to return cached result or
>>> not is decided *before* SQL parsing. Problem is, without SQL parsing
>>> it's not possible what tables are involved in the SELECT (think about
>>> complex JOIN or subquery).
>>>
>>
>>> Delaying the decision after the parsing will add an overhead.
>>
>> Can you tell me a little more detail about the implementation of the
>> cache function.
>> I guess the cache cached the result of a query. So I think just a
>> simple compare is
>> enough to check if this query is executed before. Maybe it may wast some memory
>> because sql is not case sensitive and maybe 2 sql actually is the same
>> while strcmp
>> tell us that they are not. If the result of sql is cached, just return
>> result. If not, do parse,
>> check if the the tables in white/black list and query data.
>
> Oh I was wrong. Yes, we can check if the tables included in the SELECT
> can be cached or not *after* parsing.
>
> We can have something like:
>
> memqcache_cache_tables:
> List of table names allowed to cache. Regular expression can be used.
>
> memqcache_no_cache_tables:
> List of table names not allowed to cache. Regular expression can be used.
>
> They are exclusive like black/white_function_list.
>
> Comments?
> --

My idea about the SQL cache's implementation is about like this;
1、Get SQL from client, get the hash of the SQL string to determin
whether it is invoked before or not
2、If not chached, parse and execute, if the SQL changed the result set
of chached data, clear chaced data
3、If cached, just return data from cache
4、We can add some function to determin whether the parsed SQL is
already chached,because some upcased or lowercased charactors may make
the SQL have diffirent hash.

Bambo Huang