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

[Rick Morris]

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


On Sat, Jun 27, 2020 at 1:24 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:

> >> 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?
>
> I think it is possible to do that if we implement the cache
> invalidation feature in PCP command. Invoking PCP command is already
> possible by using pgpool_adm extensions.
>
> https://www.pgpool.net/docs/latest/en/html/pgpool-adm.html
>
> pgpool_adm calls C function interface, which is similar to libpq, for
> pcp commands.
>
> So what we need are:
>
> 1) implement new PCP command to invalidate query cache by specifying
> table, something like:
>    pcp_invalidate_query_cache [other standard options such as PCP port
> number] table_name
>
> 2) implement new pgpool_adm function something like:
>    pgpool_adm_pcp_invalidate_query_cache([other pgpool_adm standard
> parameters], 'table_name');
>
> What do you think?
>

This sounds great. I'll be glad to look into it.


>
> 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/20200630/f9f994fa/attachment.html>