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

[Tatsuo Ishii]

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

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