[pgpool-general: 5478] Memcache: white_memqcache_table_list ignored if table name matches black_memqcache_table_list

[José Pedro]

Hi all,

I'm using pgpool-ii 3.5.4, with Postgres 9.4.

pgpool --version
pgpool-II version 3.5.4 (ekieboshi)

I need to *only* cache queries to one table, because this pgpool sees only
some of the queries. So I want to be safe, and only cache the tables I
*know* are safe (mainly some read-only parameters). This pool is in a
remote location, next to the replica slave, but with high latency to the
master.

I set:

white_memqcache_table_list = 'table_to_cache'
black_memqcache_table_list = '.*'

However, pgpool *never* caches queries to the "table_to_cache".

I went to have a look at the source, function pool_is_allow_to_cache, in:

https://github.com/pgpool/pgpool2/blob/V3_5_4_RPM/src/query_cache/pool_memqcache.c

line 785: bool pool_is_allow_to_cache(Node *node, char *query)

line 799: if (pool_config->num_black_memqcache_table_list > 0)

if (pool_is_table_in_black_list(ctx.table_names[i]) == true)
{
ereport(DEBUG1,
(errmsg("memcache: node is not allowed to cache")));
return false;
}

line 861: if (pool_config->num_white_memqcache_table_list > 0)

if (pool_is_table_in_white_list(table) == false)
{
ereport(DEBUG1,
(errmsg("memcache: node is not allowed to cache")));
return false;
}

This tells me that, if the table_to_cache matches the black list, it will
never reach the part where it will try to match the white list.

If I don't set the blacklist, then pgpool caches some tables that have high
churn, and gets stale values.

I found this nice slide set by Tatsuo Ishii,
https://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf
,
whose page 8 (When pgpool does not create cache) ends with this bullet
point:

   - Tables listed in  “white_memqcache_table_list” will be cached even
   above conditions are met

It seems the above function was changed two months after the slide set's
date, in commit

https://github.com/pgpool/pgpool2/commit/41febb3aaa4480e0c9219e4538e04ef7398669a6

       Fix bug that only tables in white_memqcache_table_list was cached

which split a function named pool_is_table_to_cache into two functions
pool_is_table_in_black_list and pool_is_table_in_white_list, and this broke
the above behaviour.

Am I right in this analysis? Is this a bug? I can't find in the docs any
mention of priority between these black and white lists, so, have I
stumbled upon undefined behaviour?

Regards,
-José Pedro
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170516/cc8ac7e8/attachment-0001.html>