Error message from server: ERROR: invalid memory alloc request size

Question

I am running with the below issue while taking pg_dump(PG14) of a table with bytea column.

pg_dump: error: Error message from server: ERROR: invalid memory alloc request size 1460154641 The command was: COPY TO stdout;

The table "ABC" in concern is just 60MB large(total size) and it has a bytea column.

But the error says is not able to allocate a request size of 1.3GB. What are we missing here? Could you please help? Thanks.

Update: I was able to take backup of the table using below command without error.

COPY public.abc TO stdout WITH (FORMAT binary);
--Successful execution

But below command fails:

COPY public.abc TO stdout ;
ERROR:  invalid memory alloc request size 1480703501

Even the select query return same error :

select * from ABC
ERROR: invalid memory alloc request size 1480703501

How did it even allow bytea column to be inserted with more than 1GB. Tha table is just 60Mb large and with just 1 row.

Answer 1

Your assessment that the table is only 60MB must be wrong. You don't tell us how you made that determination, so I don't where it went wrong. But it might be that small with compression.

The value's size is <1GB but the string representation of it is >1GB; so it can be stored, but it can't be escaped for normal output. Assuming bytea_output is set to 'hex' (the default) that means the raw value is about half of 1480703501 long.

The value could have been set using binary input, or it could have been constructed in place using bytea operations or functions(like bytea's ||), or for some values it could have been set using the escape format, where plain ASCII non-control characters can be input with only one character per byte.

People usually store very large files in the filesystem, and then store the file name in the database table. If it really needs to be in the database where it can be protected and/or replicated by WAL, then large objects are the only alternative (in PostgreSQL) that I know of.