<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Dear pgpool people<br>
</p>
<p>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). <br>
</p>
<p>The problem exists seemingly only for inherited tables that share
the same name, and belong to different schemas.</p>
<p>The tables are : <br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">amantzio@[local]/dynacom=#
\d+ public.useroptions
</span><br>
Table
"public.useroptions"
<br>
Column | Type | Collation | Nullable |
Default | Storage | Compression | Stats target | Description <br>
----------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
<br>
username | character varying(200) | | not null |
| extended | | | <br>
app | text | | not null |
| extended | | | <br>
detail | text | | not null |
| extended | | | <br>
urlext | text | | not null |
| extended | | | <br>
Indexes:
<br>
"useroptions_pkey" PRIMARY KEY, btree (username, app, detail)
<br>
Child tables: bdelosnav.useroptions,
<br>
bdynacom.useroptions,
<br>
bdynagas.useroptions,
<br>
prominencemaritime.useroptions
<br>
Access method: heap
<br>
<br>
amantzio@[local]/dynacom=# <br>
<br>
</span>In pgpool we have : <span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">memory_cache_enabled
= on</span></span> . All other cache related params are at
default.<br>
</p>
<p>In the specific tests we use access to <span
style="font-family:monospace">bdynacom.useroptions</span>, but
accessing without fully qualified via search_path. But access of
those rows from within public.useroptions also exhibit the
problem(s).</p>
<p>We have on the actual DB directly :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~/workspace/gatewaynet
% psql
</span><br>
psql (17.4)
<br>
Type "help" for help.
<br>
<br>
amantzio@[local]/dynacom=# SELECT tableoid::regclass, app,
urlext FROM useroptions WHERE username = 'Ioannis Mazarakis' AND
app IN ('performreport') AND detail = '';
<br>
tableoid | app | urlext <br>
----------------------+---------------+------------
<br>
bdynacom.useroptions | performreport | ?zz=foobar
<br>
(1 row)
<br>
</span></p>
<p>However querying against pgpool I see a different version of the
data: <br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~/workspace/gatewaynet
% psql -p 9999
</span><br>
psql (17.4)
<br>
Type "help" for help.
<br>
<br>
amantzio@[local]/dynacom=# SELECT app, urlext FROM useroptions
WHERE username = 'Ioannis Mazarakis' AND app IN
('performreport') AND detail = '';
<br>
app | urlext <br>
---------------+----------------------
<br>
performreport | ?group=yes&groupno=7
<br>
(1 row)
<br>
<br>
amantzio@[local]/dynacom=#<br>
</span></p>
<p>repeating with including <span style="font-family:monospace">tableoid::regclass</span>
or just <span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">/*FORCE QUERY
CACHE*/</span> </span>to beat the cache :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">/*FORCE QUERY
CACHE*/ SELECT app, urlext FROM useroptions WHERE username =
'Ioannis Mazarakis' AND app IN ('performreport') AND detail =
'';
</span><br>
app | urlext <br>
---------------+------------
<br>
performreport | ?zz=foobar
<br>
(1 row)<br>
</span></p>
<p>Using a another new user does not demo the problem : <br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~/workspace/gatewaynet
% psql -p 9999 -U stsoukalas
</span><br>
psql (17.4)
<br>
Type "help" for help.
<br>
<br>
stsoukalas@[local]/dynacom=# SELECT app, urlext FROM useroptions
WHERE username = 'Ioannis Mazarakis' AND app IN
('performreport') AND detail = '';
<br>
app | urlext <br>
---------------+------------
<br>
performreport | ?zz=foobar
<br>
(1 row)<br>
</span></p>
<p>now logging with another user :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~
% psql -p 9999 -U imazarakis dynacom
</span><br>
psql (17.4)
<br>
Type "help" for help.
<br>
<br>
imazarakis@[local]/dynacom=> SELECT app, urlext FROM
useroptions WHERE username = 'Ioannis Mazarakis' AND app IN
('performreport') AND detail = '';
<br>
app | urlext <br>
---------------+------------
<br>
performreport | ?zz=foobar
<br>
(1 row)
<br>
<br>
imazarakis@[local]/dynacom=> /*FORCE QUERY CACHE*/ SELECT
app, urlext FROM useroptions WHERE username = 'Ioannis
Mazarakis' AND app IN ('performreport') AND detail = '';
<br>
app | urlext <br>
---------------+---------------
<br>
performreport | ?zz=achillbar
<br>
(1 row)
<br>
<br>
imazarakis@[local]/dynacom=> update useroptions set
urlext=urlext WHERE username = 'Ioannis Mazarakis' AND app IN
('performreport') AND detail = '';
<br>
UPDATE 1
<br>
imazarakis@[local]/dynacom=> SELECT app, urlext FROM
useroptions WHERE username = 'Ioannis Mazarakis' AND app IN
('performreport') AND detail = '';
<br>
app | urlext <br>
---------------+---------------
<br>
performreport | ?zz=achillbar
<br>
(1 row)<br>
<br>
</span></p>
<p>As we said, non inherited tables do not seem to suffer from the
same issue.<br>
</p>
<p>Now granted , either :</p>
<p>a) disabling the memory cache or</p>
<p>b) placing <span style="font-family:monospace">a specified
comment HINT in front of the query or</span></p>
<p>c) specifying the affected tables in <span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">cache_unsafe_memqcache_table_list</span>
or</span></p>
<p>d)<span style="font-family:monospace"> </span>or using BEGIN ;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ , or above</p>
<p>as you explain here :
<a class="moz-txt-link-freetext" href="https://pgsqlpgpool.blogspot.com/2021/04/visibility-with-query-cache.html">https://pgsqlpgpool.blogspot.com/2021/04/visibility-with-query-cache.html</a><br>
</p>
<p>(btw, why non inherited tables work so nice with the cache ???
without the issue above?)</p>
<p>If the blog is still valid, why not state this explicitly inside
the docs?</p>
<p>And if the blog is no longer relevant , please include such a
statement.<br>
</p>
<p>So the above could help alleviate the problem ... however:</p>
<p>a) disabling the cache ... is not ideal , I mean why not have
such a powerful feature ?<br>
</p>
<p>b) placing comments in 1000s of queries is not an option</p>
<p>c) we would not like to restrict tables based on a bug <br>
</p>
<p>d) same as b) rewriting all our transactions is not an option ,
also we would not like to change the default <span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">default_transaction_isolation</span>.<br>
</span></p>
<p>So can you please explain what is the state of affairs regarding
pgpool query cache and inherited tables? or pgpool caching and
visibility ?<br>
</p>
<p>Granted, we didn't take the time to go into debugging, we just
report this as a possible issue.</p>
<p>We submitted an issue in github as well :
<a class="moz-txt-link-freetext" href="https://github.com/pgpool/pgpool2/issues/102">https://github.com/pgpool/pgpool2/issues/102</a><br>
</p>
<p><br>
</p>
<p><br>
</p>
</body>
</html>