ERROR: number of segment databases cannot be 0

Products

• VMware Tanzu Greenplum

Issue/Introduction

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.

When this parameter is set to on, it will make all queries run in utility mode on the segments error out with:

ERROR: number of segment databases cannot be 0

The queries will run without errors when run normally from the master.

Environment

Product Version: 6.25

Resolution

When connected to a segment in utility mode, for example:

PGOPTIONS='-c gp_session_role=utility psql -h sdw1 -p 6001

Set the parameter to "off" before running any queries:

set gp_enable_relsize_collection=off;