[postgis-tickets] [PostGIS] #4674: lwgeom_cache doesn't live through not inline functions

[PostGIS]

#4674: lwgeom_cache doesn't live through not inline functions
-------------------------+---------------------------
  Reporter:  Raúl Marín  |      Owner:  pramsey
      Type:  defect      |     Status:  new
  Priority:  medium      |  Milestone:  PostGIS 3.1.0
 Component:  postgis     |    Version:  master
Resolution:              |   Keywords:
-------------------------+---------------------------

Comment (by Raúl Marín):

 I've made it work using `CurTransactionContext` but that only free's the
 cache at the end of the full transaction.

 For example, using a BEGIN-END group:

 {{{
 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=# BEGIN;
 BEGIN
 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=#
 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=# explain
 (analyze, verbose) Select ST_AsGML(the_geom) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 limit 4;
 NOTICE:  GetGenericCacheCollection NOT FOUND
 NOTICE:  generic_cache not found
 NOTICE:  Cache not found. Expected: 4326 1. Found: 0 0 null
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  Cache FOUND. Expected: 4326 1
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  Cache FOUND. Expected: 4326 1
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  Cache FOUND. Expected: 4326 1
 QUERY PLAN

 -----------------------------------------------------------------------------------------------------------------------------
 ----------------------------------------------------
  Limit  (cost=0.00..1.25 rows=4 width=32) (actual time=1.112..1.417 rows=4
 loops=1)
    Output: (st_asgml(the_geom, 15, 0))
    ->  Seq Scan on
 public.benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..213260.58 rows=683833
 width=32) (actual time=1.110..1.414 rows=4 loops=1)
          Output: st_asgml(the_geom, 15, 0)
  Planning Time: 0.459 ms
  Execution Time: 1.443 ms
 (6 rows)

 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=#
 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=# explain
 (analyze, verbose) Select ST_AsGML(3, the_geom::geometry) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 limit 4;
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  Cache FOUND. Expected: 4326 1
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  Cache FOUND. Expected: 4326 1
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  Cache FOUND. Expected: 4326 1
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  Cache FOUND. Expected: 4326 1
 QUERY PLAN

 -----------------------------------------------------------------------------------------------------------------------------
 -----------------------------------------------------
  Limit  (cost=0.00..10.25 rows=4 width=32) (actual time=0.096..0.273
 rows=4 loops=1)
    Output: (_st_asgml(3, the_geom, 15, 0, NULL::text, NULL::text))
    ->  Seq Scan on
 public.benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..1751884.83 rows=683833
  width=32) (actual time=0.095..0.270 rows=4 loops=1)
          Output: _st_asgml(3, the_geom, 15, 0, NULL::text, NULL::text)
  Planning Time: 0.110 ms
  Execution Time: 0.293 ms
 (6 rows)

 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=#
 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=# explain
 (analyze, verbose) Select ST_Transform(the_geom, 3857),
 ST_Transform(the_geom, 3857) from
 benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293 limit 4;
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 NOTICE:  GetGenericCacheCollection FOUND!!!!!
 QUERY PLAN

 -----------------------------------------------------------------------------------------------------------------------------
 -----------------------------------------------------
  Limit  (cost=0.00..20.25 rows=4 width=64) (actual time=7.136..7.202
 rows=4 loops=1)
    Output: (st_transform(the_geom, 3857)), (st_transform(the_geom, 3857))
    ->  Seq Scan on
 public.benchmark_7773a711c8441d4b494a51fd9feebeac7a9b9c734619398620293
 (cost=0.00..3461467.33 rows=683833
  width=64) (actual time=7.135..7.201 rows=4 loops=1)
          Output: st_transform(the_geom, 3857), st_transform(the_geom,
 3857)
  Planning Time: 0.040 ms
  Execution Time: 7.233 ms
 (6 rows)

 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=#
 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=# END;
 NOTICE:  PostgisResetInternalCache
 COMMIT
 cartodb_dev_user_f80dfdef-ea5c-498d-b98a-82362650d944_db=#
 }}}

 Notice how `END` resets the cache.

 I think it would be best if the cache only lives until the end of a single
 query (and not the full transaction) but I haven't found out how to do it
 yet.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4674#comment:3>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.