Обсуждение: Memory allocation error

A function is being called in a loop.  Sometime, there is an error.

sqlstate: XX000

NOTICE:  message: invalid memory alloc request size 1073741824

What to do to resolve the issue?

Regards,

David

On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

pá 14. 7. 2023 v 8:38 odesílatel Shaozhong SHI <shishaozhong@gmail.com> napsal:

A function is being called in a loop.  Sometime, there is an error.

sqlstate: XX000

NOTICE:  message: invalid memory alloc request size 1073741824

What to do to resolve the issue?

It depends what you do. Postgres doesn't allow to allocate bigger blocks than 1GB. Maybe you create too big string or too big value of some other type. But it can be signal of some cache bloating.

Can you show source code? Can you use gdb, attach to Postgres, place breakpoint to this error message, and when you get this error, send stack trace?

Regards

Pavel

It a recursive query,.

CREATE OR REPLACE FUNCTION public.downstream_start_end_ret3333(integer)

 RETURNS record

 LANGUAGE plpgsql

AS $function$

declare

ret int;

arr int[];

rec last_arr_count;

last int;

max int;

Begin

drop table if exists t;

create temp table t (idlist int[]);

--select count(*) from t into max;

WITH RECURSIVE walk_network(id, startpoint, endpoint, name1_text, startnode, endnode) AS (

  SELECT id, startpoint, endpoint, name1_text, startnode, endnode

    FROM primarylink1

    WHERE id = $1

  UNION ALL

  SELECT n.id, n.startpoint, n.endpoint, n.name1_text, n.startnode, n.endnode

    FROM primarylink1 n, walk_network w

    WHERE w.endpoint=n.startpoint and w.startpoint != n.endpoint and w.endnode =n.startnode and w.startnode != n.endnode

)

insert into t SELECT array_unique_stable(array_agg(id)) as idlist FROM walk_network;

select idlist from t into rec.arr;

select rec.arr[array_upper(rec.arr, 1)] into rec.last;

---select count(distinct name) from t into rec.count;

drop table t;

return rec;

end;

$function$

Perhaps, it gets into a endless loop.

The original is here.  Network Walking in PostGIS · Paul Ramsey (cleverelephant.ca)

Network Walking in PostGIS · Paul Ramsey (cleverelephant.ca)

Regards,

David  

Pavel Stehule <pavel.stehule@gmail.com> writes:
> pá 14. 7. 2023 v 9:36 odesílatel Shaozhong SHI <shishaozhong@gmail.com>
> napsal:
>> On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>>> It depends what you do. Postgres doesn't allow to allocate bigger blocks
>>> than 1GB. Maybe you create too big string or too big value of some other
>>> type. But it can be signal of some cache bloating.

> The best way - do it all in one recursive query without any recursive
> function.
> This issue you can fix only by rewriting your code.

Yeah, but nonetheless this error message is pretty user-unfriendly.

The given example is too incomplete to run as-is, but I guessed that
maybe the array_agg() was accumulating too many values, and sure
enough it's possible to reproduce:

regression=# select array_agg(x::text) from generate_series(1,100000000) x;
ERROR:  invalid memory alloc request size 1073741824

We can do better than that.  The attached patch causes the error to be

regression=# select array_agg(x::text) from generate_series(1,100000000) x;
ERROR:  array size exceeds the maximum allowed (1073741823)

I'm not wedded to that wording, but it's an existing translatable string
that at least points you in the direction of "my array is too big".
(This is also what you get if the eventual array construction overruns
the 1G limit, cf construct_md_array().)

                        regards, tom lane