<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Good Day Tatsuo</p>
<p>I have not been able to reproduce via sql . <br>
</p>
<p>I will provide full log of pgpool as in the app (java) we didn't
get any useful logs , only entries like :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">2025-04-28
10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) SQL Warning Code: 0, SQLState: XX000 </span><br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) Parse message from frontend. <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) SQL Warning Code: 0, SQLState: XX000 <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) DB node id: 0 backend pid: 34753 statement: Parse:
COMMIT <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) SQL Warning Code: 0, SQLState: XX000 <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) Bind message from frontend. <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) SQL Warning Code: 0, SQLState: XX000 <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) DB node id: 0 backend pid: 34753 statement: Bind:
COMMIT <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) SQL Warning Code: 0, SQLState: XX000 <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) Execute message from frontend. <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) SQL Warning Code: 0, SQLState: XX000 <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) DB node id: 0 backend pid: 34753 statement: Execute:
COMMIT <br>
2025-04-28 10:07:01,717 WARN
[org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default
task-35) SQL Warning Code: 0, SQLState: XX000<br>
<br>
which do not seem to contain any useful info.<br>
</span></p>
<p>Please disregard any noise in the logs, we are interested on
useroptions .</p>
<p>The scenario goes like :</p>
<p>1) Front page queries the db for a specific useroption for a
subapp (saved from a previous click from within that app, we will
look into this later in step 2). <br>
</p>
<p>The prepared stmt is :</p>
<p>SELECT app, urlext FROM useroptions WHERE username = ? AND app IN
('status', 'performreport', 'freshwater', 'comms') AND detail =
'';</p>
<p>User name in our tests will be 'Achilleas Mantzios'</p>
<p>The front page uses this result to build the url for the
Performance Report subapp .<br>
</p>
<p>2) Then the user clicks into the Performance Report subapp , and
makes some clicks / selections which the app tries to store in
useroptions. It does so by investigating if there is any option
already stored or not. By issuing prep stmt:</p>
<p>select 1 from useroptions where username=? and
app='performreport' and detail=''</p>
<p>From the result it decides whether to insert or update (back then
there was no upsert so the app stayed that way), and stores the
users selections inside the app, depending on the result from the
above select by either :</p>
<p>insert into useroptions(username,app,detail,urlext)
values('"+username+"','performreport','',?)</p>
<p>or</p>
<p>update useroptions set urlext=? where username='"+username+"' and
app='performreport' and detail=''</p>
<p>3) then by visiting the front page again, (like in1) the front
page queries <br>
</p>
<p>SELECT app, urlext FROM useroptions WHERE username = ? AND app IN
('status', 'performreport', 'freshwater', 'comms') AND detail =
'';</p>
<p>and presents the new saved options to the Performance Report
subapp .</p>
<p>The problem that my colleague found is that while in 2) and
making a selection, although being stored correctly in the DB,
however, by clicking on the home page again, the user sees the
initially fetched result and not the just updated one.<br>
</p>
<p>Now I will present the actual test scenario pgpool log and some
queries from psql :<br>
</p>
<p>Initially we have , straight from the db</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~
% psql </span><br>
psql (17.4) <br>
Type "help" for help. <br>
<br>
amantzio@[local]/dynacom=# SET application_name to 'SMA ';select
set_search_path('bdynacom,epaybdynacom,epay'); <br>
SET <br>
set_search_path <br>
----------------- <br>
<br>
(1 row) <br>
<br>
amantzio@[local]/dynacom=# select * from useroptions where
username = 'Achilleas Mantzios'; <br>
username | app | detail | urlext
<br>
--------------------+---------------+--------+---------------------- <br>
Achilleas Mantzios | performreport | |
?group=yes&groupno=7 <br>
(1 row) <br>
<br>
amantzio@[local]/dynacom=# <br>
</span></p>
<p>Now I login to the app, via the front page, indeed the url I get
for this subapp inside the front page is :</p>
<p><a class="moz-txt-link-freetext"
href="https://localhost:8443/sma/pr/perfleet?group=yes&groupno=7">https://localhost:8443/sma/pr/perfleet?group=yes&groupno=7</a><br>
</p>
<p>Now I click into the supapp and now choose group 1 : </p>
<p>It correctly stores it in the db : </p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~
% psql </span><br>
psql (17.4) <br>
Type "help" for help. <br>
<br>
amantzio@[local]/dynacom=# SET application_name to 'SMA ';select
set_search_path('bdynacom,epaybdynacom,epay'); <br>
SET <br>
set_search_path <br>
----------------- <br>
<br>
(1 row) <br>
<br>
amantzio@[local]/dynacom=# select * from useroptions where
username = 'Achilleas Mantzios'; <br>
username | app | detail | urlext
<br>
--------------------+---------------+--------+---------------------- <br>
Achilleas Mantzios | performreport | |
?group=yes&groupno=1 <br>
(1 row) <br>
<br>
amantzio@[local]/dynacom=# <br>
<br>
</span>Now I click on the home page , but there I still see the
stale initial <a class="moz-txt-link-freetext"
href="https://localhost:8443/sma/pr/perfleet?group=yes&groupno=7">https://localhost:8443/sma/pr/perfleet?group=yes&groupno=7</a>
url !!<br>
</p>
<p>From psql I get the correct results , cannot reproduce :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~
% psql -p 9999 </span><br>
psql (17.4) <br>
Type "help" for help. <br>
<br>
amantzio@[local]/dynacom=# SET application_name to 'SMA ';select
set_search_path('bdynacom,epaybdynacom,epay');<br>
LOG: Query message from frontend. <br>
DETAIL: query: "SET application_name to 'SMA ';" <br>
LOG: DB node id: 0 backend pid: 35636 statement: SELECT
pg_catalog.version() <br>
LOG: DB node id: 0 backend pid: 35636 statement: SET
application_name to 'SMA '; <br>
SET <br>
LOG: Query message from frontend. <br>
DETAIL: query: "select
set_search_path('bdynacom,epaybdynacom,epay');" <br>
LOG: DB node id: 0 backend pid: 35636 statement: SELECT
count(*) FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace
AS n WHERE p.proname = 'set_search_path' AND n.oid =
p.pronamespace <br>
AND n.nspname ~ '.*' AND p.provolatile = 'i' <br>
LOG: DB node id: 0 backend pid: 35636 statement: SELECT
count(*) FROM pg_catalog.pg_proc AS p, pg_catalog.pg_namespace
AS n WHERE p.proname = 'set_search_path' AND n.oid =
p.pronamespace <br>
AND n.nspname ~ '.*' AND p.provolatile = 'v' <br>
LOG: DB node id: 0 backend pid: 35636 statement: select
set_search_path('bdynacom,epaybdynacom,epay'); <br>
set_search_path <br>
----------------- <br>
<br>
(1 row) <br>
<br>
amantzio@[local]/dynacom=# PREPARE foost(text) AS SELECT app,
urlext FROM useroptions WHERE username = $1 AND app IN
('status', 'performreport', 'freshwater', 'comms') AND detail =
''; <br>
LOG: Query message from frontend. <br>
DETAIL: query: "PREPARE foost(text) AS SELECT app, urlext FROM
useroptions WHERE username = $1 AND app IN ('status',
'performreport', 'freshwater', 'comms') AND detail = '';" <br>
LOG: DB node id: 0 backend pid: 35636 statement: PREPARE
foost(text) AS SELECT app, urlext FROM useroptions WHERE
username = $1 AND app IN ('status', 'performreport',
'freshwater', 'comm<br>
s') AND detail = ''; <br>
PREPARE <br>
amantzio@[local]/dynacom=# <br>
amantzio@[local]/dynacom=# EXECUTE foost('Achilleas Mantzios');
<br>
LOG: Query message from frontend. <br>
DETAIL: query: "EXECUTE foost('Achilleas Mantzios');" <br>
LOG: DB node id: 0 backend pid: 35636 statement: EXECUTE
foost('Achilleas Mantzios'); <br>
app | urlext <br>
---------------+---------------------- <br>
performreport | ?group=yes&groupno=1 <br>
(1 row) <br>
<br>
amantzio@[local]/dynacom=# SELECT app, urlext FROM useroptions
WHERE username = 'Achilleas Mantzios' AND app IN ('status',
'performreport', 'freshwater', 'comms') AND detail = ''; <br>
LOG: Query message from frontend. <br>
DETAIL: query: "SELECT app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app IN ('status',
'performreport', 'freshwater', 'comms') AND detail = '';" <br>
app | urlext <br>
---------------+---------------------- <br>
performreport | ?group=yes&groupno=1 <br>
(1 row) <br>
<br>
LOG: fetch from memory cache <br>
DETAIL: query result fetched from cache. statement: SELECT app,
urlext FROM useroptions WHERE username = 'Achilleas Mantzios'
AND app IN ('status', 'performreport', 'freshwater', 'comms'<br>
) AND detail = ''; <br>
amantzio@[local]/dynacom=# <br>
<br>
</span></p>
<p><span style="font-family:monospace">More clicks on the front page
, still the old reslut : </span><span
style="font-family:monospace">?group=yes&groupno=7</span></p>
<p><span style="font-family:monospace">I supply the full pgpool log.
Please specify if we can do anything more.<br>
</span></p>
</body>
</html>