GPSS Jobs Fail with “Out of Memory” After Upgrade to Greenplum 7.6.1 / GPSS 2.2.0

Products

• VMware Tanzu Data Intelligence
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire

Issue/Introduction

After upgrading to Greenplum Database 7.6.1 and Greenplum Stream Server (GPSS) 2.2.0, several GPSS ingestion jobs began failing with Out of Memory (OOM) errors on specific Greenplum segments.

Failed to execute batch: pq: Out of memory (segX sliceY <segment_host>:<port> pid=<pid>)

Environment

GPDB 7.6.1

Cause

GPORCA had a behavioral change for tables without statistics on 7.6.1 version:

- GPORCA now assumes default row count = 1000 when a table has no statistics collected.

- GPSS internally creates temporary tables when processing job data.

- These temporary tables do not contain statistics, leading GPORCA to incorrectly estimate the row counts.

- The underestimated row count causes GPORCA to generate execution plans requiring very high memory, ultimately leading to Segment Out-of-Memory (OOM) conditions during GPSS batch execution.

Resolution

Option 1: Disbale GPORCA for the GPSS User

ALTER USER <gpss_user> SET optimizer = off;

This forces Greenplum to use the legacy planner, which does not apply the new row-estimation behavior.

 

Option 2: Keep GPORCA enabled but adjust Default Row Estimation GUC:

ALTER USER <gpss_user> SET optimizer_default_rows_for_stats = 1;

Setting this value to 1 ensures GPORCA generates stable plans for tables without statistics.

 

Then stop and start GPSS jobs and monitor for memory issues/job failures.

 

Reverting the changes (if required):

To revert Option 1:

ALTER USER <gpss_user> RESET optimizer;

 

To revert Option 2:

ALTER USER <gpss_user> RESET optimizer_default_rows_for_stats;