Executing a Query in a Database Results in "FATAL: Invalid input for statement_mem. Must be less than max_statement_mem (xxx kB)"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Attempting to execute a query in the database results in the error message:

FATAL:  Invalid input for statement_mem. Must be less than max_statement_mem (1048576 kB).

Environment

Cause

As the error described in the issue related to the statement_mem parameter being set more than the max_statement_mem, which is incorrect:

flightdata=# show statement_mem;
 statement_mem
---------------
 1GB
(1 row)

flightdata=# show max_statement_mem;
 max_statement_mem
-------------------
 1024MB
(1 row)

The problem has occurred as the database that is being attempted to connect had its statement_mem to be set dynamically whenever any user connects to the database:

flightdata=# select datname,datconfig from pg_database;
  datname   |       datconfig
------------+-----------------------
 flightdata | {statement_mem=1GB}

 

Resolution

Change the value of the statement_mem to be lower than the max_statement_mem in the postgresql.conf

If the value of the statement_mem is being set dynamically as seen above then reset the value to have the system default:

alter database <database-name> reset statement_mem;

Or,

Alter the config to have the value lower than the max_statement_mem

alter database <database-name> set statement_mem='<value>';