Pivotal Greenplum Database (GPDB) 4.3.5 and above
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"
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.
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