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

[Tatsuo Ishii]

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

1-3 are already implemented in CVS HEAD.

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

So you "normalize" SQL string before hash it?
I doubt it is doable. For example,

SELECT 'abc'
and
select 'abc'

will create same result but

SELECT 'ABC'
and
select 'abc'

will not. This is because SQL identifiers are not case sensitive, but
string literals are case sensitive.  And without parsing query, we
cannot know which one is identifiers and which one is string literals.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp