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

[Tatsuo Ishii]

>>> 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!
> 
> Yeah, the bug (fixed by fix_bind.patch) probably has been there since
> the query cache was introduced. Thank you for spotting it!
> 
>> 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 :
> [snip]
>> No clue why (with the stock 4.6.0) the bind test fails right from the
>> start and not from 11th iteration.
> 
> I'm not surpised to hear that. Since 4.6.0 smashes all information
> including oids if table modofying command is coming, no cache
> invalidation happens.
> 
>> Anyway, after the patches , it seems it works, I will keep testing
>> with real life usage!
> 
> Thanks. I am looking forward to hearing from you regarding the test
> results.

In the mean time I have pushed v2 patch to all supported branches.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=3f36fe3a39fe7c9cceec368d465506c7d1bc1c27

As as the patch fixes the first problem without degradation like the
second issue. I will watch the regression test results to see if the
patch does anything bad.

Best regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp