[pgpool-general: 9427] Clarification on query results cache visibility

[Achilleas Mantzios - cloud]

Dear pgpool people

Yesterday during some tests which involve :  java app (wildfly) --> 
pgpool-II 4.6 --> pgsql 17.4 we came to a situation that a new 
connection could see stale or invalid cache data, meaning that the 
actual DB contents (5432) were not depicted on certain pgpool 
connections (9999).

The problem exists seemingly only for inherited tables that share the 
same name, and belong to different schemas.

The tables are :

amantzio@[local]/dynacom=# \d+ public.useroptions
                                                Table "public.useroptions"
  Column  |          Type          | Collation | Nullable | Default | 
Storage  | Compression | Stats target | Description
----------+------------------------+-----------+----------+---------+----------+-------------+--------------+------------- 

username | character varying(200) |           | not null |         | 
extended |             |              |
app      | text                   |           | not null |         | 
extended |             |              |
detail   | text                   |           | not null |         | 
extended |             |              |
urlext   | text                   |           | not null |         | 
extended |             |              |
Indexes:
    "useroptions_pkey" PRIMARY KEY, btree (username, app, detail)
Child tables: bdelosnav.useroptions,
              bdynacom.useroptions,
              bdynagas.useroptions,
              prominencemaritime.useroptions
Access method: heap

amantzio@[local]/dynacom=#

In pgpool we have : memory_cache_enabled = on . All other cache related 
params are at default.

In the specific tests we use access to bdynacom.useroptions, but 
accessing without fully qualified via search_path. But access of those 
rows from within public.useroptions also exhibit the problem(s).

We have on the actual DB directly :

achill at smadevnu:~/workspace/gatewaynet % psql
psql (17.4)
Type "help" for help.

amantzio@[local]/dynacom=# SELECT tableoid::regclass, app, urlext FROM 
useroptions WHERE username = 'Ioannis Mazarakis' AND app IN 
('performreport') AND detail = '';
       tableoid       |      app      |   urlext
----------------------+---------------+------------
bdynacom.useroptions | performreport | ?zz=foobar
(1 row)

However querying against pgpool I see a different version of the data:

achill at smadevnu:~/workspace/gatewaynet % psql -p 9999
psql (17.4)
Type "help" for help.

amantzio@[local]/dynacom=# SELECT app, urlext FROM useroptions WHERE 
username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail = 
'';
      app      |        urlext
---------------+----------------------
performreport | ?group=yes&groupno=7
(1 row)

amantzio@[local]/dynacom=#

repeating with including tableoid::regclass or just /*FORCE QUERY 
CACHE*/ to beat the cache :

/*FORCE QUERY CACHE*/ SELECT app, urlext FROM useroptions WHERE username 
= 'Ioannis Mazarakis' AND app IN ('performreport') AND detail = '';
      app      |   urlext
---------------+------------
performreport | ?zz=foobar
(1 row)

Using a another new user does not demo the problem :

achill at smadevnu:~/workspace/gatewaynet % psql -p 9999 -U stsoukalas
psql (17.4)
Type "help" for help.

stsoukalas@[local]/dynacom=# SELECT app, urlext FROM useroptions WHERE 
username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail = 
'';
      app      |   urlext
---------------+------------
performreport | ?zz=foobar
(1 row)

now logging with another user :

achill at smadevnu:~ % psql -p 9999 -U imazarakis dynacom
psql (17.4)
Type "help" for help.

imazarakis@[local]/dynacom=> SELECT app, urlext FROM useroptions WHERE 
username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail = 
'';
      app      |   urlext
---------------+------------
performreport | ?zz=foobar
(1 row)

imazarakis@[local]/dynacom=> /*FORCE QUERY CACHE*/  SELECT app, urlext 
FROM useroptions WHERE username = 'Ioannis Mazarakis' AND app IN 
('performreport') AND detail = '';
      app      |    urlext
---------------+---------------
performreport | ?zz=achillbar
(1 row)

imazarakis@[local]/dynacom=> update useroptions set urlext=urlext WHERE 
username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail = 
'';
UPDATE 1
imazarakis@[local]/dynacom=> SELECT app, urlext FROM useroptions WHERE 
username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail = 
'';
      app      |    urlext
---------------+---------------
performreport | ?zz=achillbar
(1 row)

As we said, non inherited tables do not seem to suffer from the same issue.

Now granted , either :

a) disabling the memory cache  or

b) placing a specified comment HINT in front of the query  or

c) specifying the affected tables in cache_unsafe_memqcache_table_list or

d)or using BEGIN ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  , or 
above

as you explain here : 
https://pgsqlpgpool.blogspot.com/2021/04/visibility-with-query-cache.html

(btw, why non inherited tables work so nice with the cache ??? without 
the issue above?)

If the blog is still valid, why not state this explicitly inside the docs?

And if the blog is no longer relevant , please include such a statement.

So the above could help alleviate the problem ... however:

a) disabling the cache ... is not ideal , I mean why not have such a 
powerful feature ?

b) placing comments in 1000s of queries is not an option

c) we would not like to restrict tables based on a bug

d) same as b) rewriting all our transactions is not an option , also we 
would not like to change the default default_transaction_isolation.

So can you please explain what is the state of affairs regarding pgpool 
query cache and inherited tables? or pgpool caching and visibility ?

Granted, we didn't take the time to go into debugging, we just report 
this as a possible issue.

We submitted an issue in github as well : 
https://github.com/pgpool/pgpool2/issues/102


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250425/4acff994/attachment.htm>