Re: fetching bytea (blob) data of 850 MB from psql client failed

Hi John,
Thanks for those details.
The database table for bytea columns has binary data data and some are not
text. Hence, when it tried to fetch by changing the bytea_output to
'escape' it failed again with the below error -
VERROR; result of bytea output conversion is too large(File varlena.c; Line
415; Routine byteaout; ).
BTW, is there any plan to fix this issue in the PostgreSQL backend code in
near future?

Regards,
Jitesh

On Mon, Aug 22, 2022 at 4:22 PM John Naylor <john(dot)naylor(at)enterprisedb(dot)com>
wrote:

> On Mon, Aug 22, 2022 at 4:35 PM jitesh tiwari <jitesh120(at)gmail(dot)com> wrote:
> >
> > Hi all,
> >
> > I have a use case in postgresql where I have inserted 500 rows in a
> table with 2 columns as described below.
> > create table xyz (
> > id citext not null primary key,
> > col1 bytea
> > );
> >
> > The table has 500 rows and each row has around 850 MB of data. The bytea
> column data in the row has around 830 to 840 MB of data while citext column
> data has 10 to to 15 bytes approximately.
> > When I try to fetch all rows with a select query or try to fetch a
> single row with a select statement using the where clause for this
> table, the Database throws the below error -
> >
> > VERROR; invalid memory alloc request size 1764311653(File mcxt.c; Line
> 959;
> > Routine palloc; )
>
> The request size is about 2x bigger than 880MB, which is what we would
> expect when outputting using the default setting for "bytea_output",
> which is "hex". Hex uses two bytes of text to represent each byte:
>
> https://www.postgresql.org/docs/devel/datatype-binary.html
>
> Unfortunately this retrieval problem has been encountered before, but
> is not documented that I can see:
>
>
> https://www.postgresql.org/message-id/flat/13120.1518793109%40sss.pgh.pa.us#81d92e9e7e15975e606bdb349ec573d4
>
> If the data is largely printable ASCII, then one thing to try is
> setting "bytea_output" to "escape", if your driver and client can
> handle that:
>
>
> https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-BYTEA-OUTPUT
>
> If the data is binary and not printable ASCII, then there is no easy
> workaround. For this, possibly the "large object" facility could be
> useful, or storing the data in a regular file with the path stored in
> the database.
>
> --
> John Naylor
> EDB: http://www.enterprisedb.com
>