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

[Tatsuo Ishii]

>> 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?

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp