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 1qKHP1-000AFk-LK for pgsql-sql@arkaria.postgresql.org; Fri, 14 Jul 2023 11:56:03 +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 1qKHO1-001OA6-Hw for pgsql-sql@arkaria.postgresql.org; Fri, 14 Jul 2023 11:55:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qKHO1-001O9y-8e for pgsql-sql@lists.postgresql.org; Fri, 14 Jul 2023 11:55:01 +0000 Received: from mail-yw1-x1132.google.com ([2607:f8b0:4864:20::1132]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qKHNu-000Mqv-JX for pgsql-sql@lists.postgresql.org; Fri, 14 Jul 2023 11:55:00 +0000 Received: by mail-yw1-x1132.google.com with SMTP id 00721157ae682-5774335bb2aso18192467b3.0 for ; Fri, 14 Jul 2023 04:54:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689335694; x=1691927694; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=xRGAQ0IgstLC9TtJ2mdGmixwaRKlRrQLh6ea25VKTnM=; b=glaA7jDzoJWMMza5jbCOc/lTZsVstPRHj4zBVOoudz2bhVmE5AK6md3Y++m8yK5l/U EQbYo1+jVAjiiYClOCrapdaq68JK6hpj0kHJKEtTLX1PbICKvQA+sDXAus9YeqOtCcR3 iU8LwxhaZZ4xJJ/LjBF/8fBNI8Lcs1/4AcHWTuSmQNM0qGNl9knfUSNPEMDWV1wTWI9Y +sKNdgJ5xAenrU5pYSLPsRDxgvH+grgoBZDzmPKAESGGPE8udjRGXrSX4aYwE7cbfOiA dEfNuKbTnNCGGtae5LhBfkHYEyhJgb9yHedWL9L1CK/64N0LErH87cJPMzK+xa+PAXtC yqIA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689335694; x=1691927694; 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=xRGAQ0IgstLC9TtJ2mdGmixwaRKlRrQLh6ea25VKTnM=; b=OksOlEJvt5ssdfw+MQqtMe6/R27MVBLY/dDNdocrJtAAtjjE6rFGgc+sRXASEBgADg yxI3BkC9tkTZbsdNVShXa81CPPo2fDTJ+s+WhXCxskewwSMiGh2IdRbXZ5iv0gp8fkgy hYQs/JX7OedLM1hMe06yS4sW3/18y7RlZUY14AyqG64/pSDrargwfqitTHW5yGWIQc/G /sbBNH3DZGKX7CYWtQ4t7SvjaFFXmJQLLBRBoBkh7SoNx80kJhHTBLvhJEXgbLTYUKL1 khasOTYg6mD/xxb7L8W5MhCEvZTh/dDHPWPLlicf+qWog7B6mGB8U5tJWWXh21go87wd /luw== X-Gm-Message-State: ABy/qLZMLaETZVPSRY/WZOuitlWQThCJ0RPO+dRXiTa7/XZQATilVW9l GToBDx77N7YDvMVHXhdFbwp30Vgcu577ICuBn5A= X-Google-Smtp-Source: APBJJlHpgX6gMjM+aW6cKxevQx/faBCuFaEKQhPOsm11aolYSW9VC+0OBQDId66w3uDHW46L60APD9B0o4qNfIn7U68= X-Received: by 2002:a81:6306:0:b0:570:899f:3a52 with SMTP id x6-20020a816306000000b00570899f3a52mr4760920ywb.35.1689335693785; Fri, 14 Jul 2023 04:54:53 -0700 (PDT) MIME-Version: 1.0 References: <3288506.1689333180@sss.pgh.pa.us> In-Reply-To: <3288506.1689333180@sss.pgh.pa.us> From: Pavel Stehule Date: Fri, 14 Jul 2023 13:54:17 +0200 Message-ID: Subject: Re: Memory allocation error To: Tom Lane Cc: Shaozhong SHI , pgsql-sql Content-Type: multipart/alternative; boundary="000000000000ef3f8a0600711dc3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ef3f8a0600711dc3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable p=C3=A1 14. 7. 2023 v 13:13 odes=C3=ADlatel Tom Lane na= psal: > 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,100000000= ) 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,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 > > --000000000000ef3f8a0600711dc3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
p=C3=A1 14. 7. 2023 v=C2=A013:13 odes= =C3=ADlatel Tom Lane <tgl@sss.pgh.p= a.us> napsal:
Pavel Stehule <pavel.stehule@gmail.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

--000000000000ef3f8a0600711dc3--