<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Dear Tatsuo</p>
<p>here is a simple java program that exhibits it all . I attach it.
Also I attach the class for you to be able to run it more easily.</p>
<p>no inheritance, no schema magic needed. It works on the very
single table .</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">amantzio@[local]/dynacom=#
\d testpgpool
</span><br>
Table "public.testpgpool"
<br>
Column | Type | Collation | Nullable |
Default <br>
----------+------------------------+-----------+----------+---------
<br>
username | character varying(200) | | not null | <br>
app | text | | not null | <br>
detail | text | | not null | <br>
urlext | text | | not null | <br>
Indexes:
<br>
"testpgpool_pkey" PRIMARY KEY, btree (username, app, detail)
<br>
<br>
</span></p>
<p>place the class inside a dir named : test like : <br>
</p>
<p><span style="font-family:monospace"><span
style="font-weight:bold;color:#54ff54;background-color:#ffffff;">achix@achix-xps159550</span><span
style="color:#000000;background-color:#ffffff;">:</span><span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">~/eclipse-workspace/PgPoolTest/bin</span><span
style="color:#000000;background-color:#ffffff;">$ pwd
</span><br>
/home/achix/eclipse-workspace/PgPoolTest/bin
<br>
<span
style="font-weight:bold;color:#54ff54;background-color:#ffffff;">achix@achix-xps159550</span><span
style="color:#000000;background-color:#ffffff;">:</span><span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">~/eclipse-workspace/PgPoolTest/bin</span><span
style="color:#000000;background-color:#ffffff;">$ ls
</span><br>
<span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">test</span><span
style="color:#000000;background-color:#ffffff;">
</span><br>
<span
style="font-weight:bold;color:#54ff54;background-color:#ffffff;">achix@achix-xps159550</span><span
style="color:#000000;background-color:#ffffff;">:</span><span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">~/eclipse-workspace/PgPoolTest/bin</span><span
style="color:#000000;background-color:#ffffff;">$ ls test/
</span><br>
PgPoolTest.class
<br>
<span
style="font-weight:bold;color:#54ff54;background-color:#ffffff;">achix@achix-xps159550</span><span
style="color:#000000;background-color:#ffffff;">:</span><span
style="font-weight:bold;color:#5454ff;background-color:#ffffff;">~/eclipse-workspace/PgPoolTest/bin</span><span
style="color:#000000;background-color:#ffffff;">$ </span><br>
<br>
</span></p>
<p>then run it from the current dir (the one how contains dir test)
as : <br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">java -cp
".:/home/achix/Downloads/postgresql-42.7.5.jar"
test.PgPoolTest <your_pgpool_host></span>
<your_pgpool_port> <your_db_name> user password</span></p>
<p><span style="font-family:monospace"><br>
</span></p>
<p><span style="font-family:monospace">as it runs (and you can see
clearly that it does not append z1 in every run as it should),
please go to a shell and type :</span></p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">/usr/local/pgpool/bin/pcp_invalidate_query_cache</span><br>
</span></p>
<p><span style="font-family:monospace">you will now see the last
real data read (with the </span><span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">perfurlext=?groupno=yesz1</span></span><span
style="font-family:monospace">)</span></p>
<p><span style="font-family:monospace">and giving </span><span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">pcp_invalidate_query_cache
again, will cause the program to yield : </span></span><span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">perfurlext=?groupno=yesz1z1
and so forth.</span><br>
</span></p>
<p><span style="font-family:monospace"><br>
</span></p>
<div class="moz-cite-prefix">On 28/4/25 10:58, Achilleas Mantzios -
cloud wrote:<br>
</div>
<blockquote type="cite"
cite="mid:73dc4547-a4e2-4f92-8270-c00aedf6de98@cloud.gatewaynet.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<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@<a class="moz-txt-link-freetext" href="smadevnu:~">smadevnu:~</a>
% 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"
moz-do-not-send="true">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@<a class="moz-txt-link-freetext" href="smadevnu:~">smadevnu:~</a>
% 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"
moz-do-not-send="true">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@<a class="moz-txt-link-freetext" href="smadevnu:~">smadevnu:~</a>
% 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>
<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>