<div dir="ltr">Hi all,<div><br></div><div>I'm using pgpool-ii 3.5.4, with Postgres 9.4.</div><div><div><br></div><div>pgpool --version</div><div>pgpool-II version 3.5.4 (ekieboshi)</div></div><div><br></div><div>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.</div><div><br></div><div>I set:</div><div><br></div><div>white_memqcache_table_list = 'table_to_cache'</div><div>black_memqcache_table_list = '.*'</div><div><br></div><div>However, pgpool *never* caches queries to the "table_to_cache".</div><div><br></div><div>I went to have a look at the source, function pool_is_allow_to_cache, in:</div><div><br></div><div><a href="https://github.com/pgpool/pgpool2/blob/V3_5_4_RPM/src/query_cache/pool_memqcache.c">https://github.com/pgpool/pgpool2/blob/V3_5_4_RPM/src/query_cache/pool_memqcache.c</a><br></div><div><br></div><div><span class="gmail-pl-k">line 785: bool</span> <span class="gmail-pl-en">pool_is_allow_to_cache</span>(Node *node, <span class="gmail-pl-k">char</span> *query)<br></div><div><br></div><div>line 799: <span class="gmail-pl-k">if</span> (pool_config->num_black_memqcache_table_list > <span class="gmail-pl-c1">0</span>)</div><div><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC809" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> <span class="gmail-pl-k"><br>if</span> (<span class="gmail-pl-c1">pool_is_table_in_black_list</span>(ctx.<span class="gmail-pl-smi">table_names</span>[i]) == <span class="gmail-pl-c1">true</span>)</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC810" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> {</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC811" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> <span class="gmail-pl-c1">ereport</span>(DEBUG1,</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC812" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> (<span class="gmail-pl-c1">errmsg</span>(<span class="gmail-pl-s"><span class="gmail-pl-pds">"</span>memcache: node is not allowed to cache<span class="gmail-pl-pds">"</span></span>)));</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC813" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> <span class="gmail-pl-k">return</span> <span class="gmail-pl-c1">false</span>;</td>
</tr>
<tr>
</tr></tbody></table> }<br></div><div><br></div><div>line 861: <span class="gmail-pl-k">if</span> (pool_config->num_white_memqcache_table_list > <span class="gmail-pl-c1">0</span>)</div><div><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC875" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> <span class="gmail-pl-k"><br>if</span> (<span class="gmail-pl-c1">pool_is_table_in_white_list</span>(table) == <span class="gmail-pl-c1">false</span>)</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC876" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> {</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC877" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> <span class="gmail-pl-c1">ereport</span>(DEBUG1,</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC878" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> (<span class="gmail-pl-c1">errmsg</span>(<span class="gmail-pl-s"><span class="gmail-pl-pds">"</span>memcache: node is not allowed to cache<span class="gmail-pl-pds">"</span></span>)));</td>
</tr>
<tr>
</tr></tbody></table><table class="gmail-highlight gmail-tab-size gmail-js-file-line-container"><tbody><tr><td id="gmail-LC879" class="gmail-blob-code gmail-blob-code-inner gmail-js-file-line"> <span class="gmail-pl-k">return</span> <span class="gmail-pl-c1">false</span>;</td>
</tr>
<tr>
</tr></tbody></table> }<br></div><div><br></div><div>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.</div><div><br></div><div>If I don't set the blacklist, then pgpool caches some tables that have high churn, and gets stale values.</div><div><br></div><div>I found this nice slide set by Tatsuo Ishii,</div><div><a href="https://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf">https://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf</a>,</div><div>whose page 8 (When pgpool does not create cache) ends with this bullet point:</div><div><ul><li>Tables listed in “white_memqcache_table_list” will be cached even above conditions are met</li></ul><div>It seems the above function was changed two months after the slide set's date, in commit</div><div><br></div><div><a href="https://github.com/pgpool/pgpool2/commit/41febb3aaa4480e0c9219e4538e04ef7398669a6">https://github.com/pgpool/pgpool2/commit/41febb3aaa4480e0c9219e4538e04ef7398669a6</a></div><div><p class="gmail-commit-title"> Fix bug that only tables in white_memqcache_table_list was cached</p><p class="gmail-commit-title">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.</p><p class="gmail-commit-title">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?</p><p class="gmail-commit-title">Regards,<br>-José Pedro</p></div></div></div>