<div dir="auto"><div><br><div class="gmail_extra"><br><div class="gmail_quote">On May 17, 2017 05:15, "Tatsuo Ishii" <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br type="attribution"><blockquote class="quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="elided-text">> Hi all,<br>
><br>
> I'm using pgpool-ii 3.5.4, with Postgres 9.4.<br>
><br>
> pgpool --version<br>
> pgpool-II version 3.5.4 (ekieboshi)<br>
><br>
> I need to *only* cache queries to one table, because this pgpool sees only<br>
> some of the queries. So I want to be safe, and only cache the tables I<br>
> *know* are safe (mainly some read-only parameters). This pool is in a<br>
> remote location, next to the replica slave, but with high latency to the<br>
> master.<br>
><br>
> I set:<br>
><br>
> white_memqcache_table_list = 'table_to_cache'<br>
> black_memqcache_table_list = '.*'<br>
><br>
> However, pgpool *never* caches queries to the "table_to_cache".<br>
><br>
> I went to have a look at the source, function pool_is_allow_to_cache, in:<br>
><br>
> <a href="https://github.com/pgpool/pgpool2/blob/V3_5_4_RPM/src/query_cache/pool_memqcache.c" rel="noreferrer" target="_blank">https://github.com/pgpool/<wbr>pgpool2/blob/V3_5_4_RPM/src/<wbr>query_cache/pool_memqcache.c</a><br>
><br>
> line 785: bool pool_is_allow_to_cache(Node *node, char *query)<br>
><br>
> line 799: if (pool_config->num_black_<wbr>memqcache_table_list > 0)<br>
><br>
> if (pool_is_table_in_black_list(<wbr>ctx.table_names[i]) == true)<br>
> {<br>
> ereport(DEBUG1,<br>
> (errmsg("memcache: node is not allowed to cache")));<br>
> return false;<br>
> }<br>
><br>
> line 861: if (pool_config->num_white_<wbr>memqcache_table_list > 0)<br>
><br>
> if (pool_is_table_in_white_list(<wbr>table) == false)<br>
> {<br>
> ereport(DEBUG1,<br>
> (errmsg("memcache: node is not allowed to cache")));<br>
> return false;<br>
> }<br>
><br>
> This tells me that, if the table_to_cache matches the black list, it will<br>
> never reach the part where it will try to match the white list.<br>
><br>
> If I don't set the blacklist, then pgpool caches some tables that have high<br>
> churn, and gets stale values.<br>
><br>
> I found this nice slide set by Tatsuo Ishii,<br>
> <a href="https://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf" rel="noreferrer" target="_blank">https://www.sraoss.co.jp/<wbr>event_seminar/2012/20121024_<wbr>pgpool-II_pgconfEU2012_sraoss.<wbr>pdf</a><br>
> ,<br>
> whose page 8 (When pgpool does not create cache) ends with this bullet<br>
> point:<br>
><br>
</div>> - Tables listed in “white_memqcache_table_list” will be cached even<br>
<div class="quoted-text">> above conditions are met<br>
><br>
> It seems the above function was changed two months after the slide set's<br>
> date, in commit<br>
><br>
> <a href="https://github.com/pgpool/pgpool2/commit/41febb3aaa4480e0c9219e4538e04ef7398669a6" rel="noreferrer" target="_blank">https://github.com/pgpool/<wbr>pgpool2/commit/<wbr>41febb3aaa4480e0c9219e4538e04e<wbr>f7398669a6</a><br>
><br>
> Fix bug that only tables in white_memqcache_table_list was cached<br>
><br>
> which split a function named pool_is_table_to_cache into two functions<br>
> pool_is_table_in_black_list and pool_is_table_in_white_list, and this broke<br>
> the above behaviour.<br>
><br>
> Am I right in this analysis? Is this a bug? I can't find in the docs any<br>
> mention of priority between these black and white lists, so, have I<br>
> stumbled upon undefined behaviour?<br>
<br>
</div>I have looked into this and found that the behavior of the program<br>
has never been same as descibed in:<br>
<a href="https://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf" rel="noreferrer" target="_blank">https://www.sraoss.co.jp/<wbr>event_seminar/2012/20121024_<wbr>pgpool-II_pgconfEU2012_sraoss.<wbr>pdf</a><br>
Sorry for this.<br>
<br>
So the commit:<br>
<a href="https://github.com/pgpool/pgpool2/commit/41febb3aaa4480e0c9219e4538e04ef7398669a6" rel="noreferrer" target="_blank">https://github.com/pgpool/<wbr>pgpool2/commit/<wbr>41febb3aaa4480e0c9219e4538e04e<wbr>f7398669a6</a><br>
actually did not break the the behavior.<br>
<br>
Here is the correct description of the current behavior of the code:<br>
<br>
1) Check black_memqcache_table_list. If the target table name matches<br>
any of the entries, the table is not cached. Done.<br>
<br>
2) If the table is one of:<br>
<br>
SELECTs including views<br>
SELECTs including non immutable functions<br>
SELECTs including temp tables, unlogged tables<br>
SELECT result is too large (memqcache_maxcache)<br>
SELECT FOR SHARE/UPDATE<br>
SELECT starting with "/*NO QUERY CACHE*/" comment<br>
SELECT including system catalogs<br>
SELECT uses TABLESAMPLE<br>
<br>
then the table is not cached. Go to step 3.<br>
<br>
3) If white_memqcache_table_list is not empty and the table is either<br>
VIEW or unlogged table, and the table name matches any of the entries<br>
of white_memqcache_table_list, then the table is cached. If not, the<br>
table is not cached. Done.<br>
<br>
4) Other tables are cached even if they are not listed in<br>
white_memqcache_table_list.<br>
<br>
So to satify your request, following method can be used.<br>
<br>
1) Keep white_memqcache_table_list empty.<br>
<br>
2) Add a regular expression which means "except table_to_cache' to<br>
black_memqcache_table_list. For example if you want to cache 't1', you<br>
can use following regular expression.<br>
<br>
black_memqcache_table_list = '(([^1]|[^t]1)|[^t]t)+'<br>
<br>
This is pretty confusing but it works.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_<wbr>en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.<wbr>jp</a><br>
</blockquote></div><br></div></div><div class="gmail_extra" dir="auto">Thanks for your feedback. </div><div class="gmail_extra" dir="auto"><br></div><div class="gmail_extra" dir="auto">I came to the same conclusion yesterday, and stuck till 2 am trying to create a regexp with a negative match for the black list.</div><div class="gmail_extra" dir="auto"><br></div><div class="gmail_extra" dir="auto">Tried with ^((?!(t1|t2)$).+$, but this syntax was not accepted by the re library pgpool uses -- I guess you use the pcre lib on your (<a href="http://pgpool.net">pgpool.net</a>'s) yum repo for rhel7, based on ldd output. </div><div class="gmail_extra" dir="auto"><br></div><div class="gmail_extra" dir="auto">I'll try to find out exactly what kind of syntax that regexp lib use, if any, otherwise I will follow your suggestion, although that feels like a an unmaintenable mess in the long run. </div><div class="gmail_extra" dir="auto"><br></div><div class="gmail_extra" dir="auto">Regards, </div><div class="gmail_extra" dir="auto">-José Pedro </div><div class="gmail_extra" dir="auto"><br></div><div class="gmail_extra" dir="auto">PS: I am impressed with the level of detail you managed to provide in your response</div></div>