MIME-Version: 1.0
References: 
 <CA+i5JwYtVS9z2E71PcNKAVPbOn4R2wuj-LqbJsYr_XOz73q7dQ@mail.gmail.com>
 <CAFj8pRCr=Qvsys2FKYscjWw5VR7wOYMFsHRoUwLsTZjgRV5yjQ@mail.gmail.com>
In-Reply-To: 
 <CAFj8pRCr=Qvsys2FKYscjWw5VR7wOYMFsHRoUwLsTZjgRV5yjQ@mail.gmail.com>
From: Shaozhong SHI <shishaozhong@gmail.com>
Date: Fri, 14 Jul 2023 08:36:46 +0100
Message-ID: 
 <CA+i5JwYy-=tbN8P59mxd3rEE6aWo9dy=5PrYM3Fq1f67eA1ytw@mail.gmail.com>
Subject: Re: Memory allocation error
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000083f99906006d8346"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2Bi5JwYy-%3DtbN8P59mxd3rEE6aWo9dy%3D5PrYM3Fq1f67eA1ytw%40mail.gmail.com>
Precedence: bulk

--00000000000083f99906006d8346
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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

> Hi
>
> p=C3=A1 14. 7. 2023 v 8:38 odes=C3=ADlatel Shaozhong SHI <shishaozhong@gm=
ail.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 =3D $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=3Dn.startpoint and w.startpoint !=3D n.endpoint and
w.endnode =3Dn.startnode and w.startnode !=3D 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 =C2=B7 Paul Ramsey
(cleverelephant.ca)
<http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>

Network Walking in PostGIS =C2=B7 Paul Ramsey (cleverelephant.ca)
<http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html>

Regards,

David

--00000000000083f99906006d8346
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div di=
r=3D"ltr"><br></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=
=3D"gmail_attr">On Fri, 14 Jul 2023 at 08:14, Pavel Stehule &lt;<a href=3D"=
mailto:pavel.stehule@gmail.com" target=3D"_blank">pavel.stehule@gmail.com</=
a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr"><div>Hi<br></div><br><div class=3D"gmail_quote"><div dir=3D"=
ltr" class=3D"gmail_attr">p=C3=A1 14. 7. 2023 v=C2=A08:38 odes=C3=ADlatel S=
haozhong SHI &lt;<a href=3D"mailto:shishaozhong@gmail.com" target=3D"_blank=
">shishaozhong@gmail.com</a>&gt; napsal:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">A function is be=
ing called in a loop.=C2=A0 Sometime, there is an error.<div><br></div><div=
><div>sqlstate: XX000</div><div>NOTICE:=C2=A0 message: invalid memory alloc=
 request size 1073741824</div></div><div><br></div><div>What to do to resol=
ve the issue?</div></div></div></blockquote><div><br></div>It depends what =
you do. Postgres doesn&#39;t allow to allocate bigger blocks than 1GB. Mayb=
e you create too big string or too big value of some other type. But it can=
 be signal of some cache bloating. <br></div><div class=3D"gmail_quote"><br=
></div><div class=3D"gmail_quote">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?</div><div class=3D"gmail_quote"><br></div=
><div class=3D"gmail_quote">Regards</div><div class=3D"gmail_quote"><br></d=
iv><div class=3D"gmail_quote">Pavel</div></div></blockquote><div><br></div>=
<div>It a recursive query,.</div><div><br></div><div>CREATE OR REPLACE FUNC=
TION public.downstream_start_end_ret3333(integer)</div><div>=C2=A0RETURNS r=
ecord</div><div>=C2=A0LANGUAGE plpgsql</div><div>AS $function$</div><div><b=
r></div><div>declare</div><div>ret int;</div><div>arr int[];</div><div>rec =
last_arr_count;</div><div>last int;</div><div>max int;</div><div>Begin</div=
><div>drop table if exists t;</div><div>create temp table t (idlist int[]);=
</div><div>--select count(*) from t into max;</div><div>WITH RECURSIVE walk=
_network(id, startpoint, endpoint, name1_text, startnode, endnode) AS (</di=
v><div>=C2=A0 SELECT id, startpoint, endpoint, name1_text, startnode, endno=
de</div><div>=C2=A0 =C2=A0 FROM primarylink1</div><div>=C2=A0 =C2=A0 WHERE =
id =3D $1</div><div>=C2=A0 UNION ALL</div><div>=C2=A0 SELECT <a href=3D"htt=
p://n.id" target=3D"_blank">n.id</a>, n.startpoint, n.endpoint, n.name1_tex=
t, n.startnode, n.endnode</div><div>=C2=A0 =C2=A0 FROM primarylink1 n, walk=
_network w</div><div>=C2=A0 =C2=A0 WHERE w.endpoint=3Dn.startpoint and w.st=
artpoint !=3D n.endpoint and w.endnode =3Dn.startnode and w.startnode !=3D =
n.endnode</div><div>)</div><div>insert into t SELECT array_unique_stable(ar=
ray_agg(id)) as idlist FROM walk_network;</div><div>select idlist from t in=
to rec.arr;</div><div>select rec.arr[array_upper(rec.arr, 1)] into rec.last=
;</div><div>---select count(distinct name) from t into rec.count;</div><div=
>drop table t;</div><div>return rec;</div><div><br></div><div>end;</div><di=
v>$function$</div><div><br></div><div>Perhaps, it gets into a endless loop.=
</div><div><br></div><div>The original is here.=C2=A0=C2=A0<a href=3D"http:=
//blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html">Network W=
alking in PostGIS =C2=B7 Paul Ramsey (cleverelephant.ca)</a></div><div><br>=
</div><div><a href=3D"http://blog.cleverelephant.ca/2010/07/network-walking=
-in-postgis.html">Network Walking in PostGIS =C2=B7 Paul Ramsey (cleverelep=
hant.ca)</a><br></div><div><br></div><div>Regards,</div><div><br></div><div=
>David=C2=A0=C2=A0</div></div></div></div></div></div>

--00000000000083f99906006d8346--