Pivotal Knowledge Base

Follow

ERROR: cannot change the value of "optimizer"

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.5 and above

Symptom

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"

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

Comments

Powered by Zendesk