[pgpool-general: 7109] Re: Query cache invalidation for functions?
ishii at sraoss.co.jp
Sat Jun 27 14:24:38 JST 2020
>> 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
> *INSERT INTO table_a VALUES('value1','value2');UPDATE table_b SET
> value_a='something' WHERE value_b='something';PERFORM
> 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.
pgpool_adm calls C function interface, which is similar to libpq, for
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?
SRA OSS, Inc. Japan
More information about the pgpool-general