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

st 31. 8. 2022 v 15:20 odesílatel Tomas Vondra <
tomas(dot)vondra(at)enterprisedb(dot)com> napsal:

>
>
> On 8/31/22 09:27, John Naylor wrote:
> > On Mon, Aug 29, 2022 at 1:02 PM jitesh tiwari <jitesh120(at)gmail(dot)com>
> wrote:
> >>
> >> 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?
> >
> > Unfortunately, there is no plan as there is no consensus on how to
> > approach the issue. It seems the possible actions are (easiest to
> > hardest):
> >
> > 1. Document that bytea is unsafe -- you can insert anything you like,
> > but you may be unable to retrieve it again.
> > 2. Determine some maximum size that guarantees accessibility and
> > invent a GUC that by default prevents inserting larger values than
> > that.
> > 3. Fix the issue properly.
> >
> > #2 and #3 are challenging for reasons given in the thread I linked to
> above.
> >
>
> I haven't tried, but wouldn't it be enough to fetch the data in smaller
> chunks? The application would have to re-assemble that, of course.
>
>
yes - there are possible both direction conversions to LO. So anybody can
convert any bytea to temp LO, and then it can download to any client

Regards

Pavel

>
> regards
>
> --
> Tomas Vondra
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
>