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

On Mon, Aug 22, 2022 at 4:35 PM jitesh tiwari <jitesh120@gmail.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
MBof 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
whereclause  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