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
2
Answers
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.
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.