<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>On 3/5/25 11:55, Achilleas Mantzios wrote:</p>
<blockquote type="cite"
cite="mid:53021cb1-1b22-4e40-8161-d25cae392750@cloud.gatewaynet.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<p>Good day Tatsuo<br>
</p>
<div class="moz-cite-prefix">On 3/5/25 09:21, Tatsuo Ishii wrote:<br>
</div>
<blockquote type="cite"
cite="mid:20250503.152132.1774127953560047670.ishii@postgresql.org">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">On 3/5/25 03:41, Tatsuo Ishii wrote:
</pre>
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">Dear Achilleas,
Thank you for the report and logs. I will look into them. BTW, Can I
assume that you did below with patch?
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">Thank you, yes with the second patch.
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">So, with no patch (stock 4.6.0) it works with or without
prepareThreshold=0?</pre>
</blockquote>
<p>Yes, with the stock 4.6.0 it works for this scenario, no matter
the <span style="white-space: pre-wrap">prepareThreshold</span>
setting, I run this twice, one with <span
style="white-space: pre-wrap">prepareThreshold=</span>0 and
then <span style="white-space: pre-wrap">prepareThreshold=</span>5,
no problem with this set of queries.</p>
<p>So, without the patch, we have the first problem of the update
as shown on test PgPoolTest.java :</p>
<p>SELECT app, urlext FROM testpgpool WHERE username = ? AND app
IN ('performreport') AND detail = ''</p>
<p>which kept using the cache improperly because previous update
did not invalidate, and you solved it with 2 different patches,
however this second patch (I dont know about the first)
introduced this new bug.</p>
<p>I will try with the first patch applied and let you know ASAP.
</p>
</blockquote>
<p><br>
</p>
<p>With the first patch, it behaves differently, the first <span
role="heading" aria-level="1" class="yKMVIe">occurrence of the
problem is delayed in comparison to the second patch, but the
problem is still there.</span></p>
<p><span role="heading" aria-level="1" class="yKMVIe">With the
second patch at least to my eyes as much as I can imagine what
happens is :</span></p>
<p><span role="heading" aria-level="1" class="yKMVIe">as long as the
prepared statements remain unnamed (nameless portals), (till the
insertion of description='sys5' in the logs), so till the 5th
call, it works ok, and the cache gets invalidated after each
insertion as it should.</span></p>
<p><span role="heading" aria-level="1" class="yKMVIe">However, after
the 5th run, the prepared statements start to be named, and
given a name by jdbc.</span></p>
<p><span role="heading" aria-level="1" class="yKMVIe">As shown in
the log : </span><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">pgpool-2025-05-02.log_problem_prepared_threshold_eq_5_no_first_function_nunu</span></span></p>
<p><span role="heading" aria-level="1" class="yKMVIe">the while the
"insert into machdefs " is unnamed and the the first time it is
parsed and named (S_9) the query <br>
</span></p>
<p><span style="font-family:monospace"><span
style="font-weight:bold;color:#ff5454;background-color:#ffffff;">select
defid from machdefs </span>where pare<span
style="color:#000000;background-color:#ffffff;">nts[1]=$1
order by description,partid</span><br>
</span></p>
<p>runs on the backend.</p>
<p>However the very first time that S_9 is used to bind directly
(the parse had been done at the previous call) , the cache is not
invalidated and the subsequent :</p>
<p><span style="font-family:monospace"><span
style="font-weight:bold;color:#ff5454;background-color:#ffffff;">select
defid from machdefs </span>where pare<span
style="color:#000000;background-color:#ffffff;">nts[1]=$1
order by description,partid</span></span></p>
<p>uses the cache. You can see this in the said log : <br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">egrep -e
'S_9|select defid from machdefs where pare|insert into
machdefs' -B 1 pgpool-20</span><br>
25-05-02.log_problem_prepared_threshold_eq_5_no_first_function_nunu</span></p>
<p>I believe this call to S_9 is located here :</p>
<p> [59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2607LOG">line:2607LOG</a>: Bind message from frontend.<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2608DETAIL">line:2608DETAIL</a>: portal: "", statement: "S_9"<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2609LOG">line:2609LOG</a>: DB node id: 0 backend pid: 59852 statement: Bind:
insert into
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,parents,specialper,action,application,pms_importance,
pms_risk, actionnote , jobnote)
values($1,$2,$3,$4,$5,$6,$7,(SELECT
intarray_push_array(itoar($8),coalesce((select parents from
machdefs where defid=$9),'{}'::integer[]))),$10,$11,$12,$13,
$14,$15,$16) <br>
--<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2611DETAIL">line:2611DETAIL</a>: portal: ""<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2612LOG">line:2612LOG</a>: statement: insert into
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,parents,specialper,action,application,pms_importance,
pms_risk, actionnote , jobnote)
values($1,$2,$3,$4,$5,$6,$7,(SELECT
intarray_push_array(itoar($8),coalesce((select parents from
machdefs where defid=$9),'{}'::integer[]))),$10,$11,$12,$13,
$14,$15,$16) <br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2613LOG">line:2613LOG</a>: DB node id: 0 backend pid: 59852 statement:
Execute: insert into
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,parents,specialper,action,application,pms_importance,
pms_risk, actionnote , jobnote)
values($1,$2,$3,$4,$5,$6,$7,(SELECT
intarray_push_array(itoar($8),coalesce((select parents from
machdefs where defid=$9),'{}'::integer[]))),$10,$11,$12,$13,
$14,$15,$16) <br>
</p>
<p><br>
</p>
<p>Please if you need anything else, do not hesitate to contact me.<br>
</p>
<p><span style="font-family:monospace"><br>
</span></p>
<blockquote type="cite"
cite="mid:53021cb1-1b22-4e40-8161-d25cae392750@cloud.gatewaynet.com">
<blockquote type="cite"
cite="mid:20250503.152132.1774127953560047670.ishii@postgresql.org">
<blockquote type="cite">
<blockquote type="cite">
<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 type="cite">
<pre wrap="" class="moz-quote-pre">Dear Tatsuo I was not able to replicate the problem, lots of queries
lots of tables involved to make it identical.
I wrote a test but no success in making the problem happen.
On all the below I use
parents[1]=?
instead of
first(parents)=?
to eliminate exotic factors.
At least I observed that for some reason , setting in java :
prepareThreshold = 0, which means use only unnamed statements,
consistently gives correct results .
But there is clearly a problem :
an insertion happens in machdefs table and a subsequent select fetches
from memory cache. So I attach to you full logs of both pgsql and
pgpool for both runs, one with prepareThreshold=5 (problem) and one
with prepareThreshold=0 (no problem).
Since in pgpool log there is no way to get the values of the
parameters in Bind and Execute (whereas in pgsql this is possible) , I
was only "joining" the two log files by the exact timestamp . My
observations, regarding the problematic run are :
on pgpool
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2743LOG"
moz-do-not-send="true">line:2743LOG</a>:
Parse message from frontend.
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2744DETAIL"
moz-do-not-send="true">line:2744DETAIL</a>: statement: "", query: "select defid from machdefs
where parents[1]=$1 order by description,partid"
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2745LOG"
moz-do-not-send="true">line:2745LOG</a>:
DB node id: 0 backend pid: 59852 statement: Parse: select defid from
machdefs where parents[1]=$1 order by description,partid
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2746LOG"
moz-do-not-send="true">line:2746LOG</a>:
Bind message from frontend.
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2747DETAIL"
moz-do-not-send="true">line:2747DETAIL</a>: portal: "", statement: ""
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2748LOG"
moz-do-not-send="true">line:2748LOG</a>:
DB node id: 0 backend pid: 59852 statement: Bind: select defid from
machdefs where parents[1]=$1 order by description,partid
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2749LOG"
moz-do-not-send="true">line:2749LOG</a>:
Describe message from frontend.
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2750DETAIL"
moz-do-not-send="true">line:2750DETAIL</a>: portal: ""
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2751LOG"
moz-do-not-send="true">line:2751LOG</a>:
DB node id: 0 backend pid: 59852 statement: D message
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2752LOG"
moz-do-not-send="true">line:2752LOG</a>:
Execute message from frontend.
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2753DETAIL"
moz-do-not-send="true">line:2753DETAIL</a>: portal: ""
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2754LOG"
moz-do-not-send="true">line:2754LOG</a>:
statement: select defid from machdefs where parents[1]=$1 order by
description,partid
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2755LOG"
moz-do-not-send="true">line:2755LOG</a>:
fetch from memory cache
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2756DETAIL"
moz-do-not-send="true">line:2756DETAIL</a>: query result fetched from cache. statement: select
defid from machdefs where parents[1]=$1 order by description,partid
0001000100010
00000040CCD179D0000
on pgsql
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02 14:10:47.582 EEST
SMA amantzio@dynacom <a class="moz-txt-link-freetext" href="line:1340"
moz-do-not-send="true">line:1340</a> LOG: duration: 0.042 ms parse
<unnamed>: select defid from machdefs where parents[1]=$1 order by
description,partid
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02 14:10:47.582 EEST
SMA amantzio@dynacom <a class="moz-txt-link-freetext" href="line:1341"
moz-do-not-send="true">line:1341</a> LOG: duration: 0.070 ms bind
<unnamed>: select defid from machdefs where parents[1]=$1 order by
description,partid
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02 14:10:47.582 EEST
SMA amantzio@dynacom <a class="moz-txt-link-freetext" href="line:1342"
moz-do-not-send="true">line:1342</a> DETAIL: Parameters: $1 = '214767517'
(no execute)
---
the problem is manifested here :
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2755LOG"
moz-do-not-send="true">line:2755LOG</a>:
fetch from memory cache
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
<a class="moz-txt-link-freetext" href="line:2756DETAIL"
moz-do-not-send="true">line:2756DETAIL</a>: query result fetched from cache. statement: select
defid from machdefs where parents[1]=$1 order by description,partid
0001000100010
00000040CCD179D0000
because earlier he have :
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom <a
class="moz-txt-link-freetext" href="line:2613LOG"
moz-do-not-send="true">line:2613LOG</a>:
DB node id: 0 backend pid: 59852 statement: Execute: insert into
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,p
arents,specialper,action,application,pms_importance, pms_risk,
actionnote , jobnote) values($1,$2,$3,$4,$5,$6,$7,(SELECT
intarray_push_array(itoar($8),coalesce((select parents from machdefs
where defid=$9),'{
}'::integer[]))),$10,$11,$12,$13, $14,$15,$16)
this insertion should have invalidated the cache for this query :
select defid from machdefs where parents[1]=$1 order by
description,partid
========================================
please have a look, I am so sorry I could not reprooduce by a simple
program.
(yes I looked on every detail, no luck).
On 5/1/25 06:02, Achilleas Mantzios wrote:
</pre>
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">On 1/5/25 03:02, Tatsuo Ishii wrote:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">update : the bug (this new bug) is not present prior to the first
patch. So the bug (the new one) is not present in plain vanilla :
pgpool-II-4.6.0 . Reverting both patches solves the issue with the new
bug which has most probably to do with an array function called
"first" :
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">"first" is coming with the extension? I am not familiar with it.
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">This (and a couple of others I wrote back then) is a C function
written by me, when still intarray was not an extension, I mean before
the extensions framework, sometime in 2004 or so. This is a simple C
function which returns the first element of an array.
dynacom=# \df+ first
List
of functions
Schema | Name | Result data type | Argument data types | Type |
Volatility | Parallel | Owner | Security | Access privileges
| Language | Source code |
Description
--------+-------+------------------+---------------------+------+------------+----------+----------+----------+--------------------------+----------+-------------+
-------------
public | first | integer | integer[] | func |
immutable | unsafe | postgres | invoker | postgres=X/postgres
+| c | first |
| | | | |
| | | | powerprom=X/postgres
+| | |
| | | | |
| | | | default_group=X/postgres
| | |
(1 row)
dynacom=#
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">select defid, description from machdefs where first(parents)=214766150
order by description;
I haven't been able to reproduce with SQL (from psql).
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">Is it possible for you to create a java reproducer?
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">I will try.
</pre>
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">Also reverting both patches, i.e. going back to plain vanilla
pgpool-II-4.6.0 solves this new bug, but re-introduces the previous (
with seeing stale and data and not invalidate data on table
testpgpool)
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
<a class="moz-txt-link-freetext"
href="English:http://www.sraoss.co.jp/index_en/" moz-do-not-send="true">English: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>
<pre wrap="" class="moz-quote-pre">_______________________________________________
pgpool-general mailing list
<a class="moz-txt-link-abbreviated moz-txt-link-freetext"
href="mailto:pgpool-general@pgpool.net"
moz-do-not-send="true">pgpool-general@pgpool.net</a>
<a class="moz-txt-link-freetext"
href="http://www.pgpool.net/mailman/listinfo/pgpool-general"
moz-do-not-send="true">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">_______________________________________________
pgpool-general mailing list
<a class="moz-txt-link-abbreviated moz-txt-link-freetext"
href="mailto:pgpool-general@pgpool.net"
moz-do-not-send="true">pgpool-general@pgpool.net</a>
<a class="moz-txt-link-freetext"
href="http://www.pgpool.net/mailman/listinfo/pgpool-general"
moz-do-not-send="true">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">_______________________________________________
pgpool-general mailing list
<a class="moz-txt-link-abbreviated moz-txt-link-freetext"
href="mailto:pgpool-general@pgpool.net"
moz-do-not-send="true">pgpool-general@pgpool.net</a>
<a class="moz-txt-link-freetext"
href="http://www.pgpool.net/mailman/listinfo/pgpool-general"
moz-do-not-send="true">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="" class="moz-quote-pre">_______________________________________________
pgpool-general mailing list
<a class="moz-txt-link-abbreviated moz-txt-link-freetext"
href="mailto:pgpool-general@pgpool.net"
moz-do-not-send="true">pgpool-general@pgpool.net</a>
<a class="moz-txt-link-freetext"
href="http://www.pgpool.net/mailman/listinfo/pgpool-general"
moz-do-not-send="true">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
</pre>
</blockquote>
</blockquote>
</blockquote>
<pre wrap="" class="moz-quote-pre">_______________________________________________
pgpool-general mailing list
<a class="moz-txt-link-abbreviated moz-txt-link-freetext"
href="mailto:pgpool-general@pgpool.net" moz-do-not-send="true">pgpool-general@pgpool.net</a>
<a class="moz-txt-link-freetext"
href="http://www.pgpool.net/mailman/listinfo/pgpool-general"
moz-do-not-send="true">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
</pre>
</blockquote>
</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>