[pgpool-general: 7108] Re: Query cache invalidation for functions?

[Rick Morris]

Rick Morris | Managing Consultant, Professional Services & Training (EMEA)
| M: +1 954 224 8682 | Skype: rycamor


On Fri, Jun 26, 2020 at 11:23 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> > In testing the in-memory query cache, I find that auto cache invalidation
> > works for UPDATE and INSERT queries, but does not work when calling
> plpgsql
> > functions.
> >
> > Is there any way to auto-invalidate cache for UPDATE/INSERTs within
> > functions? If not, is there any way to explicitly invalidate the cache
> with
> > additional code in a function?
> >
> > Using PgPool 4.1.1, with the following relevant settings:
> >
> > memqcache_auto_cache_invalidation = on
> > memqcache_expire = 0
> > relcache_expire = 0
> > enable_shared_relcache = on
> > memory_cache_enabled = on
> > black_function_list = 'test_update_function,test_insert_function'
>
> Currently there's no such functionality in Pgpool-II. It's a technical
> challenge for Pgpool-II because there's no way for Pgpool-II to know
> what table(s) are modified inside the function.


Yes, I figured that was a serious challenge.


> To implement such
> functionality, Pgpool-II needs to know:
>
> 1) whether the SELECT modifies tables or not.
>
> 2) if #1 is yes, what table(s) will be modified in the SELECT
>
> For this purpose we need to invent a new parameter to provide the
> information above. Suggestions, or even better, patches are welcome.
>

I was thinking in terms of an explicit function in PgPool-II to invalidate
cache on chosen tables.

For example if I call a function *SELECT func_that_updates_table_a();*,
could I then call a function something like *SELECT
pgpool_cache_invalidate('table_a');* 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):

*BEGIN*







*INSERT INTO table_a VALUES('value1','value2');UPDATE table_b SET
value_a='something' WHERE value_b='something';PERFORM
pgpool_cache_invalidate('table_a');PERFORM
pgpool_cache_invalidate('table_b');*


*END;*

Does something like this seem worth investigating?

Thanks,


>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200627/7f39ad76/attachment.html>