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

[Tatsuo Ishii]

> 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.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
-------------- next part --------------
A non-text attachment was scrubbed...
Name: fix_bind.patch
Type: application/octet-stream
Size: 1247 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250506/9befae93/attachment-0001.obj>