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.

[latot]

select postgis_lib_revision();
 postgis_lib_revision 
----------------------
 POSTGIS_REVISION

grep POSTGIS_LIB_VERSION postgis_config.h
#define POSTGIS_LIB_VERSION "3.5.0"

[lnicola]

I found a minimization for this bug, or a related one:

begin;
select topology.TopoGeo_addLinestring('t5808', 'LINESTRING(1 0,0 -1,-1 0,0 1)', 0); select topology.TopoGeo_addLinestring('t5808', 'LINESTRING(2 0,0 -2,-2 0,0 2)', 0);
rollback;

I tried to trace the allocations and found some potential leaks at these two places:

• ​https://gitea.osgeo.org/gitea/postgis/postgis/src/tag/3.5.0/liblwgeom/topo/lwgeom_topo.c#L681 (didn't pay attention, but I don't know if it gets freed on the path at ​https://gitea.osgeo.org/gitea/postgis/postgis/src/tag/3.5.0/liblwgeom/topo/lwgeom_topo.c#L715)
• ​https://gitea.osgeo.org/gitea/postgis/postgis/src/tag/3.5.0/liblwgeom/topo/lwgeom_topo.c#L6996, which potentially doesn't get freed at ​https://gitea.osgeo.org/gitea/postgis/postgis/src/tag/3.5.0/liblwgeom/topo/lwgeom_topo.c#L7006

Full stack traces:

Breakpoint 1.1, operator new (sz=72) at ../../../../src/libstdc++-v3/libsupc++/new_op.cc:43
43	in ../../../../src/libstdc++-v3/libsupc++/new_op.cc
#0  0x00007f1832385a95 in geos::geom::GeometryFactory::createLineString(std::unique_ptr<geos::geom::CoordinateSequence, std::default_delete<geos::geom::CoordinateSequence> >&&) const () from /lib/x86_64-linux-gnu/libgeos.so.3.13.0
#1  0x00007f1832c0c579 in GEOSGeom_createLineString_r () from /lib/x86_64-linux-gnu/libgeos_c.so.1
#2  0x00007f1831cfffd8 in LWGEOM2GEOS (lwgeom=lwgeom@entry=0x5619b60f6568, autofix=autofix@entry=0 '\000') at ../liblwgeom/lwgeom_geos.c:471
#3  0x00007f1831d02a6e in _lwt_CheckEdgeCrossing (topo=0x5619b5ed35b0, start_node=642, end_node=643, geom=0x5619b60f6568, myself=0) at ../liblwgeom/topo/lwgeom_topo.c:618
#4  0x00007f1831d1bee5 in _lwt_AddEdge.constprop.0 (topo=0x5619b5ed35b0, start_node=<optimized out>, end_node=643, geom=0x5619b60f6568, modFace=1, skipChecks=0) at ../liblwgeom/topo/lwgeom_topo.c:2488
#5  0x00007f1831d1340f in _lwt_AddLineEdge (topo=topo@entry=0x5619b5ed35b0, edge=0x5619b60f6568, tol=tol@entry=3.6000000000000001e-15, handleFaceSplit=handleFaceSplit@entry=1, forward=forward@entry=0x7fff73ce1650)
    at ../liblwgeom/topo/lwgeom_topo.c:6879
#6  0x00007f1831d13e1c in _lwt_AddLine (topo=<optimized out>, line=<optimized out>, tol=3.6000000000000001e-15, nedges=0x7fff73ce176c, handleFaceSplit=1) at ../liblwgeom/topo/lwgeom_topo.c:7265
#7  0x00007f1831cf7aaa in TopoGeo_AddLinestring (fcinfo=0x5619b6059540) at /build/postgis-vUdkWY/postgis-3.5.0+dfsg/topology/postgis_topology.c:5141
#8  0x00005619a0ec3e41 in ExecMakeFunctionResultSet (fcache=0x5619b60586c0, econtext=econtext@entry=0x5619b6057d28, argContext=0x5619b5e5dbf0, isNull=0x5619b6058668, isDone=isDone@entry=0x5619b60586b0)
    at ./build/../src/backend/executor/execSRF.c:624
#9  0x00005619a0eea836 in ExecProjectSRF (node=node@entry=0x5619b6057c20, continuing=continuing@entry=false) at ./build/../src/backend/executor/nodeProjectSet.c:182
#10 0x00005619a0eea8d5 in ExecProjectSet (pstate=0x5619b6057c20) at ./build/../src/backend/executor/nodeProjectSet.c:106
#11 0x00005619a0eba7dd in ExecProcNode (node=0x5619b6057c20) at ./build/../src/include/executor/executor.h:274
#12 ExecutePlan (estate=0x5619b6057a08, planstate=0x5619b6057c20, use_parallel_mode=<optimized out>, operation=CMD_SELECT, sendTuples=true, numberTuples=0, direction=<optimized out>, dest=0x5619b604e248, execute_once=<optimized out>)
    at ./build/../src/backend/executor/execMain.c:1648
#13 standard_ExecutorRun (queryDesc=0x5619b5d9cb78, direction=<optimized out>, count=0, execute_once=<optimized out>) at ./build/../src/backend/executor/execMain.c:365
#14 0x00005619a10782ae in PortalRunSelect (portal=portal@entry=0x5619b5dc5348, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0x5619b604e248) at ./build/../src/backend/tcop/pquery.c:924
#15 0x00005619a10798a8 in PortalRun (portal=portal@entry=0x5619b5dc5348, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x5619b604e248, 
    altdest=altdest@entry=0x5619b604e248, qc=0x7fff73ce1bc0) at ./build/../src/backend/tcop/pquery.c:768
#16 0x00005619a1075a24 in exec_simple_query (query_string=0x5619b5d20208 "select topology.TopoGeo_addLinestring('t5808', 'LINESTRING(1 0,0 -1,-1 0,0 1)', 0);") at ./build/../src/backend/tcop/postgres.c:1278
#17 0x00005619a1077660 in PostgresMain (dbname=<optimized out>, username=<optimized out>) at ./build/../src/backend/tcop/postgres.c:4767
#18 0x00005619a1071e03 in BackendMain (startup_data=<optimized out>, startup_data_len=<optimized out>) at ./build/../src/backend/tcop/backend_startup.c:105
#19 0x00005619a0fdc50e in postmaster_child_launch (child_type=child_type@entry=B_BACKEND, startup_data=startup_data@entry=0x7fff73ce2050 "", startup_data_len=startup_data_len@entry=4, client_sock=client_sock@entry=0x7fff73ce2070)
    at ./build/../src/backend/postmaster/launch_backend.c:277
#20 0x00005619a0fe0215 in BackendStartup (client_sock=0x7fff73ce2070) at ./build/../src/backend/postmaster/postmaster.c:3593
#21 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1674
#22 0x00005619a0fe1f30 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0x5619b5cdcaa0) at ./build/../src/backend/postmaster/postmaster.c:1372
#23 0x00005619a0cf83da in main (argc=5, argv=0x5619b5cdcaa0) at ./build/../src/backend/main/main.c:197

Breakpoint 1.1, operator new (sz=96) at ../../../../src/libstdc++-v3/libsupc++/new_op.cc:43
43	in ../../../../src/libstdc++-v3/libsupc++/new_op.cc
0x00007f1832379cb3 in geos::geom::CoordinateSequence::CoordinateSequence(unsigned long, bool, bool, bool) () from /lib/x86_64-linux-gnu/libgeos.so.3.13.0
#0  0x00007f1832379cb3 in geos::geom::CoordinateSequence::CoordinateSequence(unsigned long, bool, bool, bool) () from /lib/x86_64-linux-gnu/libgeos.so.3.13.0
#1  0x00007f1832c0b262 in GEOSCoordSeq_copyFromBuffer_r () from /lib/x86_64-linux-gnu/libgeos_c.so.1
#2  0x00007f1831cfdbce in ptarray_to_GEOSCoordSeq (pa=0x5619b5d839a0, fix_ring=fix_ring@entry=0 '\000') at ../liblwgeom/lwgeom_geos.c:302
#3  0x00007f1831cfffd0 in LWGEOM2GEOS (lwgeom=0x5619b5d838f0, autofix=<optimized out>) at ../liblwgeom/lwgeom_geos.c:470
#4  0x00007f1831d13bb6 in _lwt_AddLine (topo=<optimized out>, line=<optimized out>, tol=7.2000000000000002e-15, nedges=0x7fff73ce176c, handleFaceSplit=1) at ../liblwgeom/topo/lwgeom_topo.c:6996
#5  0x00007f1831cf7aaa in TopoGeo_AddLinestring (fcinfo=0x5619b6059540) at /build/postgis-vUdkWY/postgis-3.5.0+dfsg/topology/postgis_topology.c:5141
#6  0x00005619a0ec3e41 in ExecMakeFunctionResultSet (fcache=0x5619b60586c0, econtext=econtext@entry=0x5619b6057d28, argContext=0x5619b5e49090, isNull=0x5619b6058668, isDone=isDone@entry=0x5619b60586b0)
    at ./build/../src/backend/executor/execSRF.c:624
#7  0x00005619a0eea836 in ExecProjectSRF (node=node@entry=0x5619b6057c20, continuing=continuing@entry=false) at ./build/../src/backend/executor/nodeProjectSet.c:182
#8  0x00005619a0eea8d5 in ExecProjectSet (pstate=0x5619b6057c20) at ./build/../src/backend/executor/nodeProjectSet.c:106
#9  0x00005619a0eba7dd in ExecProcNode (node=0x5619b6057c20) at ./build/../src/include/executor/executor.h:274
#10 ExecutePlan (estate=0x5619b6057a08, planstate=0x5619b6057c20, use_parallel_mode=<optimized out>, operation=CMD_SELECT, sendTuples=true, numberTuples=0, direction=<optimized out>, dest=0x5619b60979d8, execute_once=<optimized out>)
    at ./build/../src/backend/executor/execMain.c:1648
#11 standard_ExecutorRun (queryDesc=0x5619b5d9cb78, direction=<optimized out>, count=0, execute_once=<optimized out>) at ./build/../src/backend/executor/execMain.c:365
#12 0x00005619a10782ae in PortalRunSelect (portal=portal@entry=0x5619b5dc5348, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0x5619b60979d8) at ./build/../src/backend/tcop/pquery.c:924
#13 0x00005619a10798a8 in PortalRun (portal=portal@entry=0x5619b5dc5348, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x5619b60979d8, 
    altdest=altdest@entry=0x5619b60979d8, qc=0x7fff73ce1bc0) at ./build/../src/backend/tcop/pquery.c:768
#14 0x00005619a1075a24 in exec_simple_query (query_string=0x5619b5d20208 "select topology.TopoGeo_addLinestring('t5808', 'LINESTRING(2 0,0 -2,-2 0,0 2)', 0);") at ./build/../src/backend/tcop/postgres.c:1278
#15 0x00005619a1077660 in PostgresMain (dbname=<optimized out>, username=<optimized out>) at ./build/../src/backend/tcop/postgres.c:4767
#16 0x00005619a1071e03 in BackendMain (startup_data=<optimized out>, startup_data_len=<optimized out>) at ./build/../src/backend/tcop/backend_startup.c:105
#17 0x00005619a0fdc50e in postmaster_child_launch (child_type=child_type@entry=B_BACKEND, startup_data=startup_data@entry=0x7fff73ce2050 "", startup_data_len=startup_data_len@entry=4, client_sock=client_sock@entry=0x7fff73ce2070)
    at ./build/../src/backend/postmaster/launch_backend.c:277
#18 0x00005619a0fe0215 in BackendStartup (client_sock=0x7fff73ce2070) at ./build/../src/backend/postmaster/postmaster.c:3593
#19 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1674
#20 0x00005619a0fe1f30 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0x5619b5cdcaa0) at ./build/../src/backend/postmaster/postmaster.c:1372
#21 0x00005619a0cf83da in main (argc=5, argv=0x5619b5cdcaa0) at ./build/../src/backend/main/main.c:197

Breakpoint 1.1, operator new (sz=32) at ../../../../src/libstdc++-v3/libsupc++/new_op.cc:43
43	in ../../../../src/libstdc++-v3/libsupc++/new_op.cc
0x00007f1832c0b241 in GEOSCoordSeq_copyFromBuffer_r () from /lib/x86_64-linux-gnu/libgeos_c.so.1
#0  0x00007f1832c0b241 in GEOSCoordSeq_copyFromBuffer_r () from /lib/x86_64-linux-gnu/libgeos_c.so.1
#1  0x00007f1831cfdbce in ptarray_to_GEOSCoordSeq (pa=0x5619b5d9c7b0, fix_ring=fix_ring@entry=0 '\000') at ../liblwgeom/lwgeom_geos.c:302
#2  0x00007f1831cfffd0 in LWGEOM2GEOS (lwgeom=lwgeom@entry=0x5619b60f6568, autofix=autofix@entry=0 '\000') at ../liblwgeom/lwgeom_geos.c:470
#3  0x00007f1831d02a6e in _lwt_CheckEdgeCrossing (topo=0x5619b5ed35b0, start_node=642, end_node=643, geom=0x5619b60f6568, myself=0) at ../liblwgeom/topo/lwgeom_topo.c:618
#4  0x00007f1831d1bee5 in _lwt_AddEdge.constprop.0 (topo=0x5619b5ed35b0, start_node=<optimized out>, end_node=643, geom=0x5619b60f6568, modFace=1, skipChecks=0) at ../liblwgeom/topo/lwgeom_topo.c:2488
#5  0x00007f1831d1340f in _lwt_AddLineEdge (topo=topo@entry=0x5619b5ed35b0, edge=0x5619b60f6568, tol=tol@entry=3.6000000000000001e-15, handleFaceSplit=handleFaceSplit@entry=1, forward=forward@entry=0x7fff73ce1650)
    at ../liblwgeom/topo/lwgeom_topo.c:6879
#6  0x00007f1831d13e1c in _lwt_AddLine (topo=<optimized out>, line=<optimized out>, tol=3.6000000000000001e-15, nedges=0x7fff73ce176c, handleFaceSplit=1) at ../liblwgeom/topo/lwgeom_topo.c:7265
#7  0x00007f1831cf7aaa in TopoGeo_AddLinestring (fcinfo=0x5619b6059540) at /build/postgis-vUdkWY/postgis-3.5.0+dfsg/topology/postgis_topology.c:5141
#8  0x00005619a0ec3e41 in ExecMakeFunctionResultSet (fcache=0x5619b60586c0, econtext=econtext@entry=0x5619b6057d28, argContext=0x5619b5e5dbf0, isNull=0x5619b6058668, isDone=isDone@entry=0x5619b60586b0)
    at ./build/../src/backend/executor/execSRF.c:624
#9  0x00005619a0eea836 in ExecProjectSRF (node=node@entry=0x5619b6057c20, continuing=continuing@entry=false) at ./build/../src/backend/executor/nodeProjectSet.c:182
#10 0x00005619a0eea8d5 in ExecProjectSet (pstate=0x5619b6057c20) at ./build/../src/backend/executor/nodeProjectSet.c:106
#11 0x00005619a0eba7dd in ExecProcNode (node=0x5619b6057c20) at ./build/../src/include/executor/executor.h:274
#12 ExecutePlan (estate=0x5619b6057a08, planstate=0x5619b6057c20, use_parallel_mode=<optimized out>, operation=CMD_SELECT, sendTuples=true, numberTuples=0, direction=<optimized out>, dest=0x5619b604e248, execute_once=<optimized out>)
    at ./build/../src/backend/executor/execMain.c:1648
#13 standard_ExecutorRun (queryDesc=0x5619b5d9cb78, direction=<optimized out>, count=0, execute_once=<optimized out>) at ./build/../src/backend/executor/execMain.c:365
#14 0x00005619a10782ae in PortalRunSelect (portal=portal@entry=0x5619b5dc5348, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0x5619b604e248) at ./build/../src/backend/tcop/pquery.c:924
#15 0x00005619a10798a8 in PortalRun (portal=portal@entry=0x5619b5dc5348, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x5619b604e248, 
    altdest=altdest@entry=0x5619b604e248, qc=0x7fff73ce1bc0) at ./build/../src/backend/tcop/pquery.c:768
#16 0x00005619a1075a24 in exec_simple_query (query_string=0x5619b5d20208 "select topology.TopoGeo_addLinestring('t5808', 'LINESTRING(1 0,0 -1,-1 0,0 1)', 0);") at ./build/../src/backend/tcop/postgres.c:1278
#17 0x00005619a1077660 in PostgresMain (dbname=<optimized out>, username=<optimized out>) at ./build/../src/backend/tcop/postgres.c:4767
#18 0x00005619a1071e03 in BackendMain (startup_data=<optimized out>, startup_data_len=<optimized out>) at ./build/../src/backend/tcop/backend_startup.c:105
#19 0x00005619a0fdc50e in postmaster_child_launch (child_type=child_type@entry=B_BACKEND, startup_data=startup_data@entry=0x7fff73ce2050 "", startup_data_len=startup_data_len@entry=4, client_sock=client_sock@entry=0x7fff73ce2070)
    at ./build/../src/backend/postmaster/launch_backend.c:277
#20 0x00005619a0fe0215 in BackendStartup (client_sock=0x7fff73ce2070) at ./build/../src/backend/postmaster/postmaster.c:3593
#21 ServerLoop () at ./build/../src/backend/postmaster/postmaster.c:1674
#22 0x00005619a0fe1f30 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0x5619b5cdcaa0) at ./build/../src/backend/postmaster/postmaster.c:1372
#23 0x00005619a0cf83da in main (argc=5, argv=0x5619b5cdcaa0) at ./build/../src/backend/main/main.c:197

[Sandro Santilli <strk@…>]

In 0faa5747/git:

Simplify releasing of GEOS memory

References #5808

[Sandro Santilli <strk@…>]

In 474a54d/git:

Fix small leak of GEOSGeometry

References #5808 comment:10

[Sandro Santilli <strk@…>]

In 48f1d4b/git:

Simplify releasing of GEOS memory

References #5808

[Sandro Santilli <strk@…>]

In 94dfbcaf/git:

Fix small leak of GEOSGeometry

References #5808 comment:10

[strk]

The leak in comment:10 is gone, I still see RSS growing by repeatedly sourcing your comment:10 SQL code into the same session. Doesn't grow at each and every run but it does grow eventually. Valgrind doesn't have much to say.

[Sandro Santilli <strk@…>]

In e4794510/git:

Reduce use of TopMemoryContext

References #5808

[Sandro Santilli <strk@…>]

In 75d9b03/git:

Explicitly release state of topology set-returning functions

References #5808

[Lars Aksel Opsahl]

I am now running a test with POSTGIS="3.6.0dev 3.5.0-44-g6366bd678" on a small test server with code (before memory fixes) and I have enabled sar so we have something to compare with before upgrade to 75d9b03d6c. This test server is only used by me and it's only running Postgres.

The test below start at 2024-11-08 11:19:36.246308+01

CALL topo_rog_static.rog_overlay(
   ARRAY[
'sk_grl.n5_forvaltning_flate n5',
'prod_dyrkbarjord_05.snapshot_ar5_flate ar5',
'prod_dyrkbarjord_05.snapshot_ssb_flate ssb',
'prod_dyrkbarjord_05.snapshot_mdir_flate mdir',
'prod_dyrkbarjord_05.snapshot_dmk_flate dmk',
'prod_dyrkbarjord_05.snapshot_ravine_dtm10_dmk_flate dtm10',
'prod_dyrkbarjord_05.snapshot_ravine_statsforvalter_flate ravine'
   ],
   'tmp_dyrkbarjord_12.dyrkbarjord_06',
   4258, --srid
   0, -- no snapto
   10.0, -- min m2 area to keep
   0, --max vertx length
   false, -- _break_up_big_polygons
   1.0, -- min negative buffer value null, remove sliver polygons
   ARRAY[
('0103000020A21000000100000005000000B3C573C549A82240C2497FD1316F4F40B3C573C549A8224059E5729B33DC4F40A3DA057EEABE264059E5729B33DC4F40A3DA057EEABE2640C2497FD1316F4F40B3C573C549A82240C2497FD1316F4F40','037')::rog_input_boundary::rog_input_boundary
  ]
   , -- run this area only
   true, -- do_qualitycheck_on_final_reseult
   --  Method 1 topology.TopoGeo_addLinestring
   --  Method 2 topology.TopoGeo_LoadGeometry
   --  Method 3 select from tmp toplogy into master topology
   3,
   10, -- _max_parallel_jobs int, -- this is the max number of paralell jobs to run. There must be at least the same number of free connections
   600, -- _max_rows_in_each_cell int, -- this is the max number rows that intersects with box before it's split into 4 new boxes
   null
   );

From sar we get this now (code before memory update)

10:40:03    kbmemfree   kbavail kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit  kbactive   kbinact   kbdirty
10:50:02     61142104  62946200   1626848      2.47      7648   2735980  11859148     16.01    945192   3015284     20028
11:00:03     61767944  63616128    981120      1.49      7648   2754880  11137220     15.04    915536   2399188      4188
11:10:03     61862556  63702900    894176      1.36      7648   2747308  11025260     14.89    925372   2279068         0
11:20:03     61872684  63714484    882952      1.34      7648   2748788  11016896     14.87    927924   2279844         0
11:30:03     58305508  61577628   1874804      2.85      7648   5276428  12172268     16.43   1429888   5337516     58492
11:40:03     57976656  61490612   1779184      2.70      7648   5686004  12166740     16.43   1801400   5268476     51368
11:50:00     57702720  61385112   1753320      2.66      7648   5969336  12038992     16.25   2069776   5260776     51224
12:00:00     55847564  59955040   2786572      4.23      7648   6780304  13041736     17.61   2571516   6582200     66796
12:10:03     55552520  59719364   2629196      3.99      7648   7227376  12882832     17.39   2998324   6447184     68128
12:20:03     55907020  60163276   2184968      3.32      7648   7307164  12457596     16.82   3027800   6053332     62032
12:30:03     55200784  59687440   2657892      4.04      7648   7530040  12938772     17.47   3068752   6706968     63724
12:40:03     54830648  59466128   2827212      4.29      7648   7723292  13078288     17.66   3153496   6991212     49348
12:50:03     54771540  59572044   2591820      3.94      7648   8008060  12870848     17.38   3313932   6876356     89092
13:00:03     54692636  59587800   2412788      3.66      7648   8259492  12682264     17.12   3508396   6756404     99048
13:10:03     54025556  59064720   2847752      4.33      7648   8484700  13111368     17.70   3615164   7301200     58748
13:20:03     54191400  59361784   2376192      3.61      7648   8782652  12638620     17.06   3815340   6936244     94432
13:30:03     54325484  59513960   2186832      3.32      7648   8828776  12438336     16.79   3871348   6733536     59352
13:40:02     53758532  59113036   2499584      3.80      7648   9075556  12770044     17.24   3982756   7182092     91200
13:50:03     52994504  58588208   2993372      4.55      7648   9334628  13243072     17.88   4029592   7863608     67640

[Lars Aksel Opsahl]

When running with POSTGIS="3.6.0dev 3.5.0-48-g75d9b03d6" I got this error. I need to retest this.

>ERROR:  cache lookup failed for type 15857891
/var/log/postgresql/postgresql-Sun.log.1:       ' , using conn 'conn_3813f3afc65243dc84261ac1bfcdfca0_1', state  : 'XX000' message: 'cache lookup failed for type 15857891' detail : '' hint : '' context: 'PL/pgSQL assignment "result = (inputLineEdgeIds,failed_line,face_edges_to_remove_list,_table_input_order,_src_table_pk_column_value,new_line,selected_edge_face_id)"

Below is the log from sar I see no big changes in memory usage.

06:30:00    kbmemfree   kbavail kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit  kbactive   kbinact   kbdirty
09:20:03     46870604  63678672    964888      1.47      7648  17284504  11140752     15.04   4167800  13616408        12
09:30:02     54351468  61472872   1873832      2.85      7648   8881104  12203724     16.48   3478428   6845296     71228
09:40:03     54060728  61178664   1959712      2.98      7648   9061380  12277336     16.58   3931872   6653236     56692
09:50:03     54711104  61097120   1918060      2.91      7648   8439672  12248920     16.54   4266792   5664344     54144
10:00:01     52944848  59890084   2431672      3.69      7648   9684532  12721888     17.18   4855788   6823912     70692
10:10:03     52787460  59454200   2868928      4.36      7648   9399136  13156584     17.76   5115464   6714596     69132
10:20:03     53129960  59953140   2368436      3.60      7648   9543828  12665064     17.10   5148836   6324272     65856
10:30:01     52310940  59273088   3049248      4.63      7648   9673892  13336024     18.01   5191960   7076380     60728
10:40:03     52596996  59667752   2515028      3.82      7648   9914588  12799180     17.28   5372904   6593484     86952
10:50:03     52426232  59606796   2446672      3.72      7648  10147756  12736424     17.20   5531712   6614168     45972
11:00:03     51883252  59239916   2616252      3.97      7648  10512920  12925556     17.45   5723168   6960980    105172
11:10:03     52036312  59419436   2306020      3.50      7648  10662740  12610384     17.03   5928948   6596164     88712
11:20:03     51280996  58913852   2694360      4.09      7648  11020636  12973156     17.52   6060100   7206428     85928
11:30:03     51025856  58745296   2818520      4.28      7648  11143464  13103884     17.69   6140960   7357568     54288
11:40:01     50466424  58236232   3224400      4.90      7648  11288644  13495192     18.22   6274660   7775044     93132
11:50:03     46446228  55125096   5304776      8.06      7648  13217260  15581204     21.04   6904708  11144952    120416
12:00:00     45610136  54602196   5497288      8.35      7648  13859348  15707692     21.21   7494788  11377356     44188
12:10:03     44237648  53522212   6236368      9.47      7648  14490436  16397884     22.14   7958108  12270112     74448
12:20:03     43445796  53091948   6220168      9.45      7648  15294224  16391312     22.13   8394292  12622436     60064
12:30:03     43492280  53229880   5805956      8.82      7648  15660896  15975884     21.57   8675324  12300800     24412
12:40:03     43594052  53222528   5817408      8.84      7648  15549396  16049928     21.67   8717608  12155880     22116
12:50:03     39943508  50632364   7559464     11.48      7648  17449480  17776788     24.00   9077192  15427732     96372
13:00:04     39013616  50327196   7371292     11.20      7648  18562680  17549968     23.69   9853320  15544592     97144

[Lars Aksel Opsahl]

Memory usage before and after memory clean

[Lars Aksel Opsahl]

I will now rerun the with test cache error with code from commit [6366bd678d2121ad2c3609f39db37b249030f04a/git]

[Lars Aksel Opsahl]

Replying to Lars Aksel Opsahl:

When running with POSTGIS="3.6.0dev 3.5.0-48-g75d9b03d6" I got this error. I need to retest this.

>ERROR:  cache lookup failed for type 15857891
/var/log/postgresql/postgresql-Sun.log.1:       ' , using conn 'conn_3813f3afc65243dc84261ac1bfcdfca0_1', state  : 'XX000' message: 'cache lookup failed for type 15857891' detail : '' hint : '' context: 'PL/pgSQL assignment "result = (inputLineEdgeIds,failed_line,face_edges_to_remove_list,_table_input_order,_src_table_pk_column_value,new_line,selected_edge_face_id)"

I was not able to reproduce this error.