Pivotal Knowledge Base

Follow

Executing a query in a database results in "FATAL: Invalid input for statement_mem. Must be less than max_statement_mem (xxx kB)."

Environment

Product Version
GPDB All Version
OS All Version

Symptom

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).

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>';

Comments

Powered by Zendesk