ERROR: number of segment databases cannot be 0

Products

• VMware Tanzu Greenplum

Issue/Introduction

Some query may fail with error:  This error only show up in segment pg log and client application. From master pg log, it may not report this error.

ERROR: number of segment databases cannot be 0

Environment

Product Version: 6.x, 7.x

Cause

Normally this error means certain query or function is not supported to run from segment instance, but somehow this query or function is sent to segment to run directly (by query planner). There are different reason for this situation, one of the cause is due to "gp_enable_relsize_collection" set to "on" from both master and segment instances.

gpconfig -s gp_enable_relsize_collection
Values on all segments are consistent
GUC          : gp_enable_relsize_collection
Master  value: on
Segment value: on

 

In Greenplum Database (GPDB) v5, v6 and v7,  there is a parameter gp_enable_relsize_collection. The description in the documentation is:

Enables GPORCA and the Postgres-based planner to use the estimated size of a table (pg_relation_size function) if there are no statistics for the table. By default, GPORCA and the planner use a default value to estimate the number of rows if statistics are not available. The default behavior improves query optimization time and reduces resource queue usage in heavy workloads, but can lead to suboptimal plans.

This parameter is ignored for a root partitioned table. When GPORCA is enabled and the root partition does not have statistics, GPORCA always uses the default value. You can use ANALZYE ROOTPARTITION to collect statistics on the root partition. See ANALYZE.

Resolution

From R&D team, it's necessary only set this GUC to on from master / coordinator instance, from all segment instances, it's necessary to set GUC to off.  So the correct setting would be:

gpconfig -s gp_enable_relsize_collection
Values on all segments are consistent
GUC          : gp_enable_relsize_collection
Master  value: on
Segment value: off

To make this change, we can use command: (reload will make it effective).

gpconfig -c gp_enable_relsize_collection -v off -m on
gpstop -u
gpconfig -s gp_enable_relsize_collection