Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qKRWV-000cNr-NA for pgsql-sql@arkaria.postgresql.org; Fri, 14 Jul 2023 22:44:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qKRWT-008u5i-HJ for pgsql-sql@arkaria.postgresql.org; Fri, 14 Jul 2023 22:44:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qKRWT-008u5Z-5D for pgsql-sql@lists.postgresql.org; Fri, 14 Jul 2023 22:44:25 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qKRWQ-000VdA-90 for pgsql-sql@lists.postgresql.org; Fri, 14 Jul 2023 22:44:24 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-9926623e367so323754566b.0 for ; Fri, 14 Jul 2023 15:44:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689374660; x=1691966660; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=UR4uxeNclONASqympmZM1NxrVTIV0X6DhQhsUaD1CzI=; b=Br/tb8TvPm9zytNGtpQ60uWmC62qRf2h10sOuwy790SJt/SqxGPAcx5aeEPzzDVPlB atB3wMmwDuZAImFuYHyh1/3gc+1ar3zlx8ZOoNrdqIWljrB8o0R69y2mMXhZl8zAFeuq 25zccqWvaqt78/eGB4d7mATAr2avND2Q8vdkvPQuTIbiIxKUhw6LzThvp8OWXt02Jp/I 1TXiydp4TlFY/0Omw8KHTpNqdONfxXZVQJypvvNC/oTidzfqelRQWRnydehka1/Ffm/A x1UC3SHf+0TU7gZAIJtlzB+SRiLS1te5//EBpU0UxuyzKH1uvU9/RNW6tczf7QIvAu9s XAFA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689374660; x=1691966660; 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=UR4uxeNclONASqympmZM1NxrVTIV0X6DhQhsUaD1CzI=; b=DN9wOvUbv8EZbuT802mXmOwdhyvhrE92u5Maw4oDXrvAGe1K34ju/kBLStZxxdhcIk c8UBGTAeJxni9HMqgxE69fNbp8eQyV/nZ+A1DF/ZAWU1GadJv0awKEqaIL0OBp+sw4UU HN11ywfh+FsDIfiDTrzrogwAQCIGBW9l5f/WQ0XKzxHXCfleaR3OFj3RzjZxxNm6en9K i9Ty8IOxVqwll6Thax/NrpZtWnTsSJmmHSjCk5hDDZrj0q14daV6EOeNAinXW6B2x20a rXdZ6Tq10a8Bk7Af+lK/q/khXyfiaiUotIO8tmH96wR8VyRweXd3ywkKXME9SC4q7owf 2MFg== X-Gm-Message-State: ABy/qLbhUT3ggHUiwrRDv25hi9GzO8E7Slly46LdxL9f6l7RB6bWlxvM km6oyS/7gLdaETCR43ta32uzg0KUASsVILhWU3M= X-Google-Smtp-Source: APBJJlFhl+rV6KPO/Seau72glgxPviDat3qlDpzycpiU3ZyKMgUCvcUzCBsY8uwfwzcRGigphheJ+AfC5NfK71WokGk= X-Received: by 2002:a17:906:100c:b0:994:4f10:fb39 with SMTP id 12-20020a170906100c00b009944f10fb39mr2056570ejm.16.1689374659810; Fri, 14 Jul 2023 15:44:19 -0700 (PDT) MIME-Version: 1.0 References: <3288506.1689333180@sss.pgh.pa.us> In-Reply-To: From: Shaozhong SHI Date: Fri, 14 Jul 2023 23:44:08 +0100 Message-ID: Subject: Re: Memory allocation error To: Pavel Stehule Cc: Tom Lane , pgsql-sql Content-Type: multipart/alternative; boundary="0000000000007d9a2906007a301c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007d9a2906007a301c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wrote= : > > > p=C3=A1 14. 7. 2023 v 13:13 odes=C3=ADlatel Tom Lane = napsal: > >> Pavel Stehule writes: >> > 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: >> >>> 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=3D# select array_agg(x::text) from generate_series(1,10000000= 0) >> x; >> ERROR: invalid memory alloc request size 1073741824 >> >> We can do better than that. The attached patch causes the error to be >> >> regression=3D# select array_agg(x::text) from generate_series(1,10000000= 0) >> 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 >> >> --0000000000007d9a2906007a301c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Well.=C2=A0 I think that I made significant advancement by= thinking of how to increase the functionality.
Adding a height of prec= eding must greater than the height of succeeding and reduce the likelihood = of infinity in recursion.
Regards,=C2=A0 David
On Fri, 1= 4 Jul 2023 at 12:54, Pavel Stehule <pavel.stehule@gmail.com> wrote:

<= br>
p=C3=A1= 14. 7. 2023 v=C2=A013:13 odes=C3=ADlatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmai= l.com> writes:
> p=C3=A1 14. 7. 2023 v 9:36 odes=C3=ADlatel Shaozhong SHI <shishaozhong@gmail.com= >
> napsal:
>> On Fri, 14 Jul 2023 at 08:14, Pavel Stehule <pavel.stehule@gmail.com><= br> >> 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<= br> > 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=3D# select array_agg(x::text) from generate_series(1,100000000) = x;
ERROR:=C2=A0 invalid memory alloc request size 1073741824

We can do better than that.=C2=A0 The attached patch causes the error to be=

regression=3D# select array_agg(x::text) from generate_series(1,100000000) = x;
ERROR:=C2=A0 array size exceeds the maximum allowed (1073741823)

+1

it is significantly b= etter

Regards

Pavel

I'm not wedded to that wording, but it's an existing translatable s= tring
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().)

=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 regards, tom lane

--0000000000007d9a2906007a301c--