query results cache visibility #102
Description
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@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@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@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@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.