[Solved] org.postgresql.util.PSQLException: Invalid Memory Alloc Request size due to Field size Limit – 1GB in postgreSQL – Sql

The Problem:

An organization is encountering the "Invalid memory alloc request size due to Field size Limit – 1GB in PostgreSQL" error when attempting to retrieve specific rows from a table containing a "bytea" data type column with large data volumes (over 700MB). The error occurs during data fetching, affecting both manual retrieval via tools like PgAdmin or DBeaver and programmatic access through Java. Standard solutions like adjusting work_mem and shared_buffers have not resolved the issue. The table holds over 70k rows, making schema changes challenging due to existing data. How can the organization overcome this limitation to successfully retrieve the desired data?

The Solutions:

Solution 1: Utilize Smaller Data Fetch Size

To resolve the “Invalid Memory Alloc Request size due to Field size Limit – 1GB” error while retrieving large bytea data from PostgreSQL, you can implement a strategy of reading data in smaller chunks. This approach helps avoid memory issues during processing.

Here’s a code snippet in Java demonstrating this technique:

try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
    preparedStatement.setString(1, "name_value");
    preparedStatement.setFetchSize(100); // Set data fetch size here

    try (ResultSet resultSet = preparedStatement.executeQuery()) {
        while (resultSet.next()) {
            byte[] dataBytes = resultSet.getBytes("data");
            //process or concatenate data here
        }
    }
} catch (SQLException e) {
    e.printStackTrace();
}

In this code:

• A PreparedStatement is created, and the input parameter name_value is set.
• The setFetchSize() method is used to explicitly specify the number of rows to fetch from the server in one go. This value is set to 100 in the example, but you can adjust it based on your specific requirements.
• The executeQuery() method is called to execute the query and retrieve the result set.
• The while loop iterates through the result set, fetching data in chunks of the specified size. The getBytes() method is used to retrieve the bytea data associated with the "data" column.
• You can then process the retrieved data within the loop or concatenate it with other chunks to reconstruct the entire large data object.

By fetching data in smaller chunks, you can avoid the memory allocation issues that arise when dealing with large data in a single operation. This approach may increase the execution time due to multiple round trips to the database, but it should successfully resolve the error you are encountering.