Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qKDZW-0001KQ-83 for pgsql-sql@arkaria.postgresql.org; Fri, 14 Jul 2023 07:50:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qKDZV-0001Rt-2f for pgsql-sql@arkaria.postgresql.org; Fri, 14 Jul 2023 07:50:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qKDZU-0001Rk-Oj for pgsql-sql@lists.postgresql.org; Fri, 14 Jul 2023 07:50:36 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qKDZN-000Nip-FG for pgsql-sql@lists.postgresql.org; Fri, 14 Jul 2023 07:50:36 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-bcb6dbc477eso1445946276.1 for ; Fri, 14 Jul 2023 00:50:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689321028; x=1691913028; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=YfskdGM6NI5RUx88eNDY8u478pxiwXL59FWg3RM5Mbw=; b=anuFq0H+uonsp3cD/OFs2JqfYHGzF1sMGZfiNfJx7aH5Vb47MmDZZ0NI0rNyz54xk4 xvBhNOJBSZHxtcm52R0JQkM1F5MvQlkqb2m4Xo9ajEPidojObKIOHPd8Cmv4O5jsNCRp HeWOemuKJVlkJs0657sAkpvDvZeAxFJFsv28fZJGLZYl+Ed7fGXne8HBztwVVZR4eULx OENY/BIWIcaIGzMcBOumIACsrNEGNdTZUCysm+JpFW/p5X1fkcc3eBLOhnq8L7YtNGKR 6s2Zx4GEMHEnbD4ZSeh3UEiFG6MkyqHvzE4gX5+F5Wbq1tAvLWfQ4Y1C48QuxlRB9o7e 9/nA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689321028; x=1691913028; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=YfskdGM6NI5RUx88eNDY8u478pxiwXL59FWg3RM5Mbw=; b=UKaFImPpKeru+MJKAfOL9gPZogNYoeEOgO+XYFThfozRFReuE7Hsn6mcJ1oFP9ycIe 1rsGFqG1PQhnw/rTC/PPApc/CKkdUtGqriCyfvHSoWgnKbRKeglrUMd6u11PkFvJjtAz WoaZoPylox81mJIMlxFBZpYL9nE+4A/tdrEVxN52OO/hvzo/CiWzdCN5u1Oh5huqxPUO ycoWb9FBNczK+LdJerfBUEWwTz0jBfjjUPgcBLiiI5gHG8A0222KGamt/ebJ0WaNWkh7 OO6FXFNDC0UK15o+MkgWjG8h48t1uadWgQR8mIj+76CW3czI+7WH7f9QdRhzERYLixM6 tNPw== X-Gm-Message-State: ABy/qLZTw5bLG3+DdbFBm7NF2XMb6Wd2AXRn/ECAuFi1zTIhIKu8ckUV R1mev1sNQBOvXwUFbtePy9Ugc2maNXRY5PQDzRM= X-Google-Smtp-Source: APBJJlGISvfY8IGMZRCAxXxNOYpj119EmKn5J2VKvEee8Ueyxz76HlrdbSmeemOsWpqmXFTAM1B1J8TywQXZ6aaLVNc= X-Received: by 2002:a81:7cc5:0:b0:56d:1521:4f6c with SMTP id x188-20020a817cc5000000b0056d15214f6cmr4077066ywc.16.1689321027943; Fri, 14 Jul 2023 00:50:27 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Fri, 14 Jul 2023 09:49:51 +0200 Message-ID: Subject: Re: Memory allocation error To: Shaozhong SHI Cc: pgsql-sql Content-Type: multipart/alternative; boundary="000000000000c840b406006db394" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000c840b406006db394 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable p=C3=A1 14. 7. 2023 v 9:36 odes=C3=ADlatel Shaozhong SHI napsal: > > > On Fri, 14 Jul 2023 at 08:14, Pavel Stehule > wrote: > >> Hi >> >> p=C3=A1 14. 7. 2023 v 8:38 odes=C3=ADlatel Shaozhong SHI >> 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 stac= k >> 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) > > > Network Walking in PostGIS =C2=B7 Paul Ramsey (cleverelephant.ca) > > 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 > --000000000000c840b406006db394 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
p=C3=A1 14. 7. 2023 v=C2=A09:36 odes= =C3=ADlatel Shaozhong SHI <shi= shaozhong@gmail.com> napsal:


On Fri, 14 Jul 2023 at 08:14, Pavel Stehu= le <pavel.s= tehule@gmail.com> wrote:
Hi

p=C3=A1 14. 7. 2023 v=C2=A08:38= odes=C3=ADlatel Shaozhong SHI <shishaozhong@gmail.com> napsal:
A function is being called in a loop.=C2=A0 Sometime, there is an error.<= div>
sqlstate: XX000
NOTICE:=C2=A0 message: in= valid memory alloc request size 1073741824

W= hat to do to resolve the issue?

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

Can you show source c= ode? Can you use gdb, attach to Postgres, place breakpoint to this error me= ssage, and when you get this error, send stack trace?

Regards

Pavel

It a recursive query,.

CR= EATE OR REPLACE FUNCTION public.downstream_start_end_ret3333(integer)
=
=C2=A0RETURNS record
=C2=A0LANGUAGE plpgsql
AS $fu= nction$

declare
ret int;
arr i= nt[];
rec last_arr_count;
last int;
max int;<= /div>
Begin
drop table if exists t;
create temp tab= le t (idlist int[]);
--select count(*) from t into max;
WITH RECURSIVE walk_network(id, startpoint, endpoint, name1_text, startnod= e, endnode) AS (
=C2=A0 SELECT id, startpoint, endpoint, name1_te= xt, startnode, endnode
=C2=A0 =C2=A0 FROM primarylink1
= =C2=A0 =C2=A0 WHERE id =3D $1
=C2=A0 UNION ALL
=C2=A0 S= ELECT n.id, n.startpoint, n.e= ndpoint, n.name1_text, n.startnode, n.endnode
=C2=A0 =C2=A0 FROM = primarylink1 n, walk_network w
=C2=A0 =C2=A0 WHERE w.endpoint=3Dn= .startpoint and w.startpoint !=3D n.endpoint and w.endnode =3Dn.startnode a= nd w.startnode !=3D n.endnode
)
insert into t SELECT ar= ray_unique_stable(array_agg(id)) as idlist FROM walk_network;
sel= ect idlist from t into rec.arr;
select rec.arr[array_upper(rec.ar= r, 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 best wa= y - do it all in one recursive query without any recursive function.
<= div>
This issue you can fix only by rewriting your code.


=C2=A0


Regards,

David=C2=A0=C2=A0
--000000000000c840b406006db394--