[PostGIS] #5808: Weird constant ram increase using topology

[PostGIS]

#5808: Weird constant ram increase using topology
-----------------------+---------------------------
  Reporter:  latot     |      Owner:  strk
      Type:  defect    |     Status:  new
  Priority:  medium    |  Milestone:  PostGIS 3.5.1
 Component:  topology  |    Version:  3.5.x
Resolution:            |   Keywords:
-----------------------+---------------------------
Description changed by latot:

Old description:

> Hi! finally, possible memleak, but is very very weird.
>
> I was first using ST_ModEdgeHeal when I noticed the process was using a
> lot of ram, and it increases constantly.
>
> After some tests, I found this also happens when we create a topology:
>
> ```SQL
>
> select topology.DropTopology('muahahaha');
>
> select topology.CreateTopology('muahahaha');
>
> DROP TABLE IF EXISTS topoperf.case_concentric_circles;
>
> CREATE TABLE topoperf.case_concentric_circles AS (
>   SELECT radius, ST_ExteriorRing(
>     ST_Buffer('POINT(0 0)', radius, 128)
>   ) g
>   FROM generate_series(10, 100) radius
> );
>
> DROP TABLE IF EXISTS parent;
>
> CREATE TABLE parent();
>
> SELECT topology.AddTopoGeometryColumn(
>   'muahahaha',
>   'public',
>   'parent',
>   'topo',
>   'LINESTRING'
> ) As  layer_id;
>
> INSERT INTO parent(topo)
> SELECT topology.toTopoGeom(g, 'muahahaha', 1)
>   FROM topoperf.case_concentric_circles;
> ```
>
> If you run the code above, will finish and everything will be Ok, until
> you see the process, "postgres: postgres postgres [local] idle", is the
> one who handle the queries.
>
> Here is the Ram usage of the workflow:
>
> Run PSQL: 2MB
>
> First Run: 109MB: This is not going down even after the query finished)
>
> Second Run: 210.6MB: From the code above, run again, the INSERT
> Statement, will be executed the same geometries, the Ram increased and
> even after it finished is up.
>
> The issue is that after the INSERT statements the RAM usage only
> increases, is not free after the finish, if we try to process more data
> it will eat all our Ram.
>
> Thx!
>
> My extensions seems to need some update (? still can't find how to get
> rid of that message, alter extension or the upgrade functions seems do
> not help here, but is other issue.
>
> ```
> SELECT PostGIS_Full_Version();
>
>  POSTGIS="3.5.0 POSTGIS_REVISION" [EXTENSION] PGSQL="160"
> GEOS="3.12.1-CAPI-1.18.1" PROJ="9.4.1 NETWORK_ENABLED=OFF
> URL_ENDPOINT=https://cdn.proj.org
> USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
> DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.12.1)
> GDAL="GDAL 3.9.1, released 2024/06/22" LIBXML="2.12.7" LIBJSON="0.17"
> LIBPROTOBUF="1.5.0" WAGYU="0.5.0 (Internal)" (core procs from "3.5.0
> d2c3ca4" need upgrade) TOPOLOGY (topology procs from "3.5.0 d2c3ca4" need
> upgrade) RASTER (raster procs from "3.5.0 d2c3ca4" need upgrade)
> ```

New description:

 Hi! finally, possible memleak, but is very very weird.

 I was first using ST_ModEdgeHeal when I noticed the process was using a
 lot of ram, and it increases constantly.

 After some tests, I found this also happens when we create a topology:

 {{{

 select topology.DropTopology('muahahaha');

 select topology.CreateTopology('muahahaha');

 DROP TABLE IF EXISTS topoperf.case_concentric_circles;

 CREATE TABLE topoperf.case_concentric_circles AS (
   SELECT radius, ST_ExteriorRing(
     ST_Buffer('POINT(0 0)', radius, 128)
   ) g
   FROM generate_series(10, 100) radius
 );

 DROP TABLE IF EXISTS parent;

 CREATE TABLE parent();

 SELECT topology.AddTopoGeometryColumn(
   'muahahaha',
   'public',
   'parent',
   'topo',
   'LINESTRING'
 ) As  layer_id;

 INSERT INTO parent(topo)
 SELECT topology.toTopoGeom(g, 'muahahaha', 1)
   FROM topoperf.case_concentric_circles;
 }}}

 If you run the code above, will finish and everything will be Ok, until
 you see the process, "postgres: postgres postgres [local] idle", is the
 one who handle the queries.

 Here is the Ram usage of the workflow:

 Run PSQL: 2MB

 First Run: 109MB: This is not going down even after the query finished)

 Second Run: 210.6MB: From the code above, run again, the INSERT Statement,
 will be executed the same geometries, the Ram increased and even after it
 finished is up.

 The issue is that after the INSERT statements the RAM usage only
 increases, is not free after the finish, if we try to process more data it
 will eat all our Ram.

 Thx!

 My extensions seems to need some update (? still can't find how to get rid
 of that message, alter extension or the upgrade functions seems do not
 help here, but is other issue.

 ```
 SELECT PostGIS_Full_Version();

  POSTGIS="3.5.0 POSTGIS_REVISION" [EXTENSION] PGSQL="160"
 GEOS="3.12.1-CAPI-1.18.1" PROJ="9.4.1 NETWORK_ENABLED=OFF
 URL_ENDPOINT=https://cdn.proj.org
 USER_WRITABLE_DIRECTORY=/var/lib/postgresql/.local/share/proj
 DATABASE_PATH=/usr/share/proj/proj.db" (compiled against PROJ 9.12.1)
 GDAL="GDAL 3.9.1, released 2024/06/22" LIBXML="2.12.7" LIBJSON="0.17"
 LIBPROTOBUF="1.5.0" WAGYU="0.5.0 (Internal)" (core procs from "3.5.0
 d2c3ca4" need upgrade) TOPOLOGY (topology procs from "3.5.0 d2c3ca4" need
 upgrade) RASTER (raster procs from "3.5.0 d2c3ca4" need upgrade)
 ```

--
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5808#comment:1>
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.