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 1qVd1I-000lfg-4h for pgsql-general@arkaria.postgresql.org; Mon, 14 Aug 2023 19:14:28 +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 1qVd1G-006vTY-LI for pgsql-general@arkaria.postgresql.org; Mon, 14 Aug 2023 19:14:26 +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 1qVd1G-006vTP-8t for pgsql-general@lists.postgresql.org; Mon, 14 Aug 2023 19:14:26 +0000 Received: from mail-yw1-x1135.google.com ([2607:f8b0:4864:20::1135]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qVd1B-0008yh-72 for pgsql-general@lists.postgresql.org; Mon, 14 Aug 2023 19:14:25 +0000 Received: by mail-yw1-x1135.google.com with SMTP id 00721157ae682-58c4f6115bdso6296637b3.1 for ; Mon, 14 Aug 2023 12:14:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692040459; x=1692645259; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=RETlQ92AdCZRDxt7R8ppejqgkPITOYyYAScT7erGaz0=; b=BuiAks6B7+xw0b87SvKKFHMZjnh2Flxg21O5X5YZ1wxULdY0svtELjRBQ51StnP9/j 9KuEP2THgw6/3Yv/0wTm1uD3Y94famm5Xf2xuDwbESTtKsuCdfX9nXeelr8T1hlIPSMa HqWgyU3NB8HzCDuK7ebU1iRXDdhdrj0VjKB0zESKfIOaZjh+Q7yrUPDGAJOOm/DFYYT0 xUhuS1Rew3OtvKwUa67wFIMX29X+9LjSAEsL+q42n729qxm2ZZ381ygHfY0htswSefaa cOJCTyjLXULxIkH2/DX0T4hfAAHUfhKOs1Kty1d01ldJbWSpDICj/B2pqif2pH6rYt6u HDAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692040459; x=1692645259; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=RETlQ92AdCZRDxt7R8ppejqgkPITOYyYAScT7erGaz0=; b=jqAxjUl563vzjqfyFSQZxlg0UmhJXh/VbqvFiIWr6HvB1tcLr4i2s+qu587S5rHX2o uOhut8s3VhcNr7tNZBFaqKsv/9khwJ7wtK5UGWwd5p6PlmiXdvSzZ3SpQsv3wfK/1aKi Xl4uO+GasJ/1nxJh86fsHIpsM+oB/S0MclBMvsJAK6rRdZ5iNDqW1aNfYa3a6jhogWPx 16Df9k9WGKj3Oy50ZrW4HK02dt+FjHasZmk/IyBC2cDKwZ+8fvEYhw3h1y0b7orhz+sc oNz6BswENriEX80CEJvd1DJiMZ5YCQEHSWT9FC36vTHRM3iClS0psw3Skg/glHO2159c 5Dtw== X-Gm-Message-State: AOJu0Yzdjn19SZOawNK6FVRL1dzbFwoVYCyZmkT3ezIf8Sm3ytyz4Csh 3bBWnSTtqg6F7l86i3nnAHJEv/E96No= X-Google-Smtp-Source: AGHT+IEYPTD1yyHIA/Z9XbWiboAQKXyDSWt/MjdrLrguMNlZkAohpry12FyvEJHu8lhYghWKPcx1CQ== X-Received: by 2002:a0d:f806:0:b0:584:1a4d:bbfa with SMTP id i6-20020a0df806000000b005841a4dbbfamr11897800ywf.29.1692040459070; Mon, 14 Aug 2023 12:14:19 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:9147:7a53:b761:df1b? ([2600:1700:bd2c:b810:9147:7a53:b761:df1b]) by smtp.googlemail.com with ESMTPSA id i6-20020a0dc606000000b0054f50f71834sm2926421ywd.124.2023.08.14.12.14.18 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 14 Aug 2023 12:14:18 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------ZFv3feav6cYi4n0AkR5xGtIF" Message-ID: Date: Mon, 14 Aug 2023 14:14:18 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: Fatal Error : Invalid Memory alloc request size 1236252631 Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <6BDA46BE-B926-4BF7-AEBF-C9153D59A51D@a-kretschmer.de> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ZFv3feav6cYi4n0AkR5xGtIF Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Did you /try/ changing bytea_output to hex? On 8/14/23 12:31, Sai Teja wrote: > I am just running select query to fetch the result > Query : select id, content_data, name from table_name > So here content_data is bytea content which is having more than 700 MB. > Even if I run this query in any DB client such as Pgadmin, dbeaver etc.. > I'm facing the same error. But this query is being called in java as well > So, I don't think java could be the issue as I can able to successfully > insert the data. But, only the problem is with fetching the data that too > only specific rows which are having huge volume of data. > > Thanks, > Sai > > On Mon, 14 Aug, 2023, 10:55 pm Rob Sargent, wrote: > > On 8/14/23 09:29, Sai Teja wrote: > > Could anyone please suggest any ideas to resolve this issue. > > > > I have increased the below parameters but still I'm getting same error. > > > > work_mem, shared_buffers > > > > Out of 70k rows in the table only for the few rows which is of large > > size (700MB) getting the issue. Am unable to fetch the data for that > > particular row. > > > > Would be appreciated if anyone share the insights. > > > > Thanks, > > Sai > > > > > Are you using java?  There's an upper limit on array size, hence also on > String length.  You'll likely need to process the output in chunks. > > -- Born in Arizona, moved to Babylonia. --------------ZFv3feav6cYi4n0AkR5xGtIF Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Did you try changing bytea_output to hex?

On 8/14/23 12:31, Sai Teja wrote:
I am just running select query to fetch the result
Query : select id, content_data, name from table_name
So here content_data is bytea content which is having more than 700 MB. Even if I run this query in any DB client such as Pgadmin, dbeaver etc.. I'm facing the same error. But this query is being called in java as well
So, I don't think java could be the issue as I can able to successfully insert the data. But, only the problem is with fetching the data that too only specific rows which are having huge volume of data.

Thanks,
Sai

On Mon, 14 Aug, 2023, 10:55 pm Rob Sargent, <robjsargent@gmail.com> wrote:
On 8/14/23 09:29, Sai Teja wrote:
> Could anyone please suggest any ideas to resolve this issue.
>
> I have increased the below parameters but still I'm getting same error.
>
> work_mem, shared_buffers
>
> Out of 70k rows in the table only for the few rows which is of large
> size (700MB) getting the issue. Am unable to fetch the data for that
> particular row.
>
> Would be appreciated if anyone share the insights.
>
> Thanks,
> Sai
>
>
Are you using java?  There's an upper limit on array size, hence also on
String length.  You'll likely need to process the output in chunks.



--
Born in Arizona, moved to Babylonia.
--------------ZFv3feav6cYi4n0AkR5xGtIF--