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

[Achilleas Mantzios]

On 26/4/25 08:36, Achilleas Mantzios wrote:
> Thank you Tatsuo
>
> On 26/4/25 07:16, Tatsuo Ishii wrote:
>> Hi,
>>
>> Thank you for the detailed report.
>>
>>> Dear pgpool people
>>>
>>> Yesterday during some tests which involve :  java app (wildfly) -->
>>> pgpool-II 4.6 --> pgsql 17.4 we came to a situation that a new
>>> connection could see stale or invalid cache data, meaning that the
>>> actual DB contents (5432) were not depicted on certain pgpool
>>> connections (9999).
>>>
>>> The problem exists seemingly only for inherited tables that share the
>>> same name, and belong to different schemas.
>>>
>>> The tables are :
>>>
>>> amantzio@[local]/dynacom=# \d+ public.useroptions
>>>                                                 Table
>>> "public.useroptions"
>>>   Column  |          Type          | Collation | Nullable | Default |
>>> Storage  | Compression | Stats target | Description
>>> ----------+------------------------+-----------+----------+---------+----------+-------------+--------------+------------- 
>>>
>>>
>>> username | character varying(200) |           | not null |         |
>>> extended |             |              |
>>> app      | text                   |           | not null |         |
>>> extended |             |              |
>>> detail   | text                   |           | not null |         |
>>> extended |             |              |
>>> urlext   | text                   |           | not null |         |
>>> extended |             |              |
>>> Indexes:
>>>     "useroptions_pkey" PRIMARY KEY, btree (username, app, detail)
>>> Child tables: bdelosnav.useroptions,
>>>               bdynacom.useroptions,
>>>               bdynagas.useroptions,
>>>               prominencemaritime.useroptions
>>> Access method: heap
>>>
>>> amantzio@[local]/dynacom=#
>>>
>>> In pgpool we have : memory_cache_enabled = on . All other cache
>>> related params are at default.
>>>
>>> In the specific tests we use access to bdynacom.useroptions, but
>>> accessing without fully qualified via search_path. But access of those
>>> rows from within public.useroptions also exhibit the problem(s).
>>>
>>> We have on the actual DB directly :
>>>
>>> achill at smadevnu:~/workspace/gatewaynet % psql
>>> psql (17.4)
>>> Type "help" for help.
>>>
>>> amantzio@[local]/dynacom=# SELECT tableoid::regclass, app, urlext FROM
>>> useroptions WHERE username = 'Ioannis Mazarakis' AND app IN
>>> ('performreport') AND detail = '';
>>>        tableoid       |      app      |   urlext
>>> ----------------------+---------------+------------
>>> bdynacom.useroptions | performreport | ?zz=foobar
>>> (1 row)
>>>
>>> However querying against pgpool I see a different version of the data:
>>>
>>> achill at smadevnu:~/workspace/gatewaynet % psql -p 9999
>>> psql (17.4)
>>> Type "help" for help.
>>>
>>> amantzio@[local]/dynacom=# SELECT app, urlext FROM useroptions WHERE
>>> username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail
>>> = '';
>>>       app      |        urlext
>>> ---------------+----------------------
>>> performreport | ?group=yes&groupno=7
>>> (1 row)
>>>
>>> amantzio@[local]/dynacom=#
>>>
>>> repeating with including tableoid::regclass or just /*FORCE QUERY
>>> CACHE*/ to beat the cache :
>>>
>>> /*FORCE QUERY CACHE*/ SELECT app, urlext FROM useroptions WHERE
>>> username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail
>>> = '';
>>>       app      |   urlext
>>> ---------------+------------
>>> performreport | ?zz=foobar
>>> (1 row)
>>>
>>> Using a another new user does not demo the problem :
>>>
>>> achill at smadevnu:~/workspace/gatewaynet % psql -p 9999 -U stsoukalas
>>> psql (17.4)
>>> Type "help" for help.
>>>
>>> stsoukalas@[local]/dynacom=# SELECT app, urlext FROM useroptions WHERE
>>> username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail
>>> = '';
>>>       app      |   urlext
>>> ---------------+------------
>>> performreport | ?zz=foobar
>>> (1 row)
>>>
>>> now logging with another user :
>>>
>>> achill at smadevnu:~ % psql -p 9999 -U imazarakis dynacom
>>> psql (17.4)
>>> Type "help" for help.
>>>
>>> imazarakis@[local]/dynacom=> SELECT app, urlext FROM useroptions WHERE
>>> username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail
>>> = '';
>>>       app      |   urlext
>>> ---------------+------------
>>> performreport | ?zz=foobar
>>> (1 row)
>>>
>>> imazarakis@[local]/dynacom=> /*FORCE QUERY CACHE*/  SELECT app, urlext
>>> FROM useroptions WHERE username = 'Ioannis Mazarakis' AND app IN
>>> ('performreport') AND detail = '';
>>>       app      |    urlext
>>> ---------------+---------------
>>> performreport | ?zz=achillbar
>>> (1 row)
>>>
>>> imazarakis@[local]/dynacom=> update useroptions set urlext=urlext
>>> WHERE username = 'Ioannis Mazarakis' AND app IN ('performreport') AND
>>> detail = '';
>>> UPDATE 1
>>> imazarakis@[local]/dynacom=> SELECT app, urlext FROM useroptions WHERE
>>> username = 'Ioannis Mazarakis' AND app IN ('performreport') AND detail
>>> = '';
>>>       app      |    urlext
>>> ---------------+---------------
>>> performreport | ?zz=achillbar
>>> (1 row)
>>>
>>> As we said, non inherited tables do not seem to suffer from the same
>>> issue.
>> I think the issue is not related to the table inheritance, but schema
>> search path. Since you use the same table name in the queries, you
>> should have changed the schema search path so that you can access the
>> appropriate table. I can reproduce similar issue just changing search
>> path.
>>
>> create schema s1;
>> CREATE SCHEMA
>> create schema s2;
>> CREATE SCHEMA
>> create table s1.t1(i int);
>> CREATE TABLE
>> create table s2.t1(i int);
>> CREATE TABLE
>> insert into s1.t1 values(1);
>> INSERT 0 1
>> insert into s2.t1 values(2);
>> INSERT 0 1
>> set search_path to s1,public;
>> SET
>> select * from t1;
>>   i
>> ---
>>   1
>> (1 row)
>>
>> set search_path to s2,public;
>> SET
>> select * from t1;
>>   i
>> ---
>>   1
>> (1 row)
>>
>> The last select should have returned 2 since it accessed s2.t1, but it
>> returned 1 because of the cached row produced by the previous "select
>> * from t1" which accessed s1.t1.
> in the scenarios that we did and found the problem, the search path 
> was constant. The table (effectively bdynacom.useroptions) was 
> accessed without qualification (just useroptions). But the insert and 
> the selects. This is 100% certain.

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.

>>
>>> 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?
>
> Lets stay in the inheritance example :
>
> We query :
>
> 1) public.useroptions and the row belongs to the parent table 
> (public.useroptions) : we use the  public.useroptions::regclass
>
> 2) public.useroptions and the row belongs to the child table 
> (bdynacom.useroptions) : we still use the table queried oid : 
> public.useroptions::regclass
>
> 3) bdynacom.useroptions (via search path or fully qualified) : we use 
> bdynacom.useroptions::regclass
>
>>
>> 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