Database query running via PSQL fails with error "out of memory for query result"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Database query running from PSQL fails, producing the error message out of memory for query result.

Error Message:

xx=# select * from xx where xxx=xxx;
out of memory for query result

 

Environment

Cause

The PSQL application runs out of memory when the query results are displayed. By default, the PSQL tool will display all rows associated with the query result.

Resolution

Set the FETCH_COUNT > 0 to specify the number of rows of data that need to be fetched and displayed.

This parameter will reduce the amount of data that is being fetched and displayed, rather than displaying the entire result data set after it is fetched. Therefore, only a limited amount of memory is used. 

\set FETCH_COUNT 1000
< run the query >