<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p><br>
</p>
<div class="moz-cite-prefix">On 26/4/25 13:39, Achilleas Mantzios
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:e7987fd5-f52a-4172-b5df-6894ffde5c92@cloud.gatewaynet.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<p><br>
</p>
<div class="moz-cite-prefix">On 26/4/25 10:21, Tatsuo Ishii wrote:<br>
</div>
<blockquote type="cite"
cite="mid:20250426.162149.2115672035155097410.ishii@postgresql.org">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">sorry above I meant to say :
But both the inserts and the selects were done with table not being
qualified , and with a constant search_path.
The thing to add here is that search_path is set by calling : void
set_search_path(text) a volatile function.
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">I'm confused. So the search_path is literary always constant (not
something like '$user,public'), or the search_path is changed by
set_search_path?</pre>
</blockquote>
<p>the app (wildfly) has this in the conf :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;"><new-connection-sql>SET
application_name to 'SMA ';select
set_search_path('bdynacom,epaybd</span>ynacom,epay');</new-connection-sql><br>
</span></p>
<p>This function does some logic and then sets the search path: so
by calling it with the above argument values it yields : <span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">anonym,bdynacom,epaybdynacom,epay,"$user",
public</span></span></p>
<p>So the potentially relevant schemata here are the ones that
contain our tables useroptions : bdynacom and public.</p>
<p>The above is called at the start of the connection, only. No
search path setting in the code. We base our multi-tenancy on
this technique to be more precise, so every tenant application
sets its own search path, but only once at the start of the
connection. So all insertions and selects and updates happen on
the unqualified table : useroptions, which is
bdynacom.useroptions. <br>
</p>
<p><b>The problem was discovered inside the same app, with same
search_path, with the same user, it inserted the new value,
but could not read it. We were unable to reproduce this with
plain SQL.</b> </p>
<p>So we may need to try to reproduce it with java and send you
the full working bug. Would you like us to to do this?<br>
</p>
<blockquote type="cite"
cite="mid:20250426.162149.2115672035155097410.ishii@postgresql.org">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">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 :
<a class="moz-txt-link-freetext"
href="https://pgsqlpgpool.blogspot.com/2021/04/visibility-with-query-cache.html"
moz-do-not-send="true">https://pgsqlpgpool.blogspot.com/2021/04/visibility-with-query-cache.html</a>
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">Sorry but I don't understand why "BEGIN ; SET TRANSACTION ISOLATION
LEVEL REPEATABLE READ ," is useful for your problem.
</pre>
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">(btw, why non inherited tables work so nice with the cache ??? without
the issue above?)
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">I don't think inheritance is relevant. See above.
</pre>
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">If the blog is still valid, why not state this explicitly inside the
docs?
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">I think the blog is still valid but it's not connected to your
problem.
</pre>
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">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 ?
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">As stated above, the query cache does not take account of schema
search path. I think that's source of the problem. Probably in
addition to user name, database and bind parameters (if any), the
schema search path should be added to the data when pgpool calculates
a MD5 hash (which is the cache key) on it. However this requires
pgpool to obtain the search path using "show search_path", which adds
an additional overhead while fetching query cache. Any idea?
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">regarding the MD5 calculation, instead of the schema why not the
effective oid of the actual table?
</pre>
</blockquote>
</blockquote>
<pre wrap="" class="moz-quote-pre">Because the number of tables included in a SELECT is not necessarily
1, it could be 2, 3 or more, or even 0.
And even if we used table oids, the would add extract. If we have to
pay an extra cost, using search_path is simpler.</pre>
</blockquote>
</blockquote>
<p><br>
</p>
<p>From the tests I am doing today, I am very confident that this
hasn't got to do with search_path. <br>
</p>
<p>It is like it created a "hidden" cache entry that is only
surfacing under some conditions.</p>
<p>Please look :</p>
<p>straight to pgsql :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@<a class="moz-txt-link-freetext" href="smadevnu:~">smadevnu:~</a>
% psql -U amantzio
</span><br>
psql (17.4)
<br>
Type "help" for help.
<br>
<br>
amantzio@[local]/dynacom=# SELECT <a class="moz-txt-link-freetext" href="tableoid::regclass">tableoid::regclass</a>, app,
urlext FROM useroptions WHERE username = 'Achilleas<br>
Mantzios' AND app IN ('performreport') AND detail = '';
<br>
tableoid | app | urlext <br>
----------------------+---------------+----------------------
<br>
bdynacom.useroptions | performreport | <b>?group=yes&groupno=7
<-- real value</b><br>
(1 row)<br>
</span></p>
<p>marked as bold above is the correct value (real value).<br>
</p>
<p>1st pgpool session :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@<a class="moz-txt-link-freetext" href="smadevnu:~">smadevnu:~</a>
% psql -p 9999 -U amantzio dynacom
</span><br>
psql (17.4)
<br>
Type "help" for help.
<br>
<br>
amantzio@[local]/dynacom=# SELECT app, urlext FROM useroptions
WHERE username = 'Achilleas Mantzios' AND app IN
('performreport') AND d<br>
etail = '';
<br>
app | urlext <br>
---------------+----------------------
<br>
performreport | <b>?group=yes&groupno=2 <-- old value</b>
<br>
(1 row)
<br>
<br>
amantzio@[local]/dynacom=# SELECT <a class="moz-txt-link-freetext" href="tableoid::regclass">tableoid::regclass</a>, app,
urlext FROM useroptions WHERE username = 'Achilleas Mantzios'
AND app IN ('p<br>
erformreport') AND detail = '';
<br>
tableoid | app | urlext <br>
----------------------+---------------+-----------
<br>
bdynacom.useroptions | performreport | <b>?group=no <--
also old value</b>
<br>
(1 row)<br>
<br>
</span></p>
<p>bow besides the false stale value ( <span
style="font-family:monospace"><b>?group=yes&groupno=2 ) </b></span>if
we add tableoid in the select we also get another also false and
stale value : "<span style="font-family:monospace">group=no</span>"
<br>
</p>
<p>2nd pgpool session<br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">pgpool@<a class="moz-txt-link-freetext" href="smadevnu:~">smadevnu:~</a>
% psql -p 9999 -U amantzio dynacom
</span><br>
psql (15.7, server 17.4)
<br>
WARNING: psql major version 15, server major version 17.
<br>
Some psql features might not work.
<br>
Type "help" for help.
<br>
<br>
dynacom=# SELECT app, urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport<br>
') AND detail = '';
<br>
app | urlext <br>
---------------+----------------------
<br>
performreport | <b>?group=yes&groupno=2</b>
<br>
(1 row)
<br>
<br>
dynacom=# /*NO QUERY CACHE*/ SELECT app, urlext FROM useroptions
WHERE username = 'Achilleas Mantzios' AND app<br>
IN ('performreport') AND detail = '';
<br>
app | urlext <br>
---------------+----------------------
<br>
performreport | <b>?group=yes&groupno=7 <---- real
value</b>
<br>
(1 row)
<br>
<br>
dynacom=# /*FORCE QUERY CACHE*/ SELECT app, urlext FROM
useroptions WHERE username = 'Achilleas Mantzios' AND <br>
app IN ('performreport') AND detail = '';
<br>
app | urlext <br>
---------------+-----------
<br>
performreport | <b>?group=no <-- also old value</b>
<br>
(1 row)</span><br>
</p>
<p>In the first select we get the same old version as in the 1st
session, in the second select (no query cache) we get the real
correct value , while in the last select by forcing query cache,
we get the same value as if putting the tableoid in the select.</p>
<p><br>
</p>
<p>now comes the ultra crazy part :</p>
<p>same session :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">dynacom=#
SELECT <a class="moz-txt-link-freetext" href="tableoid::regclass,app">tableoid::regclass,app</a>, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app</span><br>
IN ('performreport') AND detail = '';
<br>
tableoid | app | urlext <br>
----------------------+---------------+----------------------
<br>
bdynacom.useroptions | performreport | ?group=yes&groupno=7
<br>
(1 row)
<br>
<br>
dynacom=# SELECT <a class="moz-txt-link-freetext" href="tableoid::regclass">tableoid::regclass</a>, app, urlext FROM
useroptions WHERE username = 'Achilleas Mantzios' AND ap<br>
p IN ('performreport') AND detail = '';
<br>
tableoid | app | urlext <br>
----------------------+---------------+-----------
<br>
bdynacom.useroptions | performreport | ?group=no
<br>
(1 row)<br>
<br>
</span></p>
<p>can you spot the difference ? it is the space after "regclass,"
here : <span style="font-family:monospace"><a class="moz-txt-link-freetext" href="tableoid::regclass">tableoid::regclass</a>,
app</span></p>
<p>here without the tableoid :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">dynacom=#
SELECT app, urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport</span><br>
') AND detail = '';
<br>
app | urlext <br>
---------------+----------------------
<br>
performreport | ?group=yes&groupno=2
<br>
(1 row)
<br>
<br>
dynacom=# SELECT app,urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport'<br>
) AND detail = '';
<br>
app | urlext <br>
---------------+----------------------
<br>
performreport | ?group=yes&groupno=7
<br>
(1 row)<br>
<br>
</span></p>
<p>only different thing is the space in the select ,</p>
<p>so I am afraid the situation is much worse than simply fixing the
search_path (which obviously is not relevant here)<br>
</p>
<p><br>
</p>
<blockquote type="cite"
cite="mid:e7987fd5-f52a-4172-b5df-6894ffde5c92@cloud.gatewaynet.com">
<blockquote type="cite"
cite="mid:20250426.162149.2115672035155097410.ishii@postgresql.org">
<pre wrap="" class="moz-quote-pre">
Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: <a class="moz-txt-link-freetext"
href="http://www.sraoss.co.jp/index_en/" moz-do-not-send="true">http://www.sraoss.co.jp/index_en/</a>
<a class="moz-txt-link-freetext" href="Japanese:http://www.sraoss.co.jp"
moz-do-not-send="true">Japanese:http://www.sraoss.co.jp</a>
</pre>
</blockquote>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre wrap="" class="moz-quote-pre">_______________________________________________
pgpool-general mailing list
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
</pre>
</blockquote>
</body>
</html>