Re: Memory allocation error

pá 14. 7. 2023 v 9:36 odesílatel Shaozhong SHI <shishaozhong(at)gmail(dot)com>
napsal:

>
>
> On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>> pá 14. 7. 2023 v 8:38 odesílatel Shaozhong SHI <shishaozhong(at)gmail(dot)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)
> <http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>
>
> Network Walking in PostGIS · Paul Ramsey (cleverelephant.ca)
> <http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>
>

The best way - do it all in one recursive query without any recursive
function.

This issue you can fix only by rewriting your code.

>
>
> Regards,
>
> David
>