Обсуждение: Fatal Error : Invalid Memory alloc request size 1236252631

On 14 August 2023 11:59:26 CEST, Sai Teja <saitejasaichintalapudi@gmail.com> wrote:
>Hi Team,
>
>We are trying to fetch the one row of data (bytea data) for one table in
>But getting the error stating that "Invalid Memory alloc request size
>1236252631"
>
>The row which we were trying to fetch have one bytea column which is more
>than 1GB
>
>Could anyone please help me to resolve this issue.
>

You can try to change the bytea_output. Possible values are hex and escape. With some luck it will work, but maybe your
applicationwill have problems with that. 

Andreas

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.

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.

Sai Teja <saitejasaichintalapudi@gmail.com> writes:
> I got to know the field size limit for the bytea datatype column is limited
> to 1 GB in postgreSQL. Then how can we increase this?

You can't.  That limit is wired-in in many ways.  Think about how to
split your data across multiple table rows.

            regards, tom lane

 
Even I used postgreSQL Large Objects by referring this link to store and retrieve large files (As bytea not working)
https://www.postgresql.org/docs/current/largeobjects.html
 
But even now I am unable to fetch the data at once from large objects
 
select lo_get(oid);
 
Here I'm getting the same error message.
 
But if I use select data from pg_large_object where loid = 49374
Then I can fetch the data but in page wise (data splitting into rows of each size 2KB)
 
So, here how can I fetch the data at single step rather than page by page without any error.
 
And I'm just wondering how do many applications storing huge amount of data in GBs? I know that there is 1GB limit for
eachfield set by postgreSQL. If so, how to deal with these kind of situations? Would like to know about this to deal
withreal time scenarios. 



https://github.com/lzlabs/pg_dumpbinary/blob/master/README.md
might be of help

Karsten

On 8/17/23 07:35, Sai Teja wrote:
> Hi Team,
>
> Even I used postgreSQL Large Objects by referring this link to store 
> and retrieve large files (As bytea not working)
> https://www.postgresql.org/docs/current/largeobjects.html
>
> But even now I am unable to fetch the data at once from large objects
>
> select lo_get(oid);
>
> Here I'm getting the same error message.
>
> But if I use select data from pg_large_object where loid = 49374
> Then I can fetch the data but in page wise (data splitting into rows 
> of each size 2KB)
>
> So, here how can I fetch the data at single step rather than page by 
> page without any error.
>
> And I'm just wondering how do many applications storing huge amount of 
> data in GBs? I know that there is 1GB limit for each field set by 
> postgreSQL. If so, how to deal with these kind of situations? Would 
> like to know about this to deal with real time scenarios.
>
> We need to store large content (huge volume of data) and retrieve it. 
> Currently It is not happening due to limit of field size set by 
> postgreSQL.
>
> Would request to share your insights and suggestions on this to help 
> me for resolving this issue.
>
>
>
My first attempt at handling large payload was to use Java Selector 
directly in my app.  This worked but manually chunking the data was 
tricky.  I switched to using Tomcat and it handles large http(s) 
payloads seamlessly.