<!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 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></p>
<p></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">
<pre wrap="" class="moz-quote-pre">
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">
</pre>
<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">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.
Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: <a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp/index_en/">http://www.sraoss.co.jp/index_en/</a>
<a class="moz-txt-link-freetext" href="Japanese:http://www.sraoss.co.jp">Japanese:http://www.sraoss.co.jp</a>
</pre>
</blockquote>
</body>
</html>