Weird constant ram increase using topology

[latot]

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)

[Lars Aksel Opsahl]

Tested this more than 30 times now on this setup

POSTGIS="3.5.0dev 3.4.0rc1-1272-g80806376a" [EXTENSION] PGSQL="160" GEOS="3.12.1-CAPI-1.18.1" PROJ="9.4.0 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=/Users/lop/Library/Application Support/proj DATABASE_PATH=/opt/homebrew/Cellar/proj/9.4.0/share/proj/proj.db" (compiled against PROJ 9.12.1) LIBXML="2.13.0" LIBJSON="0.17" TOPOLOGY

and I am not able to see this memory issue

PID    COMMAND      %CPU TIME     #TH     #WQ  #PORTS MEM    PURG   CMPRS  PGRP  PPID  STATE    BOOSTS             %CPU_ME %CPU_OTHRS UID  FAULTS     COW      MSGSENT     MSGRECV

54567  postgres         0.0  00:11.39 1       0    8      294M  0B    94M   54567 48153 sleeping *0[1]        0.00000 0.00000    501 20920     115      10         5          137694     121        2387       233      0         0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  
54566  psql             0.0  00:00.02 2       1    31     4977K 0B    2368K 54566 1870  sleeping *0[1]        0.00000 0.00000    501 838       111      62         19         2706       192        137        36       0         0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  

[latot]

Note: The process who keeps the Ram, is not PSQL.

[Lars Aksel Opsahl]

Memory seems to be released as we see here some hours later for postgres

48161  postgres         0.0  00:00.06 1       0    8      2977K 0B    1344K 48161 48153 sleeping *0[1]        0.00000 0.00000    501 557       64       10         5          2604       25         539        0        2         0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  
48160  postgres         0.0  00:00.63 1       0    8      3457K 0B    1568K 48160 48153 sleeping *0[1]        0.00000 0.00000    501 528       66       10         5          18922      62         5222       0        17        0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  
48159  postgres         0.0  00:00.30 1       0    8      6113K 0B    2656K 48159 48153 sleeping *0[1]        0.00000 0.00000    501 1273      60       11         5          9809       16         11657      0        115       0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  
48157  postgres         0.0  00:00.12 1       0    8      5313K 0B    3872K 48157 48153 sleeping *0[1]        0.00000 0.00000    501 576       56       10         5          5536       17         5702       0        50        0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  
48156  postgres         0.0  00:00.73 1       0    8      17M   0B    8800K 48156 48153 sleeping *0[1]        0.00000 0.00000    501 2760      77       12         5          14196      69         6394       2        116       0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  
48153  postgres         0.0  00:02.98 1       0    22     15M   0B    12M   48153 1     sleeping *0[1]        0.00000 0.00000    501 195965    89939    10386      5180       70156      5419       15276      78       1         0.0   0      0      lop                   N/A    N/A   N/A   N/A   N/A   N/A  

The pattern seems to be normal run a job and postgres takes some memory and wait some time and memory is released.

[strk]

Having a literal POSTGIS_REVISION in the output of postgis_full_version() is unexpected, did you have trouble with SQLPP at build time ? How does postgis_config.h looks ? And grep ^SQLPP postgis/Makefile?

In any case is ​currently using ​TopMemoryContext which is never automatically released by PostgreSQL backend so if any code path fails to explicitly release that memory you'd get such increasing memory usage until disconnect

[Lars Aksel Opsahl]

Confirmed that memory is released as soon as we disconnect the psql session.

[latot]

Gentoo compiles each package and install it, any issue would block the installation, I have not had any problems installing and compiling it.

SQLPP variable from postgis and topology are the same:

SQLPP = /usr/bin/cpp -traditional-cpp -w -P -Upixel -Ubool

I have added postgis_config.h as attachment.