<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hello Tatsuo<br>
</p>
<div class="moz-cite-prefix">On 27/4/25 15:01, Tatsuo Ishii wrote:<br>
</div>
<blockquote type="cite"
cite="mid:20250427.210142.506988899151788566.ishii@postgresql.org">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">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>
<pre class="moz-quote-pre" wrap="">
the app (wildfly) has this in the conf :
<new-connection-sql>SET application_name to 'SMA ';select
set_search_path('bdynacom,epaybdynacom,epay');</new-connection-sql>
This function does some logic and then sets the search path: so by
calling it with the above argument values it yields :
anonym,bdynacom,epaybdynacom,epay,"$user", public
So the potentially relevant schemata here are the ones that contain
our tables useroptions : bdynacom and public.
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.
</pre>
</blockquote>
</blockquote>
<pre class="moz-quote-pre" wrap="">
Ok, the search_path is set by the app and only bdynacom and public
include useroptions table.
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">*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.*
</pre>
</blockquote>
</blockquote>
<pre class="moz-quote-pre" wrap="">
That's unfortunatete.
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">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?
</pre>
</blockquote>
</blockquote>
<pre class="moz-quote-pre" wrap="">
No, I am not familiar with Java applications.</pre>
</blockquote>
<p>Ok, the JDBC driver handles prepared statements using the low
level protocol : Parse, Bind, Execute flow (Extended Query
protocol): <br>
</p>
<p><a class="moz-txt-link-freetext" href="https://jdbc.postgresql.org/documentation/server-prepare/#server-prepared-statements">https://jdbc.postgresql.org/documentation/server-prepare/#server-prepared-statements</a></p>
<p><a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY">https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY</a></p>
<p>If the low level protocol is involved somehow in the bug I am
unaware how it could be reproduced. But at the moment we are
unsure about this.<br>
</p>
<blockquote type="cite"
cite="mid:20250427.210142.506988899151788566.ishii@postgresql.org">
<pre class="moz-quote-pre" wrap="">
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">From the tests I am doing today, I am very confident that this hasn't
got to do with search_path.
It is like it created a "hidden" cache entry that is only surfacing
under some conditions.
Please look :
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
How did you set search_path? According to the explanation above, the
search_path was set by Java app.</pre>
</blockquote>
<p>Like I wrote before :</p>
<pre class="moz-quote-pre" wrap=""><new-connection-sql>SET application_name to 'SMA ';select set_search_path('bdynacom,epaybdynacom,epay');</new-connection-sql>
it runs the above at the start of every connection.
</pre>
<p></p>
<blockquote type="cite"
cite="mid:20250427.210142.506988899151788566.ishii@postgresql.org">
<pre class="moz-quote-pre" wrap="">
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">straight to pgsql :
achill@smadevnu:~ % psql -U amantzio
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
Maybe you forgot to copy database name here? I am asking because if
the database name is missing, psql will connect to "amantzio"
database, which is different database from the pgpool session.</pre>
</blockquote>
No, I am using PgSQL since 2000, I'd be a very lousy dba if I made
such mistakes. <a id="id-1.7.3.22.3.4.5.1.1" class="indexterm"
name="id-1.7.3.22.3.4.5.1.1"></a> <code class="envar">PGDATABASE
is set to : dynacom , and there is no db : amantzio.<br>
</code>
<blockquote type="cite"
cite="mid:20250427.210142.506988899151788566.ishii@postgresql.org">
<pre class="moz-quote-pre" wrap="">
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">psql (17.4)
Type "help" for help.
amantzio@[local]/dynacom=# SELECT tableoid::regclass, app, urlext FROM
useroptions WHERE username = 'Achilleas
Mantzios' AND app IN ('performreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+----------------------
bdynacom.useroptions | performreport | *?group=yes&groupno=7 <-- real
value*
(1 row)
marked as bold above is the correct value (real value).
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
Ok.
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">1st pgpool session :
achill@smadevnu:~ % psql -p 9999 -U amantzio dynacom
psql (17.4)
Type "help" for help.
amantzio@[local]/dynacom=# SELECT app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app IN ('performreport') AND d
etail = '';
app | urlext
---------------+----------------------
performreport | *?group=yes&groupno=2 <-- old value*
(1 row)
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
Before starting the query, can you set below?
pgpool set log_per_node_statement = on;
pgpool set client_min_messages = log;
(you can also set these in pgpool.conf as:
log_per_node_statement = on
set client_min_messages = log)
These will print the query sent to backend and print whether the query
result was fetched from query cache. This will help to know whether
pgpool retrieves the data from query cache or not.
At this point I guess the reason you see the old value could be, the
query cache entry was not removed by previous update for some reasons.
When a SELECT is executed and a query cache entry is created, pgpool
creates a file containing the cache information. This is used to
invalidate cache when the table is updated. If the file is created,
you can see it as: logdir/oid_of_database/oid_of_table. ("logdir" is
set in pgpool.conf). In this case oid_of_database = oid of dynacom,
and oid_of_table = oid of bdynacom.useroptions. Can you confirm?</pre>
</blockquote>
Thank you for the useful hints.<br>
<p>Yes I verify the presence of the file, and indeed it bears the
oid of bdynacom.useroptions :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">dynacom=#
select oid from pg_database where datname = 'dynacom';
</span><br>
oid <br>
-------
<br>
17748
<br>
(1 row)
<br>
dynacom=# select oid from pg_namespace where nspname =
'bdynacom';
<br>
oid <br>
-------
<br>
16902
<br>
(1 row)
<br>
dynacom=# select oid, oid::regclass, relnamespace from pg_class
where relname='useroptions' and relnamespace=16902;
<br>
oid | oid | relnamespace <br>
-------+----------------------+--------------
<br>
19097 | bdynacom.useroptions | 16902
<br>
(1 row)<br>
<br>
</span><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">jboss@smadevnu:~
% sudo ls -l /var/log/pgpool/oiddir/17748/
</span><br>
Password:
<br>
total 1
<br>
-rw------- 1 pgpool pgpool 8 Apr 27 19:00 19097</span></p>
<p><span style="font-family:monospace"><br>
</span></p>
<p><span style="font-family:monospace">I will provide the rest of
answers tomorrow. Hopefully.<br>
</span></p>
<blockquote type="cite"
cite="mid:20250427.210142.506988899151788566.ishii@postgresql.org">
<pre class="moz-quote-pre" wrap="">
</pre>
<blockquote type="cite">
<pre class="moz-quote-pre" wrap="">amantzio@[local]/dynacom=# SELECT tableoid::regclass, app, urlext FROM
useroptions WHERE username = 'Achilleas Mantzios' AND app IN ('p
erformreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+-----------
bdynacom.useroptions | performreport | *?group=no <-- also old value*
(1 row)
bow besides the false stale value ( *?group=yes&groupno=2 ) *if we add
tableoid in the select we also get another also false and stale value
: "group=no"
2nd pgpool session
pgpool@smadevnu:~ % psql -p 9999 -U amantzio dynacom
psql (15.7, server 17.4)
WARNING: psql major version 15, server major version 17.
Some psql features might not work.
Type "help" for help.
dynacom=# SELECT app, urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport
') AND detail = '';
app | urlext
---------------+----------------------
performreport | *?group=yes&groupno=2*
(1 row)
dynacom=# /*NO QUERY CACHE*/ SELECT app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app
IN ('performreport') AND detail = '';
app | urlext
---------------+----------------------
performreport | *?group=yes&groupno=7 <---- real value*
(1 row)
dynacom=# /*FORCE QUERY CACHE*/ SELECT app, urlext FROM useroptions
WHERE username = 'Achilleas Mantzios' AND
app IN ('performreport') AND detail = '';
app | urlext
---------------+-----------
performreport | *?group=no <-- also old value*
(1 row)
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.
now comes the ultra crazy part :
same session :
dynacom=# SELECT tableoid::regclass,app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app
IN ('performreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+----------------------
bdynacom.useroptions | performreport | ?group=yes&groupno=7
(1 row)
dynacom=# SELECT tableoid::regclass, app, urlext FROM useroptions
WHERE username = 'Achilleas Mantzios' AND ap
p IN ('performreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+-----------
bdynacom.useroptions | performreport | ?group=no
(1 row)
can you spot the difference ? it is the space after "regclass," here :
tableoid::regclass, app
here without the tableoid :
dynacom=# SELECT app, urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport
') AND detail = '';
app | urlext
---------------+----------------------
performreport | ?group=yes&groupno=2
(1 row)
dynacom=# SELECT app,urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport'
) AND detail = '';
app | urlext
---------------+----------------------
performreport | ?group=yes&groupno=7
(1 row)
only different thing is the space in the select ,
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">
That's an expected behavior. When pgpool fetches a cache entry, it is
based on the exact string match of the query strings. So even a white
space matters.</pre>
</blockquote>
<p>Ok but seeing stale and irrelevant results for the same query is
not right.</p>
<p>Maybe you could thing of using postgresql's queryid ? just saying<br>
</p>
<blockquote type="cite"
cite="mid:20250427.210142.506988899151788566.ishii@postgresql.org">
<pre class="moz-quote-pre" wrap="">
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>
Japanese:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
</pre>
</blockquote>
</body>
</html>