[pgpool-general: 5482] Re: Memcache: white_memqcache_table_list ignored if table name matches black_memqcache_table_list

[Tatsuo Ishii]

> I have looked into this and found that the behavior of the program
> has never been same as descibed in:
> https://www.sraoss.co.jp/event_seminar/2012/20121024_pgpool-II_pgconfEU2012_sraoss.pdf
> Sorry for this.
> 
> So the commit:
> https://github.com/pgpool/pgpool2/commit/41febb3aaa4480e0c9219e4538e04ef7398669a6
> actually did not break the the behavior.
> 
> Here is the correct description of the current behavior of the code:
> 
> 1) Check black_memqcache_table_list. If the target table name matches
> any of the entries, the table is not cached. Done.
> 
> 2) If the table is one of:
> 
>    SELECTs including views
>    SELECTs including non immutable functions
>    SELECTs including temp tables, unlogged tables
>    SELECT result is too large (memqcache_maxcache)
>    SELECT FOR SHARE/UPDATE
>    SELECT starting with "/*NO QUERY CACHE*/" comment
>    SELECT including system catalogs
>    SELECT uses TABLESAMPLE
> 
>    then the table is not cached. Go to step 3.
> 
> 3) If white_memqcache_table_list is not empty and the table is either
> VIEW or unlogged table, and the table name matches any of the entries
> of white_memqcache_table_list, then the table is cached. If not, the
> table is not cached. Done.
> 
> 4) Other tables are cached even if they are not listed in
> white_memqcache_table_list.

I have updated the documentation of the query cache in master and 3.6
stable branch in the direction above.

> So to satify your request, following method can be used.
> 
> 1) Keep white_memqcache_table_list empty.
> 
> 2) Add a regular expression which means "except table_to_cache' to
> black_memqcache_table_list. For example if you want to cache 't1', you
> can use following regular expression.
> 
> black_memqcache_table_list = '(([^1]|[^t]1)|[^t]t)+'
> 
> This is pretty confusing but it works.
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general