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

Tatsuo Ishii ishii at sraoss.co.jp
Wed May 17 13:16:10 JST 2017

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

I have looked into this and found that the behavior of the program
has never been same as descibed in:
Sorry for this.

So the commit:
actually did not break the the behavior.

Here is the correct description of the current behavior of the code:

1) Check black_memqcache_table_list. If the target table name matches
any of the entries, the table is not cached. Done.

2) If the table is one of:

   SELECTs including views
   SELECTs including non immutable functions
   SELECTs including temp tables, unlogged tables
   SELECT result is too large (memqcache_maxcache)
   SELECT starting with "/*NO QUERY CACHE*/" comment
   SELECT including system catalogs

   then the table is not cached. Go to step 3.

3) If white_memqcache_table_list is not empty and the table is either
VIEW or unlogged table, and the table name matches any of the entries
of white_memqcache_table_list, then the table is cached. If not, the
table is not cached. Done.

4) Other tables are cached even if they are not listed in

So to satify your request, following method can be used.

1) Keep white_memqcache_table_list empty.

2) Add a regular expression which means "except table_to_cache' to
black_memqcache_table_list. For example if you want to cache 't1', you
can use following regular expression.

black_memqcache_table_list = '(([^1]|[^t]1)|[^t]t)+'

This is pretty confusing but it works.

Best regards,
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php

More information about the pgpool-general mailing list