[pgpool-general: 9433] Re: Clarification on query results cache visibility
Achilleas Mantzios
a.mantzios at cloud.gatewaynet.com
Sun Apr 27 00:05:16 JST 2025
On 26/4/25 13:39, Achilleas Mantzios wrote:
>
>
> 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.
From the tests I am doing today, I am very confident that this hasn't
got to do with search_path.
It is like it created a "hidden" cache entry that is only surfacing
under some conditions.
Please look :
straight to pgsql :
achill at smadevnu:~ % psql -U amantzio
psql (17.4)
Type "help" for help.
amantzio@[local]/dynacom=# SELECT tableoid::regclass, app, urlext FROM
useroptions WHERE username = 'Achilleas
Mantzios' AND app IN ('performreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+----------------------
bdynacom.useroptions | performreport | *?group=yes&groupno=7 <-- real value*
(1 row)
marked as bold above is the correct value (real value).
1st pgpool session :
achill at smadevnu:~ % psql -p 9999 -U amantzio dynacom
psql (17.4)
Type "help" for help.
amantzio@[local]/dynacom=# SELECT app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app IN ('performreport') AND d
etail = '';
app | urlext
---------------+----------------------
performreport | *?group=yes&groupno=2 <-- old value*
(1 row)
amantzio@[local]/dynacom=# SELECT tableoid::regclass, app, urlext FROM
useroptions WHERE username = 'Achilleas Mantzios' AND app IN ('p
erformreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+-----------
bdynacom.useroptions | performreport | *?group=no <-- also old value*
(1 row)
bow besides the false stale value ( *?group=yes&groupno=2 ) *if we add
tableoid in the select we also get another also false and stale value :
"group=no"
2nd pgpool session
pgpool at smadevnu:~ % psql -p 9999 -U amantzio dynacom
psql (15.7, server 17.4)
WARNING: psql major version 15, server major version 17.
Some psql features might not work.
Type "help" for help.
dynacom=# SELECT app, urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport
') AND detail = '';
app | urlext
---------------+----------------------
performreport | *?group=yes&groupno=2*
(1 row)
dynacom=# /*NO QUERY CACHE*/ SELECT app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app
IN ('performreport') AND detail = '';
app | urlext
---------------+----------------------
performreport | *?group=yes&groupno=7 <---- real value*
(1 row)
dynacom=# /*FORCE QUERY CACHE*/ SELECT app, urlext FROM useroptions
WHERE username = 'Achilleas Mantzios' AND
app IN ('performreport') AND detail = '';
app | urlext
---------------+-----------
performreport | *?group=no <-- also old value*
(1 row)
In the first select we get the same old version as in the 1st session,
in the second select (no query cache) we get the real correct value ,
while in the last select by forcing query cache, we get the same value
as if putting the tableoid in the select.
now comes the ultra crazy part :
same session :
dynacom=# SELECT tableoid::regclass,app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND app
IN ('performreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+----------------------
bdynacom.useroptions | performreport | ?group=yes&groupno=7
(1 row)
dynacom=# SELECT tableoid::regclass, app, urlext FROM useroptions WHERE
username = 'Achilleas Mantzios' AND ap
p IN ('performreport') AND detail = '';
tableoid | app | urlext
----------------------+---------------+-----------
bdynacom.useroptions | performreport | ?group=no
(1 row)
can you spot the difference ? it is the space after "regclass," here :
tableoid::regclass, app
here without the tableoid :
dynacom=# SELECT app, urlext FROM useroptions WHERE username =
'Achilleas Mantzios' AND app IN ('performreport
') AND detail = '';
app | urlext
---------------+----------------------
performreport | ?group=yes&groupno=2
(1 row)
dynacom=# SELECT app,urlext FROM useroptions WHERE username = 'Achilleas
Mantzios' AND app IN ('performreport'
) AND detail = '';
app | urlext
---------------+----------------------
performreport | ?group=yes&groupno=7
(1 row)
only different thing is the space in the select ,
so I am afraid the situation is much worse than simply fixing the
search_path (which obviously is not relevant here)
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English:http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250426/5de82f46/attachment-0001.htm>
More information about the pgpool-general
mailing list