ERROR: cannot change the value of "optimizer"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

While setting "optimizer" to "on" at the session level, the below error has occurred:

gpadmin=#SET optimizer=on ;
ERROR:  cannot change the value of "optimizer"

 

Environment

Cause

From GPDB 4.3.5.x, there is a new Global User Configuration (GUC) "optimizer_control" that has been added to control the usage of the new optimizer. This GUC can only be set by the Superuser.

 

Resolution

If a non-Superuser needs to set "optimizer" at the session level, either of these options need to be executed by the Superuser: 

• For specific database, connect to the DB

  ALTER DATABASE <dbname> SET optimizer_control=on ;

Reconnect to take the changes into affect.

• For all databases:

  gpconfig -c optimizer_control -v on
  gpstop -u