[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:
https://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf
Sorry for this.

So the commit:
https://github.com/pgpool/pgpool2/commit/41febb3aaa4480e0c9219e4538e04ef7398669a6
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 FOR SHARE/UPDATE
   SELECT starting with "/*NO QUERY CACHE*/" comment
   SELECT including system catalogs
   SELECT uses TABLESAMPLE

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

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
Japanese:http://www.sraoss.co.jp


More information about the pgpool-general mailing list