PostGIS with invalid memory alloc request size ERROR on polygon transform

Question

It seems I need to SET correct PostgreSQL memory parameters... How?

The complete error message is

ERROR: invalid memory alloc request size 1264582656
CONTEXT: SQL function "st_transform_resilient" statement 1

And the cited function was defined here, and was working with other tables.

I try the solution PostgreSQL Error invalid DSA memory alloc request size. Pg 17 configuration

SET max_parallel_workers_per_gather = 0;
SET work_mem TO '64MB';

No effect, no ERROR changes. As 1264582656 bytes = 1206 Mb, I try also, with no effect, SET work_mem TO '2048MB'.

Unwanted workaround: replacing ST_Transform_Resilient() by ST_Transform() it works.

Explain analyze

... Impossible. Adding a LIMIT 20 was possible:

explain analyze SELECT row_number() over() as gid, gid as old_gid, i, tipo_ada, geom
 FROM (
   SELECT t.gid, t.i, t.tipo_ada, ST_Intersection(c.geom,t.geom) as geom
   FROM (
     SELECT gid, i, tipo_ada,
            ST_Transform_Resilient( ST_MakeValid(geom), 952019, 0.02, 0.0000001 ) as geom 
     FROM dpvd24.t03dump_mancha_inund LIMIT 20
   ) t, dpvd24.vw_frafa_grid_cell_d1f9d c
   WHERE c.geom && t.geom AND ST_Intersects(c.geom,t.geom)
 ) t2
;

PS: 952019 is the SRID of an Albers equal-area projection.

 WindowAgg  (cost=0.00..19235.16 rows=1 width=59) (actual time=155.424..155.426 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..19222.65 rows=1 width=171) (actual time=155.422..155.423 rows=0 loops=1)
         Join Filter: ((export_br_grid_to_l8.geom && (st_transform_resilient(st_makevalid(t03dump_mancha_inund.geom), 952019, '0.02'::double precision, '1e-07'::double precision))) AND st_intersects(export_br_grid_to_l8.geom, (st_transform_resilient(st_makevalid(t03dump_mancha_inund.geom), 952019, '0.02'::double precision, '1e-07'::double precision))))
         Rows Removed by Join Filter: 20
         ->  Seq Scan on export_br_grid_to_l8  (cost=0.00..18713.29 rows=1 width=120) (actual time=0.153..42.111 rows=1 loops=1)
               Filter: ((intlevel = 80) AND (code_b16h = 'd1f9d'::text))
               Rows Removed by Filter: 528485
         ->  Limit  (cost=0.00..259.11 rows=20 width=51) (actual time=3.399..113.282 rows=20 loops=1)
               ->  Seq Scan on t03dump_mancha_inund  (cost=0.00..368337.56 rows=28431 width=51) (actual time=3.398..113.274 rows=20 loops=1)
 Planning Time: 0.578 ms
 Execution Time: 155.469 ms

Explain analyse the workaround

After replace by ST_Transform( ST_MakeValid(geom), 952019 ), and using a CREATE TABLE x AS before SELECT:

 WindowAgg  (cost=1801886.00..1801886.01 rows=1 width=59) (actual time=47800.944..47800.949 rows=2 loops=1)
   ->  Sort  (cost=1801886.00..1801886.00 rows=1 width=51) (actual time=47800.920..47800.922 rows=2 loops=1)
         Sort Key: t03dump_mancha_inund.gid, t03dump_mancha_inund.i
         Sort Method: quicksort  Memory: 29kB
         ->  Nested Loop  (cost=0.00..1801885.99 rows=1 width=51) (actual time=39342.519..47800.905 rows=2 loops=1)
               Join Filter: ((export_br_grid_to_l8.geom && st_transform(st_makevalid(t03dump_mancha_inund.geom), 952019)) AND st_intersects(export_br_grid_to_l8.geom, st_transform(st_makevalid(t03dump_mancha_inund.geom), 952019)))
               Rows Removed by Join Filter: 28429
               ->  Seq Scan on export_br_grid_to_l8  (cost=0.00..18713.29 rows=1 width=120) (actual time=190.274..226.549 rows=1 loops=1)
                     Filter: ((intlevel = 80) AND (code_b16h = 'd1f9d'::text))
                     Rows Removed by Filter: 528485
               ->  Seq Scan on t03dump_mancha_inund  (cost=0.00..5842.31 rows=28431 width=5160) (actual time=0.008..32.558 rows=28431 loops=1)
 Planning Time: 0.271 ms
 JIT:
   Functions: 10
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 2.840 ms, Inlining 24.424 ms, Optimization 116.665 ms, Emission 48.888 ms, Total 192.817 ms
 Execution Time: 47810.050 ms

select version();         --  PostgreSQL 16.8 (Ubuntu 16.8) linux-gnu, ..., 64-bit
select postgis_version(); --  3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Using other workaround: to create table with intermediary results. For big geometries: ST_Subdivide — Peter Krauss
– Peter Krauss, Commented Jun 14 at 13:40

Laurenz Albe · Accepted Answer · 2025-06-12 07:36:20Z

This error is caused by a hard limit for memory allocations withing PostgreSQL:

/*
 * MaxAllocSize, MaxAllocHugeSize
 *      Quasi-arbitrary limits on size of allocations.
 *
 * Note:
 *      There is no guarantee that smaller allocations will succeed, but
 *      larger requests will be summarily denied.
 *
 * palloc() enforces MaxAllocSize, chosen to correspond to the limiting size
 * of varlena objects under TOAST.  See VARSIZE_4B() and related macros in
 * postgres.h.  Many datatypes assume that any allocatable size can be
 * represented in a varlena header.  This limit also permits a caller to use
 * an "int" variable for an index into or length of an allocation.  Callers
 * careful to avoid these hazards can access the higher limit with
 * MemoryContextAllocHuge().  Both limits permit code to assume that it may
 * compute twice an allocation's size without overflow.
 */
#define MaxAllocSize    ((Size) 0x3fffffff) /* 1 gigabyte - 1 */

#define AllocSizeIsValid(size)  ((Size) (size) <= MaxAllocSize)

/* Must be less than SIZE_MAX */
#define MaxAllocHugeSize    (SIZE_MAX / 2)

#define InvalidAllocSize    SIZE_MAX

#define AllocHugeSizeIsValid(size)  ((Size) (size) <= MaxAllocHugeSize)

So any memory allocation beyond 1GB will fail, and there is no way to configure that. It seems lie your geometries are too large for the operation you want to perform.

Collectives™ on Stack Overflow

PostGIS with invalid memory alloc request size ERROR on polygon transform

Explain analyze

Explain analyse the workaround

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Explain analyze

Explain analyse the workaround

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related