public inbox for [email protected]help / color / mirror / Atom feed
Memory allocation error 7+ messages / 3 participants [nested] [flat]
* Memory allocation error @ 2023-07-14 06:38 Shaozhong SHI <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Shaozhong SHI @ 2023-07-14 06:38 UTC (permalink / raw) To: pgsql-sql <[email protected]> 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 ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Memory allocation error @ 2023-07-14 07:13 Pavel Stehule <[email protected]> parent: Shaozhong SHI <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Pavel Stehule @ 2023-07-14 07:13 UTC (permalink / raw) To: Shaozhong SHI <[email protected]>; +Cc: pgsql-sql <[email protected]> Hi pá 14. 7. 2023 v 8:38 odesílatel Shaozhong SHI <[email protected]> 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 > > Regards, > > David > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Memory allocation error @ 2023-07-14 07:36 Shaozhong SHI <[email protected]> parent: Pavel Stehule <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Shaozhong SHI @ 2023-07-14 07:36 UTC (permalink / raw) To: Pavel Stehule <[email protected]>; +Cc: pgsql-sql <[email protected]> On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <[email protected]> wrote: > Hi > > pá 14. 7. 2023 v 8:38 odesílatel Shaozhong SHI <[email protected]> > 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; Regards, David ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Memory allocation error @ 2023-07-14 07:49 Pavel Stehule <[email protected]> parent: Shaozhong SHI <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Pavel Stehule @ 2023-07-14 07:49 UTC (permalink / raw) To: Shaozhong SHI <[email protected]>; +Cc: pgsql-sql <[email protected]> pá 14. 7. 2023 v 9:36 odesílatel Shaozhong SHI <[email protected]> napsal: > > > On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <[email protected]> > wrote: > >> Hi >> >> pá 14. 7. 2023 v 8:38 odesílatel Shaozhong SHI <[email protected]> >> 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 > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Memory allocation error @ 2023-07-14 11:13 Tom Lane <[email protected]> parent: Pavel Stehule <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Tom Lane @ 2023-07-14 11:13 UTC (permalink / raw) To: Pavel Stehule <[email protected]>; +Cc: Shaozhong SHI <[email protected]>; pgsql-sql <[email protected]> Pavel Stehule <[email protected]> writes: > pá 14. 7. 2023 v 9:36 odesílatel Shaozhong SHI <[email protected]> > napsal: >> On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <[email protected]> >> 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 Attachments: [text/x-diff] better-error-for-array_agg-overflow.patch (732B, 2-better-error-for-array_agg-overflow.patch) download | inline diff: 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 *) ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Memory allocation error @ 2023-07-14 11:54 Pavel Stehule <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Pavel Stehule @ 2023-07-14 11:54 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Shaozhong SHI <[email protected]>; pgsql-sql <[email protected]> pá 14. 7. 2023 v 13:13 odesílatel Tom Lane <[email protected]> napsal: > Pavel Stehule <[email protected]> writes: > > pá 14. 7. 2023 v 9:36 odesílatel Shaozhong SHI <[email protected]> > > napsal: > >> On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <[email protected]> > >> 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) > +1 it is significantly better Regards Pavel > > 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 > > ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Memory allocation error @ 2023-07-14 22:44 Shaozhong SHI <[email protected]> parent: Pavel Stehule <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Shaozhong SHI @ 2023-07-14 22:44 UTC (permalink / raw) To: Pavel Stehule <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-sql <[email protected]> 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 On Fri, 14 Jul 2023 at 12:54, Pavel Stehule <[email protected]> wrote: > > > pá 14. 7. 2023 v 13:13 odesílatel Tom Lane <[email protected]> napsal: > >> Pavel Stehule <[email protected]> writes: >> > pá 14. 7. 2023 v 9:36 odesílatel Shaozhong SHI <[email protected]> >> > napsal: >> >> On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <[email protected]> >> >> 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) >> > > +1 > > it is significantly better > > Regards > > Pavel > >> >> 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 >> >> ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-07-14 22:44 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-07-14 06:38 Memory allocation error Shaozhong SHI <[email protected]> 2023-07-14 07:13 ` Pavel Stehule <[email protected]> 2023-07-14 07:36 ` Shaozhong SHI <[email protected]> 2023-07-14 07:49 ` Pavel Stehule <[email protected]> 2023-07-14 11:13 ` Tom Lane <[email protected]> 2023-07-14 11:54 ` Pavel Stehule <[email protected]> 2023-07-14 22:44 ` Shaozhong SHI <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox