<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>On 5/6/25 11:01, Tatsuo Ishii wrote:</p>
<blockquote type="cite"
cite="mid:20250506.190102.1208502039142336713.ishii@postgresql.org">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">Good Day Tatsuo
On 6/5/25 02:17, Tatsuo Ishii wrote:
</pre>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">On 5/4/25 05:55, Achilleas Mantzios wrote:
</pre>
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">On 4/5/25 06:42, Tatsuo Ishii wrote:
</pre>
<blockquote type="cite">
<pre wrap="" class="moz-quote-pre">Achilleas,
Please disregard the patch. I think I have an oeversight with the
patch.
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">Good Day Tatsuo. Thank you for all your work!
I will wait !
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">Dear Tatsuo ,
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!
This is supposed to be run against the 4.6.0 version with the
query_cache-v2.patch applied.
the table looks like :
CREATE TABLE testarr(id bigserial primary key, parents int[], descr
text);
postgres@[local]/dynacom=# \d testarr
Table "public.testarr"
Column | Type | Collation | Nullable | Default
---------+-----------+-----------+----------+-------------------------------------
id | bigint | | not null |
nextval('testarr_id_seq'::regclass)
parents | integer[] | | |
descr | text | | |
Indexes:
"testarr_pkey" PRIMARY KEY, btree (id)
postgres@[local]/dynacom=#
As with with the first program pls put the class inside a dir called
"test", then from the parent :
achill@smadevnu:~/workspace/pgpoolbugII/bin % ls
test
achill@smadevnu:~/workspace/pgpoolbugII/bin % ls test/
PgPoolTestII.class
achill@smadevnu:~/workspace/pgpoolbugII/bin %
run
java -cp ":/home/achill/SQL/postgresql-42.7.5.jar" test.PgPoolTestII
localhost 9999 dynacom username passwd
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">Great! I will give it a try.
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">Unfortunately the java program needs functions I don't have.
PreparedStatement stidxINS = con.prepareStatement("insert into
testarr(parents,descr) VALUES((SELECT
intarray_push_array(itoar(?),coalesce((select parents from testarr
where id=?),'{}'::integer[]))),?)");
It seems it requires itoar() and intarray_push_array(), which I don't
have.
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">itoar is our own, sorry, intarray_push_array comes from the intarray
contrib, I just wanted to make it look like the original as much as I
could, here is a version that doesn't use any of those. So please try
that.
</pre>
</blockquote>
<pre wrap="" class="moz-quote-pre">
Thanks. Attached is the patch to fix the issue.
It can be applied on top of the v2 patch.
Basically the cause of the issue was what I already explained in the
upthread. Your test program issues INSERT inside an explicit
transaction when the issue shows up.
bind(begin);
execute(begin);
bind (insert);
execute (insert);
bind(commit);
execute(commit);
Bind without parse message fails to pick up table oids used in
insert. At commit, pgpool looks for table oids so that it invalidates
query cache created by SELECT (on another session) which uses the same
tables as insert. But since the table oid list is empty, pgpool cannot
invalidate the query cache and SELECT fetches the stale query cache.
I still don't know why 4.6.0 does not show the issue. Maybe I study it
when I have time.</pre>
</blockquote>
<p>Thank you! I tested 4.6.0 with the two patches applied : <span
style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">query_cache-v2.patch
, fix_bind.patch </span></span>and both tests pass! <br>
</p>
<p>As far as stock 4.6.0 (without patches) is concerned, now that I
run the tests , they both failed, and I don't remember under which
conditions I had communicated with you otherwise :</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">achill@smadevnu:~
% java -cp
'workspace/pgpoolbug/bin/:SQL/postgresql-42.7.5.jar'
test.PgPoolTest localhost 9999 dynacom amantzio foo
</span><br>
1 rows initialized
<br>
rsidx0 returned : perfurlext=?groupno=yes
<br>
1 rows updated
<br>
rsidx0 returned : perfurlext=?groupno=yes
<br>
1 rows updated
<br>
rsidx0 returned : perfurlext=?groupno=yes
<br>
1 rows updated
<br>
rsidx0 returned : perfurlext=?groupno=yes
<br>
1 rows updated
<br>
rsidx0 returned : perfurlext=?groupno=yes
<br>
1 rows updated
<br>
^Cachill@smadevnu:~ % <br>
achill@smadevnu:~ % <br>
achill@smadevnu:~ % java -cp
'workspace/pgpoolbugII/bin/:SQL/postgresql-42.7.5.jar'
test.PgPoolTestII localhost 9999 dynacom amantzio foo
<br>
1 inserted. OK Initialized.
<br>
inserted head id =4848
<br>
1 kid rows inserted
<br>
listing kids for run : 1 .
<br>
Kid id=4849 descr=some descr 0
<br>
Should have 1 kids. Has 1 kids. OK
<br>
1 kid rows inserted
<br>
listing kids for run : 2 .
<br>
Kid id=4849 descr=some descr 0
<br>
Should have 2 kids. Has 1 kids. PROBLEM
<br>
1 kid rows inserted
<br>
listing kids for run : 3 .
<br>
Kid id=4849 descr=some descr 0
<br>
Should have 3 kids. Has 1 kids. PROBLEM
<br>
1 kid rows inserted
<br>
listing kids for run : 4 .
<br>
Kid id=4849 descr=some descr 0
<br>
Should have 4 kids. Has 1 kids. PROBLEM<br>
<br>
</span>No clue why (with the stock 4.6.0) the bind test fails
right from the start and not from 11th iteration.</p>
<p>Anyway, after the patches , it seems it works, I will keep
testing with real life usage!<span style="font-family:monospace"><br>
</span></p>
<p><span style="font-family:monospace"><br>
</span></p>
<blockquote type="cite"
cite="mid:20250506.190102.1208502039142336713.ishii@postgresql.org">
<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>
Japanese:<a class="moz-txt-link-freetext" href="http://www.sraoss.co.jp">http://www.sraoss.co.jp</a>
</pre>
</blockquote>
</body>
</html>