[pgpool-general: 9471] 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.

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