<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Dear Tatsuo I was not able to replicate the problem, lots of
queries lots of tables involved to make it identical.</p>
<p>I wrote a test but no success in making the problem happen.</p>
<p>On all the below I use </p>
<p>parents[1]=? </p>
<p>instead of </p>
<p>first(parents)=?</p>
<p>to eliminate exotic factors.<br>
</p>
<p>At least I observed that for some reason , setting in java :
prepareThreshold = 0, which means use only unnamed statements,
consistently gives correct results .<br>
</p>
<p>But there is clearly a problem :</p>
<p>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).</p>
<p><br>
</p>
<p>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 : <br>
</p>
<p>on pgpool<br>
<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2743LOG: Parse message from frontend.<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2744DETAIL: statement: "", query: "select defid from
machdefs where parents[1]=$1 order by description,partid"<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2745LOG: DB node id: 0 backend pid: 59852 statement: Parse:
select defid from machdefs where parents[1]=$1 order by
description,partid<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2746LOG: Bind message from frontend.<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2747DETAIL: portal: "", statement: ""<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2748LOG: DB node id: 0 backend pid: 59852 statement: Bind:
select defid from machdefs where parents[1]=$1 order by
description,partid<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2749LOG: Describe message from frontend.<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2750DETAIL: portal: ""<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2751LOG: DB node id: 0 backend pid: 59852 statement: D
message<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2752LOG: Execute message from frontend.<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2753DETAIL: portal: ""<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2754LOG: statement: select defid from machdefs where
parents[1]=$1 order by description,partid<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2755LOG: fetch from memory cache<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2756DETAIL: query result fetched from cache. statement:
select defid from machdefs where parents[1]=$1 order by
description,partid 0001000100010<br>
00000040CCD179D0000<br>
<br>
on pgsql<br>
<br>
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02 14:10:47.582
EEST SMA amantzio@dynacom line:1340 LOG: duration: 0.042 ms
parse <unnamed>: select defid from machdefs where
parents[1]=$1 order by description,partid<br>
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02 14:10:47.582
EEST SMA amantzio@dynacom line:1341 LOG: duration: 0.070 ms
bind <unnamed>: select defid from machdefs where
parents[1]=$1 order by description,partid<br>
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02 14:10:47.582
EEST SMA amantzio@dynacom line:1342 DETAIL: Parameters: $1 =
'214767517'<br>
<br>
(no execute)<br>
<br>
---<br>
<br>
the problem is manifested here :<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2755LOG: fetch from memory cache<br>
[59728] 2025-05-02 14:10:47.582 SMA amantzio@dynacom
line:2756DETAIL: query result fetched from cache. statement:
select defid from machdefs where parents[1]=$1 order by
description,partid 0001000100010<br>
00000040CCD179D0000<br>
<br>
because earlier he have :<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
line:2613LOG: DB node id: 0 backend pid: 59852 statement:
Execute: insert into
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,p<br>
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),'{<br>
}'::integer[]))),$10,$11,$12,$13, $14,$15,$16)<br>
<br>
this insertion should have invalidated the cache for this query :<br>
select defid from machdefs where parents[1]=$1 order by
description,partid<br>
<br>
========================================</p>
<p><br>
</p>
<p>please have a look, I am so sorry I could not reprooduce by a
simple program.</p>
<p>(yes I looked on every detail, no luck).<br>
</p>
<p><br>
</p>
<div class="moz-cite-prefix">On 5/1/25 06:02, Achilleas Mantzios
wrote:<br>
</div>
<blockquote type="cite"
cite="mid:4f40bea7-50df-4398-8d41-8acc0927a067@cloud.gatewaynet.com">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<p><br>
</p>
<div class="moz-cite-prefix">On 1/5/25 03:02, Tatsuo Ishii wrote:<br>
</div>
<blockquote type="cite"
cite="mid:20250501.090210.2170879573052867721.ishii@postgresql.org">
<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>
<p>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.</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">dynacom=#
\df+ first </span><br>
List
of functions <br>
Schema | Name | Result data type | Argument data types | Type
| Volatility | Parallel | Owner | Security | Access
privileges | Language | Source code |<br>
Description <br>
--------+-------+------------------+---------------------+------+------------+----------+----------+----------+--------------------------+----------+-------------+<br>
------------- <br>
public | first | integer | integer[] | func
| immutable | unsafe | postgres | invoker |
postgres=X/postgres +| c | first |<br>
<br>
| | | |
| | | | |
powerprom=X/postgres +| | |<br>
<br>
| | | |
| | | | |
default_group=X/postgres | | |<br>
<br>
(1 row) <br>
<br>
dynacom=# <br>
<br>
<br>
</span></p>
<blockquote type="cite"
cite="mid:20250501.090210.2170879573052867721.ishii@postgresql.org">
<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>
I will try.<br>
<blockquote type="cite"
cite="mid:20250501.090210.2170879573052867721.ishii@postgresql.org">
<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.
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>
<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>
<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>