[PostGIS] #5955: ST_GetFaceGeometry eats our ram
PostGIS
trac at osgeo.org
Fri Jul 25 11:10:03 PDT 2025
#5955: ST_GetFaceGeometry eats our ram
-----------------------+---------------------------
Reporter: latot | Owner: strk
Type: defect | Status: new
Priority: high | Milestone: PostGIS 3.5.4
Component: topology | Version: 3.5.x
Resolution: | Keywords:
-----------------------+---------------------------
Description changed by latot:
Old description:
> Hi! we know from some time in some circunstances there was something off,
> what was missing is a reprex to find this.
>
> Here is! how to cause the whole session use all the ram:
>
> {{{
> SELECT
> topology.createtopology (
> 'overflow',
> 0,
> 0
> )
> ;
>
> SELECT TopoGeo_AddPolygon(
> 'overflow',
> 'POLYGON((0 0, 0 1, 1 1, 0 0))'::geometry,
> 0
> );
>
> DO $$
> DECLARE
> face GEOMETRY;
> BEGIN
> LOOP
> face := (topology.ST_GetFaceGeometry('overflow', 1));
> RAISE NOTICE 'run';
> END LOOP;
>
> END;
> $$;
> }}}
>
> With grayshde we did some tests, this is the most simple scenario, the
> ram will slowly increase, never being free....
>
> There is other circunstances, in some queries even running this function
> will not trigger this behavior, something closer that still would be good
> to keep an eye would be this scenario:
>
> ```
>
> DO $$
> DECLARE
> geom GEOMETRY;
> face GEOMETRY;
> inter GEOMETRY;
> BEGIN
> geom := 'SRID=0;POLYGON EMPTY'::GEOMETRY;
> LOOP
> face := (topology.ST_GetFaceGeometry('overflow', 1));
> inter := ST_Intersection(geom, face);
> RAISE NOTICE 'run';
> END LOOP;
>
> END;
> $$;
> ```
>
> There is some very weird things with this, I hope with this simple cases
> we can finally start looking what happens behind.
>
> I have other complex queries that also needs some data to be executed,
> where any change causes to this do not happen.
>
> For the record:
>
> {{{
>
> CREATE OR REPLACE FUNCTION dumppolygons (in_geom geometry) returns TABLE
> (path INT, geom geometry) language plpgsql AS $$
> BEGIN
> IF GeometryType(in_geom) <> 'MULTIPOLYGON' THEN
> RAISE EXCEPTION 'geom is not a multipolygon';
> END IF;
> RETURN QUERY SELECT x.path[1], x.geom
> FROM (SELECT (ST_Dump(in_geom)).*) x;
>
> IF NOT FOUND THEN
> RAISE EXCEPTION 'No geometries!';
> END IF;
>
> RETURN;
>
> END;
> $$
> ;
>
> DO $$
> DECLARE
> a INT;
> BEGIN
> LOOP
> a := (
> WITH faces AS (
> SELECT
> x.face,
> topology.ST_GetFaceGeometry('manzana_urbana_topo',
> x.face[1]) geom
> FROM (
> SELECT
> topology.GetTopoGeomElements('(1,1,190137,3)'::TopoGeometry) face
> ) x
> ),
> inter AS (
> SELECT dp.path, faces.face, ST_Intersection(faces.geom,
> dp.geom) area
> FROM faces,
> dumppolygons('0106000020CF7F0000010000000103000000010000006B000000FA6663C5F544074129552AB101C65541E0EACAA499440741B19DCCC6F8C555410002D17D984407415C809DC5F8C55541C67B98A1864407419BF930B3F8C55541160E4B46834407414977B3B4F8C55541765368D976440741EAF04DBAF8C55541A0E2722F064307419BCCB019FCC5554110C822F8E2420741A497296CFCC55541229E695EE242074104C6916DFCC5554118BCB5EBCC4207411FBBCB9FFCC5554100A262ABC84207414A25C0A9FCC55541F86B58D5B942074103273C1EFCC555411EABEF55BF42074116130E11FCC55541A2EAFAB5D5420741E01272DBFBC55541EAD2FE14794407419FBBA5EEF7C555417CD5DBA190440741AED038B6F7C55541B4D033C58E44074106CDEF75F7C55541C8B047A5354407410270E45EEEC555416EEE66052C44074153ECEC23EEC555416E999EBA2A440741360C9022EEC55541D6E6545122440741DA72B219EEC55541B2E605B917440741469B2E24EEC55541F47B31850A44074166EE543DEEC55541E68AB7BCE7420741B9DDD2FBF0C555419A124A9CD8420741A0BC6020F1C555410607935BC94207419B573845F1C5554106E2341EC9420741E9D2CC45F1C55541CA6E09CCB8420741FD846230F0C555415C9991FCB7420741B450A51FF0C555418C6A82A2BF4207419E4CC00CF0C5554170757548C74207417A76D9F9EFC555414E2830BBD642074183C1A8D3EFC55541F480375611440741D7201DCAECC55541C0E8F2DD184407419DB65AA4ECC55541EE2F8A6323440741A82273D5EBC55541CA91E12ED7430741BD822195E3C555419654A3A7CE4307418668AE53E3C555418AF77310CD4307415618B531E3C55541406DFE4CC04307419E576B42E3C5554102F8D068B5430741809AB050E3C5554170B710A35042074131936B66E6C5554142B9584D3C420741E7A16D93E6C555418E163FDB334207418A254F47E6C55541ACFB1CE811420741AD748ED5E5C5554102E60E8342420741B29AB362E5C555414E9B571CA8430741F814A815E2C555413C9B70E9B2430741F00DBBD4E1C555416E4F0039B8430741172B5C8EE1C555413EF60CBFB84307413CADD07FE1C55541481C7C71BB430741EDA1EA34E1C5554144AA1DBFBB430741917F13DFE0C555412CEDA1A4B943074129214389E0C555418664C9A376430741BE5CB0FBD8C55541BC24EDF871430741F27D10B9D8C55541482429836E430741C9086FA4D8C5554178EAEA3D6B430741FD8FF190D8C555415E28FC50644307412CA9007CD8C555411C91E638614307418041A872D8C555411471D734584307410066A26CD8C55541EE9AE3A24B430741C1C6227DD8C555415EF74BE27B4107411C778A0ADDC555413008F15A5F41074153543A52DDC55541D25E1E9259410741630327F8DEC55541F4DCB8B75D4107412AE0654EE1C55541686F546565410741A25E92CAE1C55541220BAD2095410741D5BB5EA8E4C5554130E332A09E410741E87AAF97E5C55541A6EAC7C49D4107414857CFC0E5C5554136720A5398410741A56D05C6E6C55541D8306F268B41074135F7270FE8C555418237545D8F41074134E91920E8C55541BC03AF7E9041074132BDA624E8C55541C06B15FB76410741FDE7F77CEAC555418CA313CA7141074197D01EF7EAC55541D0B811996C41074108B94571EBC55541DCB4260458410741EFBD8F55EDC5554170A270F3574107411EA51757EDC55541561A085D4A41074156C4C896EEC55541582397D425410741967462F2F1C55541BE053044E14007417697A93FF8C55541CA4BB229DA400741AC2BCDE6F8C5554112F7CEA7D24007415B2FDCB9F9C55541B23A261AD44007419DA1ED55FAC55541C62CFA20D9400741471182A7FAC55541940EE555E140074139EA28DFFAC5554154C5E898E640074105868CFCFAC55541EE257A0FF14007410BC6FE36FBC5554150586B9CE5410741F7278C16FFC555413844139E424207410C85A58F00C65541DC051B615E4207410F86340001C655412A38B895C94207417C3FD42404C6554166BC49A3F542074154A0F73D05C65541B8692E6B22430741AAF5C15B06C655418EF13D3B67430741375E521C07C655415AFD67488C43074160DC824B07C6554136822F222F440741BD84E5D607C65541B6912F2A5944074169175BA107C65541B22F096585440741557F8C2907C6554192D4400898440741B88325FA06C6554114003F6EE4440741154652AB05C6554104006B58EE4407413EE0D45905C65541E6425B81054507411F4D7F9B04C655419CAB73950745074165F0962D04C6554138B4A8E8FF4407415D8BC2D702C65541E4B4EE88FF4407419FBE17C702C655418ACF1BFBF94407416160771902C65541FA6663C5F544074129552AB101C65541'::GEOMETRY)
> dp
> )
> SELECT COUNT(1) counter
> FROM (
> SELECT
> FROM inter
> )
> );
>
> RAISE NOTICE 'run';
>
> END LOOP;
>
> END;
>
> $$;
> }}}
>
> With this data:
> https://drive.google.com/file/d/14oF1eBqTVm01ETuXIfDugnK62g1NYxvB/view?usp=sharing
>
> What is weird here is... if you replace the function DumpPolygons with
> ST_Dump stops working, any change I tested there also make the reprex do
> not works.
>
> So now we have the simple example, and the complex one!
New description:
Hi! we know from some time in some circunstances there was something off,
what was missing is a reprex to find this.
Here is! how to cause the whole session use all the ram:
{{{
SELECT
topology.createtopology (
'overflow',
0,
0
)
;
SELECT TopoGeo_AddPolygon(
'overflow',
'POLYGON((0 0, 0 1, 1 1, 0 0))'::geometry,
0
);
DO $$
DECLARE
face GEOMETRY;
BEGIN
LOOP
face := (topology.ST_GetFaceGeometry('overflow', 1));
RAISE NOTICE 'run';
END LOOP;
END;
$$;
}}}
With grayshde we did some tests, this is the most simple scenario, the ram
will slowly increase, never being free....
There is other circunstances, in some queries even running this function
will not trigger this behavior, something closer that still would be good
to keep an eye would be this scenario:
{{{
DO $$
DECLARE
geom GEOMETRY;
face GEOMETRY;
inter GEOMETRY;
BEGIN
geom := 'SRID=0;POLYGON EMPTY'::GEOMETRY;
LOOP
face := (topology.ST_GetFaceGeometry('overflow', 1));
inter := ST_Intersection(geom, face);
RAISE NOTICE 'run';
END LOOP;
END;
$$;
}}}
There is some very weird things with this, I hope with this simple cases
we can finally start looking what happens behind.
I have other complex queries that also needs some data to be executed,
where any change causes to this do not happen.
For the record:
{{{
CREATE OR REPLACE FUNCTION dumppolygons (in_geom geometry) returns TABLE
(path INT, geom geometry) language plpgsql AS $$
BEGIN
IF GeometryType(in_geom) <> 'MULTIPOLYGON' THEN
RAISE EXCEPTION 'geom is not a multipolygon';
END IF;
RETURN QUERY SELECT x.path[1], x.geom
FROM (SELECT (ST_Dump(in_geom)).*) x;
IF NOT FOUND THEN
RAISE EXCEPTION 'No geometries!';
END IF;
RETURN;
END;
$$
;
DO $$
DECLARE
a INT;
BEGIN
LOOP
a := (
WITH faces AS (
SELECT
x.face,
topology.ST_GetFaceGeometry('manzana_urbana_topo',
x.face[1]) geom
FROM (
SELECT
topology.GetTopoGeomElements('(1,1,190137,3)'::TopoGeometry) face
) x
),
inter AS (
SELECT dp.path, faces.face, ST_Intersection(faces.geom,
dp.geom) area
FROM faces,
dumppolygons('0106000020CF7F0000010000000103000000010000006B000000FA6663C5F544074129552AB101C65541E0EACAA499440741B19DCCC6F8C555410002D17D984407415C809DC5F8C55541C67B98A1864407419BF930B3F8C55541160E4B46834407414977B3B4F8C55541765368D976440741EAF04DBAF8C55541A0E2722F064307419BCCB019FCC5554110C822F8E2420741A497296CFCC55541229E695EE242074104C6916DFCC5554118BCB5EBCC4207411FBBCB9FFCC5554100A262ABC84207414A25C0A9FCC55541F86B58D5B942074103273C1EFCC555411EABEF55BF42074116130E11FCC55541A2EAFAB5D5420741E01272DBFBC55541EAD2FE14794407419FBBA5EEF7C555417CD5DBA190440741AED038B6F7C55541B4D033C58E44074106CDEF75F7C55541C8B047A5354407410270E45EEEC555416EEE66052C44074153ECEC23EEC555416E999EBA2A440741360C9022EEC55541D6E6545122440741DA72B219EEC55541B2E605B917440741469B2E24EEC55541F47B31850A44074166EE543DEEC55541E68AB7BCE7420741B9DDD2FBF0C555419A124A9CD8420741A0BC6020F1C555410607935BC94207419B573845F1C5554106E2341EC9420741E9D2CC45F1C55541CA6E09CCB8420741FD846230F0C555415C9991FCB7420741B450A51FF0C555418C6A82A2BF4207419E4CC00CF0C5554170757548C74207417A76D9F9EFC555414E2830BBD642074183C1A8D3EFC55541F480375611440741D7201DCAECC55541C0E8F2DD184407419DB65AA4ECC55541EE2F8A6323440741A82273D5EBC55541CA91E12ED7430741BD822195E3C555419654A3A7CE4307418668AE53E3C555418AF77310CD4307415618B531E3C55541406DFE4CC04307419E576B42E3C5554102F8D068B5430741809AB050E3C5554170B710A35042074131936B66E6C5554142B9584D3C420741E7A16D93E6C555418E163FDB334207418A254F47E6C55541ACFB1CE811420741AD748ED5E5C5554102E60E8342420741B29AB362E5C555414E9B571CA8430741F814A815E2C555413C9B70E9B2430741F00DBBD4E1C555416E4F0039B8430741172B5C8EE1C555413EF60CBFB84307413CADD07FE1C55541481C7C71BB430741EDA1EA34E1C5554144AA1DBFBB430741917F13DFE0C555412CEDA1A4B943074129214389E0C555418664C9A376430741BE5CB0FBD8C55541BC24EDF871430741F27D10B9D8C55541482429836E430741C9086FA4D8C5554178EAEA3D6B430741FD8FF190D8C555415E28FC50644307412CA9007CD8C555411C91E638614307418041A872D8C555411471D734584307410066A26CD8C55541EE9AE3A24B430741C1C6227DD8C555415EF74BE27B4107411C778A0ADDC555413008F15A5F41074153543A52DDC55541D25E1E9259410741630327F8DEC55541F4DCB8B75D4107412AE0654EE1C55541686F546565410741A25E92CAE1C55541220BAD2095410741D5BB5EA8E4C5554130E332A09E410741E87AAF97E5C55541A6EAC7C49D4107414857CFC0E5C5554136720A5398410741A56D05C6E6C55541D8306F268B41074135F7270FE8C555418237545D8F41074134E91920E8C55541BC03AF7E9041074132BDA624E8C55541C06B15FB76410741FDE7F77CEAC555418CA313CA7141074197D01EF7EAC55541D0B811996C41074108B94571EBC55541DCB4260458410741EFBD8F55EDC5554170A270F3574107411EA51757EDC55541561A085D4A41074156C4C896EEC55541582397D425410741967462F2F1C55541BE053044E14007417697A93FF8C55541CA4BB229DA400741AC2BCDE6F8C5554112F7CEA7D24007415B2FDCB9F9C55541B23A261AD44007419DA1ED55FAC55541C62CFA20D9400741471182A7FAC55541940EE555E140074139EA28DFFAC5554154C5E898E640074105868CFCFAC55541EE257A0FF14007410BC6FE36FBC5554150586B9CE5410741F7278C16FFC555413844139E424207410C85A58F00C65541DC051B615E4207410F86340001C655412A38B895C94207417C3FD42404C6554166BC49A3F542074154A0F73D05C65541B8692E6B22430741AAF5C15B06C655418EF13D3B67430741375E521C07C655415AFD67488C43074160DC824B07C6554136822F222F440741BD84E5D607C65541B6912F2A5944074169175BA107C65541B22F096585440741557F8C2907C6554192D4400898440741B88325FA06C6554114003F6EE4440741154652AB05C6554104006B58EE4407413EE0D45905C65541E6425B81054507411F4D7F9B04C655419CAB73950745074165F0962D04C6554138B4A8E8FF4407415D8BC2D702C65541E4B4EE88FF4407419FBE17C702C655418ACF1BFBF94407416160771902C65541FA6663C5F544074129552AB101C65541'::GEOMETRY)
dp
)
SELECT COUNT(1) counter
FROM (
SELECT
FROM inter
)
);
RAISE NOTICE 'run';
END LOOP;
END;
$$;
}}}
With this data:
https://drive.google.com/file/d/14oF1eBqTVm01ETuXIfDugnK62g1NYxvB/view?usp=sharing
What is weird here is... if you replace the function DumpPolygons with
ST_Dump stops working, any change I tested there also make the reprex do
not works.
So now we have the simple example, and the complex one!
--
--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5955#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.
More information about the postgis-tickets
mailing list