<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>On 5/4/25 05:55, Achilleas Mantzios wrote:</p>
<blockquote type="cite"
cite="mid:f0abee1e-df82-4530-b241-d6a28e399a35@cloud.gatewaynet.com">On
4/5/25 06:42, Tatsuo Ishii wrote:
<br>
<br>
<blockquote type="cite">Achilleas,
<br>
<br>
Please disregard the patch. I think I have an oeversight with
the
<br>
patch.
<br>
</blockquote>
<br>
Good Day Tatsuo. Thank you for all your work!
<br>
<br>
I will wait !
<br>
</blockquote>
<p>Dear Tatsuo , <br>
</p>
<p>I happy to say to you that I finally came up with a valid test
that reproduces the 2nd bug (with the INSERT and the BIND), so I
have this java program, which will fail exactly after
"preparedThreshold" iterations! <br>
</p>
<p>This is supposed to be run against the <span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">4.6.0</span> </span>version
with the <span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">query_cache-v2.patch</span>
</span>applied.<br>
</p>
<p>the table looks like : <br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">CREATE TABLE
testarr(id bigserial primary key, parents int[], descr text);</span><br>
</span><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">postgres@[local]/dynacom=#
\d testarr
</span><br>
Table "public.testarr"
<br>
Column | Type | Collation | Nullable |
Default <br>
---------+-----------+-----------+----------+-------------------------------------
<br>
id | bigint | | not null |
nextval('testarr_id_seq'::regclass)
<br>
parents | integer[] | | | <br>
descr | text | | | <br>
Indexes:
<br>
"testarr_pkey" PRIMARY KEY, btree (id)
<br>
<br>
postgres@[local]/dynacom=# <br>
<br>
</span></p>
<p>As with with the first program pls put the class inside a dir
called "test", then from the parent :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~/workspace/pgpoolbugII/bin
% ls
</span><br>
<span style="color:#1818b2;background-color:#ffffff;">test</span><span
style="color:#000000;background-color:#ffffff;">
</span><br>
achill@smadevnu:~/workspace/pgpoolbugII/bin % ls test/
<br>
PgPoolTestII.class
<br>
achill@smadevnu:~/workspace/pgpoolbugII/bin % <br>
<br>
run</span></p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">java -cp
":/home/achill/SQL/postgresql-42.7.5.jar" test.PgPoolTestII
localhost 9999 dynacom username passwd</span><br>
<br>
</span></p>
<blockquote type="cite"
cite="mid:f0abee1e-df82-4530-b241-d6a28e399a35@cloud.gatewaynet.com">
<br>
<br>
<blockquote type="cite">From: Tatsuo Ishii
<a class="moz-txt-link-rfc2396E" href="mailto:ishii@postgresql.org"><ishii@postgresql.org></a>
<br>
Subject: [pgpool-general: 9462] Re: Clarification on query
results cache visibility
<br>
Date: Sun, 04 May 2025 11:05:38 +0900 (JST)
<br>
Message-ID:
<a class="moz-txt-link-rfc2396E" href="mailto:20250504.110538.1845818308821205486.ishii@postgresql.org"><20250504.110538.1845818308821205486.ishii@postgresql.org></a>
<br>
<br>
<blockquote type="cite">
<blockquote type="cite">On 3/5/25 11:55, Achilleas Mantzios
wrote:
<br>
<br>
<blockquote type="cite">Good day Tatsuo
<br>
<br>
On 3/5/25 09:21, Tatsuo Ishii wrote:
<br>
<blockquote type="cite">
<blockquote type="cite">On 3/5/25 03:41, Tatsuo Ishii
wrote:
<br>
<blockquote type="cite">Dear Achilleas,
<br>
<br>
Thank you for the report and logs. I will look into
them. BTW, Can I
<br>
assume that you did below with patch?
<br>
</blockquote>
Thank you, yes with the second patch.
<br>
</blockquote>
So, with no patch (stock 4.6.0) it works with or without
<br>
prepareThreshold=0?
<br>
</blockquote>
Yes, with the stock 4.6.0 it works for this scenario, no
matter the
<br>
prepareThreshold setting, I run this twice, one with
<br>
prepareThreshold=0 and then prepareThreshold=5, no problem
with this
<br>
set of queries.
<br>
<br>
So, without the patch, we have the first problem of the
update as
<br>
shown on test PgPoolTest.java :
<br>
<br>
SELECT app, urlext FROM testpgpool WHERE username = ? AND
app IN
<br>
('performreport') AND detail = ''
<br>
<br>
which kept using the cache improperly because previous
update did not
<br>
invalidate, and you solved it with 2 different patches,
however this
<br>
second patch (I dont know about the first) introduced this
new bug.
<br>
<br>
I will try with the first patch applied and let you know
ASAP.
<br>
<br>
</blockquote>
With the first patch, it behaves differently, the first
occurrence of
<br>
the problem is delayed in comparison to the second patch,
but the
<br>
problem is still there.
<br>
<br>
With the second patch at least to my eyes as much as I can
imagine
<br>
what happens is :
<br>
<br>
as long as the prepared statements remain unnamed (nameless
portals),
<br>
(till the insertion of description='sys5' in the logs), so
till the
<br>
5th call, it works ok, and the cache gets invalidated after
each
<br>
insertion as it should.
<br>
<br>
However, after the 5th run, the prepared statements start to
be named,
<br>
and given a name by jdbc.
<br>
<br>
As shown in the log :
<br>
pgpool-2025-05-02.log_problem_prepared_threshold_eq_5_no_first_function_nunu
<br>
<br>
the while the "insert into machdefs " is unnamed and the the
first
<br>
time it is parsed and named (S_9) the query
<br>
<br>
select defid from machdefs where parents[1]=$1 order by
<br>
description,partid
<br>
<br>
runs on the backend.
<br>
<br>
However the very first time that S_9 is used to bind
directly (the
<br>
parse had been done at the previous call) , the cache is not
<br>
invalidated and the subsequent :
<br>
</blockquote>
Bind without parse could cause difference: when a parse
message
<br>
arrives, pgpool extracts table oids used in the SQL. For
example, for
<br>
"INSERT INTO machdefs..." pgpool searches the oid of machdefs
and
<br>
keeps it in memory so that later on (at CommandComplete)
pgpool
<br>
invalidates query cache belongs to the oid (pgpool stores oids
under
<br>
logdir/pgpool to remember the relationship between cache key
and
<br>
oid). Unfortunately there's only one memory area to store the
oids. If
<br>
other parse message arrives, the area might be overwritten and
fails to
<br>
invalidate query cache.
<br>
<br>
On the other hand, prepareThreshold=0 case always parse is
used before
<br>
bind and the problem is not there, I guess.
<br>
<br>
Attached patch tries to solve the issue by by adding new
member to
<br>
"query context" structure (holding various data including
parse tree
<br>
etc.) to save the table oids, and restore at CommandComplete
time.
<br>
<br>
The patch is on top the second patch (query_cache-v2.patch).
So please
<br>
apply it after applying query_cache-v2.patch.
<br>
<br>
Note that I don't have an explanation (yet) why 4.6.0 does not
have
<br>
the probolem you have. I have a simple test case which
demonstrates
<br>
the issue even 4.6.0.
<br>
<br>
Best regards,
<br>
--
<br>
Tatsuo Ishii
<br>
SRA OSS K.K.
<br>
English: <a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp/index_en/">http://www.sraoss.co.jp/index_en/</a>
<br>
Japanese:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
<br>
<br>
<blockquote type="cite">select defid from machdefs where
parents[1]=$1 order by
<br>
description,partid
<br>
<br>
uses the cache. You can see this in the said log :
<br>
<br>
egrep -e 'S_9|select defid from machdefs where pare|insert
into
<br>
machdefs' -B 1 pgpool-20
<br>
25-05-02.log_problem_prepared_threshold_eq_5_no_first_function_nunu
<br>
<br>
I believe this call to S_9 is located here :
<br>
<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
line:2607LOG:
<br>
Bind message from frontend.
<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<br>
line:2608DETAIL: portal: "", statement: "S_9"
<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
line:2609LOG:
<br>
DB node id: 0 backend pid: 59852 statement: Bind: insert
into
<br>
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,parents,specialper,action,application,pms_importance,
<br>
pms_risk, actionnote , jobnote)
values($1,$2,$3,$4,$5,$6,$7,(SELECT
<br>
intarray_push_array(itoar($8),coalesce((select parents from
machdefs
<br>
where defid=$9),'{}'::integer[]))),$10,$11,$12,$13,
$14,$15,$16)
<br>
--
<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
<br>
line:2611DETAIL: portal: ""
<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
line:2612LOG:
<br>
statement: insert into
<br>
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,parents,specialper,action,application,pms_importance,
<br>
pms_risk, actionnote , jobnote)
values($1,$2,$3,$4,$5,$6,$7,(SELECT
<br>
intarray_push_array(itoar($8),coalesce((select parents from
machdefs
<br>
where defid=$9),'{}'::integer[]))),$10,$11,$12,$13,
$14,$15,$16)
<br>
[59728] 2025-05-02 14:10:45.038 SMA amantzio@dynacom
line:2613LOG:
<br>
DB node id: 0 backend pid: 59852 statement: Execute: insert
into
<br>
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,parents,specialper,action,application,pms_importance,
<br>
pms_risk, actionnote , jobnote)
values($1,$2,$3,$4,$5,$6,$7,(SELECT
<br>
intarray_push_array(itoar($8),coalesce((select parents from
machdefs
<br>
where defid=$9),'{}'::integer[]))),$10,$11,$12,$13,
$14,$15,$16)
<br>
<br>
<br>
Please if you need anything else, do not hesitate to contact
me.
<br>
<br>
<br>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">Best regards,
<br>
--
<br>
Tatsuo Ishii
<br>
SRA OSS K.K.
<br>
English:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp/index_en/">http://www.sraoss.co.jp/index_en/</a>
<br>
Japanese:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
<br>
<br>
<blockquote type="cite">Dear Tatsuo I was not able
to replicate the problem, lots of queries
<br>
lots of tables involved to make it identical.
<br>
<br>
I wrote a test but no success in making the
problem happen.
<br>
<br>
On all the below I use
<br>
<br>
parents[1]=?
<br>
<br>
instead of
<br>
<br>
first(parents)=?
<br>
<br>
to eliminate exotic factors.
<br>
<br>
At least I observed that for some reason , setting
in java :
<br>
prepareThreshold = 0, which means use only unnamed
statements,
<br>
consistently gives correct results .
<br>
<br>
But there is clearly a problem :
<br>
<br>
an insertion happens in machdefs table and a
subsequent select fetches
<br>
from memory cache. So I attach to you full logs of
both pgsql and
<br>
pgpool for both runs, one with prepareThreshold=5
(problem) and one
<br>
with prepareThreshold=0 (no problem).
<br>
<br>
<br>
Since in pgpool log there is no way to get the
values of the
<br>
parameters in Bind and Execute (whereas in pgsql
this is possible) , I
<br>
was only "joining" the two log files by the exact
timestamp . My
<br>
observations, regarding the problematic run are :
<br>
<br>
on pgpool
<br>
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2743LOG:
<br>
Parse message from frontend.
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacom
<br>
line:2744DETAIL: statement: "", query: "select
defid from machdefs
<br>
where parents[1]=$1 order by description,partid"
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2745LOG:
<br>
DB node id: 0 backend pid: 59852 statement: Parse:
select defid from
<br>
machdefs where parents[1]=$1 order by
description,partid
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2746LOG:
<br>
Bind message from frontend.
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacom
<br>
line:2747DETAIL: portal: "", statement: ""
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2748LOG:
<br>
DB node id: 0 backend pid: 59852 statement: Bind:
select defid from
<br>
machdefs where parents[1]=$1 order by
description,partid
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2749LOG:
<br>
Describe message from frontend.
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacom
<br>
line:2750DETAIL: portal: ""
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2751LOG:
<br>
DB node id: 0 backend pid: 59852 statement: D
message
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2752LOG:
<br>
Execute message from frontend.
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacom
<br>
line:2753DETAIL: portal: ""
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2754LOG:
<br>
statement: select defid from machdefs where
parents[1]=$1 order by
<br>
description,partid
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacomline:2755LOG:
<br>
fetch from memory cache
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacom
<br>
line:2756DETAIL: query result fetched from cache.
statement: select
<br>
defid from machdefs where parents[1]=$1 order by
description,partid
<br>
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
<br>
SMA amantzio@dynacomline:1340 LOG: duration:
0.042 ms parse
<br>
<unnamed>: select defid from machdefs where
parents[1]=$1 order by
<br>
description,partid
<br>
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02
14:10:47.582 EEST
<br>
SMA amantzio@dynacomline:1341 LOG: duration:
0.070 ms bind
<br>
<unnamed>: select defid from machdefs where
parents[1]=$1 order by
<br>
description,partid
<br>
127.0.0.1(15112) [59852] 6814a7ed.e9cc 2025-05-02
14:10:47.582 EEST
<br>
SMA amantzio@dynacomline: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@dynacomline:2755LOG:
<br>
fetch from memory cache
<br>
[59728] 2025-05-02 14:10:47.582 SMA
amantzio@dynacom
<br>
line:2756DETAIL: query result fetched from cache.
statement: select
<br>
defid from machdefs where parents[1]=$1 order by
description,partid
<br>
0001000100010
<br>
00000040CCD179D0000
<br>
<br>
because earlier he have :
<br>
[59728] 2025-05-02 14:10:45.038 SMA
amantzio@dynacomline:2613LOG:
<br>
DB node id: 0 backend pid: 59852 statement:
Execute: insert into
<br>
machdefs(description,partid,machtypeid,rhbec,rhdue,periodbec,perioddue,p
<br>
arents,specialper,action,application,pms_importance, pms_risk,
<br>
actionnote , jobnote)
values($1,$2,$3,$4,$5,$6,$7,(SELECT
<br>
intarray_push_array(itoar($8),coalesce((select
parents from machdefs
<br>
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
<br>
description,partid
<br>
<br>
========================================
<br>
<br>
<br>
please have a look, I am so sorry I could not
reprooduce by a simple
<br>
program.
<br>
<br>
(yes I looked on every detail, no luck).
<br>
<br>
<br>
On 5/1/25 06:02, Achilleas Mantzios wrote:
<br>
<blockquote type="cite">On 1/5/25 03:02, Tatsuo
Ishii wrote:
<br>
<blockquote type="cite">
<blockquote type="cite">update : the bug (this
new bug) is not present prior to the first
<br>
patch. So the bug (the new one) is not
present in plain vanilla :
<br>
pgpool-II-4.6.0 . Reverting both patches
solves the issue with the new
<br>
bug which has most probably to do with an
array function called
<br>
"first" :
<br>
</blockquote>
"first" is coming with the extension? I am not
familiar with it.
<br>
</blockquote>
This (and a couple of others I wrote back then)
is a C function
<br>
written by me, when still intarray was not an
extension, I mean before
<br>
the extensions framework, sometime in 2004 or
so. This is a simple C
<br>
function which returns the first element of an
array.
<br>
<br>
dynacom=# \df+ first
<br>
List<br>
of functions
<br>
Schema | Name | Result data type | Argument
data types | Type |
<br>
Volatility | Parallel | Owner | Security |
Access privileges
<br>
| Language | Source code |
<br>
Description
<br>
--------+-------+------------------+---------------------+------+------------+----------+----------+----------+--------------------------+----------+-------------+
<br>
-------------
<br>
public | first | integer | integer[]
| func |
<br>
immutable | unsafe | postgres | invoker |
postgres=X/postgres
<br>
+| c | first |
<br>
<br>
| | |
| |
<br>
| | | |
powerprom=X/postgres
<br>
+| | |
<br>
<br>
| | |
| |
<br>
| | | |
default_group=X/postgres
<br>
| | |
<br>
<br>
(1 row)
<br>
<br>
dynacom=#
<br>
<br>
<br>
<blockquote type="cite">
<blockquote type="cite">select defid,
description from machdefs where
first(parents)=214766150
<br>
order by description;
<br>
<br>
I haven't been able to reproduce with SQL
(from psql).
<br>
</blockquote>
Is it possible for you to create a java
reproducer?
<br>
</blockquote>
I will try.
<br>
<blockquote type="cite">
<blockquote type="cite">Also reverting both
patches, i.e. going back to plain vanilla
<br>
pgpool-II-4.6.0 solves this new bug, but
re-introduces the previous (
<br>
with seeing stale and data and not
invalidate data on table
<br>
testpgpool)
<br>
<br>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">Best regards,
<br>
-- <br>
Tatsuo Ishii
<br>
SRA OSS K.K.
<br>
English:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp/index_en/">http://www.sraoss.co.jp/index_en/</a>
<br>
Japanese:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
<br>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
</blockquote>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
</blockquote>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
_______________________________________________
<br>
pgpool-general mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:pgpool-general@pgpool.net">pgpool-general@pgpool.net</a>
<br>
<a class="moz-txt-link-freetext" href="http://www.pgpool.net/mailman/listinfo/pgpool-general">http://www.pgpool.net/mailman/listinfo/pgpool-general</a>
<br>
</blockquote>
</body>
</html>