<div dir="ltr"><div dir="ltr"><br clear="all"><div><div dir="ltr" class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><br><div><span style="color:rgb(153,153,153)">Rick Morris | Managing Consultant, Professional Services & Training (EMEA) | M</span><span style="color:rgb(153,153,153);font-size:12.8px">: +1 954 224 8682 | </span><span style="color:rgb(153,153,153);font-size:12.8px">Skype: rycamor</span><br></div></div></div></div><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Jun 26, 2020 at 11:23 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">> In testing the in-memory query cache, I find that auto cache invalidation<br>
> works for UPDATE and INSERT queries, but does not work when calling plpgsql<br>
> functions.<br>
> <br>
> Is there any way to auto-invalidate cache for UPDATE/INSERTs within<br>
> functions? If not, is there any way to explicitly invalidate the cache with<br>
> additional code in a function?<br>
> <br>
> Using PgPool 4.1.1, with the following relevant settings:<br>
> <br>
> memqcache_auto_cache_invalidation = on<br>
> memqcache_expire = 0<br>
> relcache_expire = 0<br>
> enable_shared_relcache = on<br>
> memory_cache_enabled = on<br>
> black_function_list = 'test_update_function,test_insert_function'<br>
<br>
Currently there's no such functionality in Pgpool-II. It's a technical<br>
challenge for Pgpool-II because there's no way for Pgpool-II to know<br>
what table(s) are modified inside the function. </blockquote><div><br></div><div>Yes, I figured that was a serious challenge.</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">To implement such<br>
functionality, Pgpool-II needs to know:<br>
<br>
1) whether the SELECT modifies tables or not.<br>
<br>
2) if #1 is yes, what table(s) will be modified in the SELECT<br>
<br>
For this purpose we need to invent a new parameter to provide the<br>
information above. Suggestions, or even better, patches are welcome.<br></blockquote><div><br></div><div>I was thinking in terms of an explicit function in PgPool-II to invalidate cache on chosen tables.<br><br>For example if I call a function <b>SELECT func_that_updates_table_a();</b>, could I then call a function something like <b>SELECT pgpool_cache_invalidate('table_a');</b> and have the cache for that table explicitly invalidated? Or would it be possible to put that sort of function call inside a function, so the function might do something like (pseudocode):<br><br><b>BEGIN</b></div></div><b><br></b><blockquote style="margin:0 0 0 40px;border:none;padding:0px"><div class="gmail_quote"><div><b>INSERT INTO table_a VALUES('value1','value2');<br>UPDATE table_b SET value_a='something' WHERE value_b='something';<br><br>PERFORM pgpool_cache_invalidate('table_a');<br>PERFORM pgpool_cache_invalidate('table_b');<br><br></b></div></div></blockquote><b>END;<br></b><div class="gmail_quote"><div><br>Does something like this seem worth investigating?<br><br>Thanks,</div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<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_en.php</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
</blockquote></div></div>