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.
... 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
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
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.