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

[Tatsuo Ishii]

> sorry above I meant to say :
> 
> But both the inserts and the selects were done with table not being
> qualified , and with a constant search_path.
> 
> The thing to add here is that search_path is set by calling : void
> set_search_path(text) a volatile function.

I'm confused. So the search_path is literary always constant (not
something like '$user,public'), or the search_path is changed by
set_search_path?

>>>
>>>> Now granted , either :
>>>>
>>>> a) disabling the memory cache  or
>>>>
>>>> b) placing a specified comment HINT in front of the query  or
>>>>
>>>> c) specifying the affected tables in cache_unsafe_memqcache_table_list
>>>> or
>>>>
>>>> d)or using BEGIN ; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  ,
>>>> or above
>>>>
>>>> as you explain here :
>>>> https://pgsqlpgpool.blogspot.com/2021/04/visibility-with-query-cache.html
>>> Sorry but I don't understand why "BEGIN ; SET TRANSACTION ISOLATION
>>> LEVEL REPEATABLE READ  ," is useful for your problem.
>>>
>>>> (btw, why non inherited tables work so nice with the cache ??? without
>>>> the issue above?)
>>> I don't think inheritance is relevant. See above.
>>>
>>>> If the blog is still valid, why not state this explicitly inside the
>>>> docs?
>>> I think the blog is still valid but it's not connected to your
>>> problem.
>>>
>>>> And if the blog is no longer relevant , please include such a
>>>> statement.
>>>>
>>>> So the above could help alleviate the problem ... however:
>>>>
>>>> a) disabling the cache ... is not ideal , I mean why not have such a
>>>> powerful feature ?
>>>>
>>>> b) placing comments in 1000s of queries is not an option
>>>>
>>>> c) we would not like to restrict tables based on a bug
>>>>
>>>> d) same as b) rewriting all our transactions is not an option , also
>>>> we would not like to change the default default_transaction_isolation.
>>>>
>>>> So can you please explain what is the state of affairs regarding
>>>> pgpool query cache and inherited tables? or pgpool caching and
>>>> visibility ?
>>> As stated above, the query cache does not take account of schema
>>> search path. I think that's source of the problem. Probably in
>>> addition to user name, database and bind parameters (if any), the
>>> schema search path should be added to the data when pgpool calculates
>>> a MD5 hash (which is the cache key) on it. However this requires
>>> pgpool to obtain the search path using "show search_path", which adds
>>> an additional overhead while fetching query cache. Any idea?
>>
>> regarding the MD5 calculation, instead of the schema why not the
>> effective oid of the actual table?

Because the number of tables included in a SELECT is not necessarily
1, it could be 2, 3 or more, or even 0.

And even if we used table oids, the would add extract. If we have to
pay an extra cost, using search_path is simpler.

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