<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<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 type="cite"
cite="mid:20250503.152132.1774127953560047670.ishii@postgresql.org">
<pre wrap="" class="moz-quote-pre">
</pre>
<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/">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 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">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">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">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">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">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">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">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">line:2750DETAIL</a>: portal: ""
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a class="moz-txt-link-freetext" href="line:2751LOG">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">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">line:2753DETAIL</a>: portal: ""
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom <a class="moz-txt-link-freetext" href="line:2754LOG">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">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">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">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">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">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">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">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">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/">English: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>
<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>
<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>
<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>
</blockquote>
</blockquote>
<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>
</blockquote>
</blockquote>
<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>
</blockquote>
</body>
</html>