Why PostgreSQL Reports ‘invalid memory alloc request size’ and How to Fix It

Background

During migration of an Oracle BLOB column to PostgreSQL BYTEA, the server raised the error invalid memory alloc request size 1293327093 (mcxt.c:1198). The reported size (~1.29 GB) suggested that a hard 1 GB field‑size limit was being exceeded.

PostgreSQL limits

PostgreSQL defines a maximum size for any varlena field (including BYTEA, TEXT, etc.) of 1 GB − 1 byte. The limit is enforced by the constant MaxAllocSize (0x3fffffff). With the default 8 KB block size the maximum table size is 32 TB. The limit can be reproduced with the following commands:

CREATE TABLE bytea_test(data BYTEA);
INSERT INTO bytea_test(data) VALUES (repeat('x',1024*1024*1023)::BYTEA);  -- 1023 MiB, succeeds
INSERT INTO bytea_test(data) VALUES (repeat('x',1024*1024*1024)::BYTEA);  -- 1 GiB, fails

Source‑code cause

Memory allocation in PostgreSQL goes through MemoryContextAlloc, which validates the requested size with AllocSizeIsValid. The relevant macros are:

#define MaxAllocSize ((Size) 0x3fffffff)   /* 1 GB‑1 */
#define AllocSizeIsValid(size) ((Size)(size) <= MaxAllocSize)

If a request is larger than MaxAllocSize, the server aborts with the “invalid memory alloc request size” error.

Out‑of‑memory in executor contexts

Large binary data loaded with functions such as pg_read_binary_file is first placed in the ExecutorState memory context. When the host machine lacks sufficient RAM, the same error appears. Example Bash script that creates a 512 MiB file and inserts it:

#!/bin/bash
size_in_mb=512
head -c $((size_in_mb*1024*1024)) /dev/zero | tr '\0' 'a' > largefile.txt
psql -U postgres -d postgres -c "INSERT INTO bytea_test(data) VALUES (pg_read_binary_file('$(pwd)/largefile.txt'));"

Result on a 1 GB VM:

ERROR:  out of memory
DETAIL:  Failed on request of size 536870924 in memory context "ExecutorState".

Lexer allocation behavior

The SQL lexer doubles its buffer when the current allocation is insufficient. The buffer growth is also bounded by MaxAllocSize. Consequently, a query whose literal length exceeds 536 MiB causes the lexer to request 1 GiB, which exceeds the limit and triggers the same error.

Hard‑coded varchar / char limit

Declared lengths for varchar(n) and char(n) cannot exceed 10 485 760 characters (10 MiB). This limit is defined in the source as:

#define MaxAttrSize  (10 * 1024 * 1024)

Creating a column with a larger length, e.g. varchar(10485761), yields:

ERROR:  length for type varchar cannot exceed 10485760

Other internal limits

Various internal thresholds (such as vacuum‑truncate thresholds) are also hard‑coded in the source code. These limits share the same 1 GB ceiling for memory allocations.

Work‑around

Because BYTEA values cannot exceed MaxAllocSize, split or truncate large BLOBs before migration. In practice, deleting rows larger than 512 MiB on the Oracle side ensures each BYTEA value stays below the PostgreSQL limit. In the reported case PostgreSQL attempted to allocate 1 293 327 093 bytes (646 663 545 × 2), which exceeds MaxAllocSize and caused the error.

References

PostgreSQL datatype character documentation: https://www.postgresql.org/docs/15/datatype-character.html

DBA StackExchange discussion: https://dba.stackexchange.com/questions/329893/error-message-from-server-error-invalid-memory-alloc-request-size