Hello!
Some time ago I've encountered a problem with the bytea type: we can't
SELECT
bytea strings whose textual representation is too big to fit into
StringInfoData.
And as a side effect, pg_dump refuses to dump tables with big bytea
strings.
It's a bug, it's pretty confusing, but it seems like there's no pretty
way
to fix it so far. Here's a link to a recent discussion on the issue:
https://www.postgresql.org/message-id/flat/c8bdf802d41ec37003ec3b726db79428@postgrespro.ru#c8bdf802d41ec37003ec3b726db79428@postgrespro.ru
Since it won't be fixed anytime soon, I thought it could be worth
documenting.
Attaching a patch for the documentation: I added some text to the
"Binary Data Types"
part where I tried to describe the issue and to explain how to deal with
it.
My patch in plain text (for convenience):
It is not recommended to use bytea strings whose textual representation
exceeds 1GB, as it may not be possible to SELECT them due to output size
limitations. Consequently, a table containing such big strings cannot be
properly processed by pg_dump, as pg_dump will try to SELECT these
values from the
table and fail. The exact size limit advised for bytea strings depends
on their
content, the external format and encoding that you are using, the
context in
which they will be selected. The general rule is that when you use
SELECT,
the returned tuple should not exceed 1GB. Although even if SELECT does
not
work, you can still retrieve big bytea strings using COPY in binary
format.
--
Anna Akenteva
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company