Toggles for caching exceptions in in-memory query cache

[lc-guy]

The documentation says:

I'm facing a use-case with Grafana, where I wish to cache some heavy queries being made on some dashboards. These queries frequently feature timestamps with timezones or somesuch, functions running on timestamps, etc. I'm using pgpool in raw mode for that, as the caching functionality is all I wish to use.

However, despite moving these queries around, there always seems to be some element somewhere or other that triggers the immutability filter in pgpool, and renders the queries I want uncacheable.

Would it be possible to add a configuration toggle for each of the big "categories" of caching exceptions, such that one can bypass this initial filtering? I understand the footguns involved (in particular with non-immutable functions, timestamps with timezones potentially giving different results, ...), but in my specific use case, these do not really matter as there is only one well-known client requesting this information.

Thank you in advance.

[tatsuo-ishii]

[previous example was not appropriate and removed.] You can create a view and register it to cache_safe_memqcache_table_list. cache_safe_memqcache_table_list = 'v1'

test=# create table t1(id int, tm text);
CREATE TABLE
test=# insert into t1 values(1, '2024-07-01 20:36:00+0900');
INSERT 0 1
test=# select id,tm::timestamptz from t1 where id = 1;    -- this result is not cached because of type cast
 id |           tm           
----+------------------------
  1 | 2024-07-01 20:36:00+09
(1 row)
test=#  create view v1 as select id,tm::timestamptz from t1;    -- create wrapping view
CREATE VIEW
test=# select * from v1 where id = 1;     -- this result is cached

A downside of this method is, even if t1 gets updated, query result of v1 does not automatically get updated. You need to execute a "dummy update" (e,g, update v1 set id = 1 where id = 1) or drop/re-create v1.

[tatsuo-ishii]

You need to execute a "dummy update" (e,g, update v1 set id = 1 where id = 1) or drop/re-create v1.

Actually more safer dummy update would be: update v1 set id = 1 where false;

BTW, this method (using view) cannot be used if the SELECT in question uses joins because PostgreSQL's updatable view does not allow to use joins.

[tatsuo-ishii]

Would it be possible to add a configuration toggle for each of the big "categories" of caching exceptions, such that one can bypass this initial filtering?

I think defining the reasonable "category" for everyone is difficult as each user has their own use case. What about more simple solution?: allow to add a comment to queries like "/ QUERY CACHE/" which force to create a cache entry for the query.

[lc-guy]

You need to execute a "dummy update" (e,g, update v1 set id = 1 where id = 1) or drop/re-create v1.

Actually more safer dummy update would be: update v1 set id = 1 where false;

BTW, this method (using view) cannot be used if the SELECT in question uses joins because PostgreSQL's updatable view does not allow to use joins.

This seems to work after testing. However, the tables I use in my queries tend to be generated dynamically; having to generate a view for each of them, and adding the views to the configuration every time, would be non-trivial.

Would it be possible to add a configuration toggle for each of the big "categories" of caching exceptions, such that one can bypass this initial filtering?

I think defining the reasonable "category" for everyone is difficult as each user has their own use case. What about more simple solution?: allow to add a comment to queries like "/ QUERY CACHE/" which force to create a cache entry for the query.

This would be perfect for me, though, if that could be implemented. Thank you!

[tatsuo-ishii]

I have posted a patch to implement this in the master branch. I change the comment string to "/FORCE QUERY CACHE/" as it seems cleaner to users. https://www.pgpool.net/pipermail/pgpool-hackers/2024-July/004480.html

[tatsuo-ishii]

The patch has been committed. https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=bdbee93ceb9f1452f9eab56077c8041a68e53ba3 Will appear in upcoming Pgpool-II 4.6.