The gpconfig not able to update the GUC due to database level settings overrided it

Products

• VMware Tanzu Greenplum

Issue/Introduction

The user might observe the below behavior while updating GUCs: the gpconfig failed to update the GUC
for example, when a user would like to set "optimizer" (a session-level GUC which does not need to restart DB) to "on", but it does not work:

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

# gpconfig -c optimizer -v on;
# gpstop -u

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

Environment

Product Version: 6.21

Resolution

This kind of behavior will occur if all of the below settings matches:

1. User set the GUC on the database level:

# alter database postgres set optimizer to off;

2 the default DB was set to the database in #1

# export PGDATABASE=postgres;

To confirm that, we can run either one of the below commands:

postgres=# \drds
        List of settings
 Role | Database |   Settings
------+----------+---------------
      | postgres | optimizer=off
(1 row)

-- or below query which do the same thing as above one:

SELECT rolname AS "Role", datname AS "Database",
pg_catalog.array_to_string(setconfig, E'\n') AS "Settings"
FROM pg_catalog.pg_db_role_setting s
LEFT JOIN pg_catalog.pg_database d ON d.oid = setdatabase
LEFT JOIN pg_catalog.pg_roles r ON r.oid = setrole
ORDER BY 1, 2;

 Role | Database |   Settings
------+----------+---------------
      | postgres | optimizer=off
(1 row)

To fix this, we can reset the GUC on the database level:

# alter database <DB> reset <GUC>;
-- for example:
# alter database postgres reset optimizer;

# gpconfig -s optimizer;
Values on all segments are consistent
GUC          : optimizer
Master  value: on
Segment value: on