I can insert data into a bytea type column in a table, but I can't select it. It seems like a memory issue, how do I fix it?

code sample

INSERT INTO bytea_test(data) values (repeat('x', 1024 * 1024 * 1023)::bytea);
-- OK

select repeat('x', 1024 * 1024 * 1023)::bytea;
-- invalid memory alloc request size 2145386499

• container on k8s
• PostgreSQL 12.5 (Debian 12.5-1.pgdg100 1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

I tried editing around the system, but it didn't improve.

My current settings are:

max_connections=100
shared_buffers=6GB
effective_cache_size=4GB
maintenance_work_mem=64MB
checkpoint_completion_target=0.5
wal_buffers=16MB
default_statistics_target=100
random_page_cost=4
effective_io_concurrency=1
work_mem=3GB
min_wal_size=80MB
max_wal_size=6GB
max_worker_processes=8
max_parallel_workers_per_gather=2

CodePudding user response：

You are running out of memory. Changing PostgreSQL parameters won't help with that. You'll have to set the memory limits of the container wider if you want to run a statement like that.

CodePudding user response：

When formatted to return from a select, a bytea is converted to format which takes up twice as much space as it does when it is in storage. PostgreSQL can't deal with a single value as large as that.

In this case, you could work around it by doing set bytea_output=escape;, as that variable-width way of representing bytea would be more compact for this particular case.