[pgpool-general: 7662] How to cache a SQL that is using customized function?
chen dean
dadiorchen at outlook.com
Sun Aug 8 12:46:25 JST 2021
Hi, pgpool team:
I'm using pgpool as my cache for postgresql, my question is, why I can not cache this SQL:
/* sql case1 tile */
SELECT
'cluster' AS type,
'case1 tile' AS log,
NULL AS zoom_to,
region_id id,
centroid estimated_geometric_location,
St_asgeojson(centroid) latlon,
type_id as region_type,
count(tree_region.id) count,
CASE WHEN count(tree_region.id) > 1000
THEN (count(tree_region.id) / 1000) || 'K'
ELSE count(tree_region.id) || ''
END AS count_text
FROM active_tree_region tree_region
WHERE zoom_level = 8
AND tree_region.tree_id IN(
select distinct * from (
SELECT trees.id as id from trees
INNER JOIN (
SELECT id FROM planter
JOIN (
SELECT entity_id FROM getEntityRelationshipChildren(
(SELECT id FROM entity WHERE map_name = 'TheHaitiTreeProject')
)
) org ON planter.organization_id = org.entity_id
) planter_ids
ON trees.planter_id = planter_ids.id
union all
SELECT trees.id as id from trees
INNER JOIN (
SELECT id FROM planter
JOIN (
SELECT entity_id FROM getEntityRelationshipChildren(
(SELECT id FROM entity WHERE map_name = 'TheHaitiTreeProject')
)
) org ON planter.organization_id = org.entity_id
) planter_ids
ON trees.planter_id = planter_ids.id
) t1
)
GROUP BY region_id, centroid, type_idtreetracker
In the SQL, I guess that's because the function `getEntityRelationshipChildren` make pgpool ignore the result of this SQL? This is a function I created by myself, it would select some other table and return the result, basically, I hope it get cached.
According to the document, there is a setting called `cache_safe_memqcache_table_list.` but the document says it just support view and table, so, how could I make this SQL cached?
Thanks! team.
Dadior
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210808/449adb00/attachment.htm>
More information about the pgpool-general
mailing list