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

[Achilleas Mantzios]

On 26/4/25 10:21, Tatsuo Ishii wrote:
>> 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?

the app (wildfly) has this in the conf :

<new-connection-sql>SET application_name to 'SMA ';select 
set_search_path('bdynacom,epaybdynacom,epay');</new-connection-sql>

This function does some logic and then sets the search path: so by 
calling it with the above argument values it yields : 
anonym,bdynacom,epaybdynacom,epay,"$user", public

So the potentially relevant schemata here are  the ones that contain our 
tables  useroptions : bdynacom and public.

The above is called at the start of the connection, only. No search path 
setting in the code. We base our multi-tenancy on this technique to be 
more precise, so every tenant application sets its own search path, but 
only once at the start of the connection. So all insertions and selects 
and updates happen on the unqualified table : useroptions, which is 
bdynacom.useroptions.

*The problem was discovered inside the same app, with same search_path,  
with the same user, it inserted the new value, but could not read it. We 
were unable to reproduce this with plain SQL.*

So we may need to try to reproduce it with java and send you the full 
working bug. Would you like us to to do this?

>
>>>>> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250426/e2b43954/attachment.htm>