[pgpool-general: 9470] Re: Clarification on query results cache visibility

[Achilleas Mantzios]

On 5/6/25 11:01, Tatsuo Ishii wrote:

>> Good Day Tatsuo
>>
>> On 6/5/25 02:17, Tatsuo Ishii wrote:
>>>>> On 5/4/25 05:55, Achilleas Mantzios wrote:
>>>>>
>>>>>> On 4/5/25 06:42, Tatsuo Ishii wrote:
>>>>>>
>>>>>>> Achilleas,
>>>>>>>
>>>>>>> Please disregard the patch. I think I have an oeversight with the
>>>>>>> patch.
>>>>>> Good Day Tatsuo. Thank you for all your work!
>>>>>>
>>>>>> I will wait !
>>>>> 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 at smadevnu:~/workspace/pgpoolbugII/bin % ls
>>>>> test
>>>>> achill at smadevnu:~/workspace/pgpoolbugII/bin % ls test/
>>>>> PgPoolTestII.class
>>>>> achill at smadevnu:~/workspace/pgpoolbugII/bin %
>>>>>
>>>>> run
>>>>>
>>>>> java -cp ":/home/achill/SQL/postgresql-42.7.5.jar" test.PgPoolTestII
>>>>> localhost 9999 dynacom username passwd
>>>> Great! I will give it a try.
>>> 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.
>> 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.
> 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.

Thank you! I tested 4.6.0 with the two patches applied : 
query_cache-v2.patch , fix_bind.patch and both tests pass!

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 :

achill at smadevnu:~ % java -cp 
'workspace/pgpoolbug/bin/:SQL/postgresql-42.7.5.jar' test.PgPoolTest 
localhost 9999 dynacom amantzio foo
1 rows initialized
rsidx0 returned : perfurlext=?groupno=yes
1 rows updated
rsidx0 returned : perfurlext=?groupno=yes
1 rows updated
rsidx0 returned : perfurlext=?groupno=yes
1 rows updated
rsidx0 returned : perfurlext=?groupno=yes
1 rows updated
rsidx0 returned : perfurlext=?groupno=yes
1 rows updated
^Cachill at smadevnu:~ %
achill at smadevnu:~ %
achill at smadevnu:~ % java -cp 
'workspace/pgpoolbugII/bin/:SQL/postgresql-42.7.5.jar' test.PgPoolTestII 
localhost 9999 dynacom amantzio foo
1 inserted. OK Initialized.
inserted head id =4848
1 kid rows inserted
listing kids for run : 1 .
Kid id=4849 descr=some descr 0
Should have 1 kids. Has 1 kids. OK
1 kid rows inserted
listing kids for run : 2 .
Kid id=4849 descr=some descr 0
Should have 2 kids. Has 1 kids. PROBLEM
1 kid rows inserted
listing kids for run : 3 .
Kid id=4849 descr=some descr 0
Should have 3 kids. Has 1 kids. PROBLEM
1 kid rows inserted
listing kids for run : 4 .
Kid id=4849 descr=some descr 0
Should have 4 kids. Has 1 kids. PROBLEM

No clue why (with the stock 4.6.0) the bind test fails right from the 
start and not from 11th iteration.

Anyway, after the patches , it seems it works, I will keep testing with 
real life usage!


>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English:http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250506/37cdaadf/attachment.htm>