Subject: Memory allocation error

From: Shaozhong SHI <[email-masked]>
Date: Fri, 14 Jul 2023 07:38:34 +0100

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

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 14 Jul 2023 09:13:23 +0200

Hi

> 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

---

From: Shaozhong SHI <[email-masked]>
Date: Fri, 14 Jul 2023 08:36:46 +0100

> breakpoint to this error message, and when you get this error, send stack

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>

Regards,

David

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 14 Jul 2023 09:49:51 +0200

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

---

From: Tom Lane <[email-masked]>
Date: Fri, 14 Jul 2023 07:13:00 -0400

> 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

diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index 4359dbd83d..7828a6264b 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -5317,6 +5317,12 @@ accumArrayResult(ArrayBuildState *astate,
 	if (astate->nelems >= astate->alen)
 	{
 		astate->alen *= 2;
+		/* give an array-related error if we go past MaxAllocSize */
+		if (!AllocSizeIsValid(astate->alen * sizeof(Datum)))
+			ereport(ERROR,
+					(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+					 errmsg("array size exceeds the maximum allowed (%d)",
+							(int) MaxAllocSize)));
 		astate->dvalues = (Datum *)
 			repalloc(astate->dvalues, astate->alen * sizeof(Datum));
 		astate->dnulls = (bool *)

---

From: Pavel Stehule <[email-masked]>
Date: Fri, 14 Jul 2023 13:54:17 +0200

> ERROR:  array size exceeds the maximum allowed (1073741823)

+1

it is significantly better

Regards

Pavel

---

From: Shaozhong SHI <[email-masked]>
Date: Fri, 14 Jul 2023 23:44:08 +0100

Well.  I think that I made significant advancement by thinking of how to
increase the functionality.
Adding a height of preceding must greater than the height of succeeding and
reduce the likelihood of infinity in recursion.
Regards,  David