default_transaction_isolation parameter issues

[bala-cg]

Page No : 1323

2 Issues 1) this parameter change takes effect only on DB restart and not on reload. 2) Post changing this parameter restart fails, manually we can comment this paramtere in postgresql.conf file and able to start the DB.

testing

1) [gpadmin@mdw_ipv4 pg_log]$ [gpadmin@mdw_ipv4 pg_log]$ gpconfig -s default_transaction_isolation Values on all segments are consistent GUC : default_transaction_isolation Master value: read committed Segment value: read committed [gpadmin@mdw_ipv4 pg_log]$ [gpadmin@mdw_ipv4 pg_log]$ [gpadmin@mdw_ipv4 pg_log]$ gpconfig -c default_transaction_isolation -v 'repeatable read'; 20210713:17:39:59:028446 gpconfig:mdw_ipv4:gpadmin-[INFO]:-completed successfully with parameters '-c default_transaction_isolation -v 'repeatable read'' [gpadmin@mdw_ipv4 pg_log]$ [gpadmin@mdw_ipv4 pg_log]$ [gpadmin@mdw_ipv4 pg_log]$ gpstop -u 20210713:17:40:09:028526 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -u 20210713:17:40:09:028526 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment... 20210713:17:40:09:028526 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20210713:17:40:09:028526 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master... 20210713:17:40:09:028526 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c' 20210713:17:40:09:028526 gpstop:mdw_ipv4:gpadmin-[INFO]:-Signalling all postmaster processes to reload [gpadmin@mdw_ipv4 pg_log]$ [gpadmin@mdw_ipv4 pg_log]$ gpconfig -s default_transaction_isolation Values on all segments are consistent GUC : default_transaction_isolation Master value: read committed Segment value: read committed [gpadmin@mdw_ipv4 pg_log]$

2) fails to start upon restarting DB.

[gpadmin@mdw_ipv4 pg_log]$ gpstop -arf 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -arf 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment... 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master... 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c' 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast' 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Master segment instance directory=/data1/gpdb/master/gpseg-1 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Terminating processes for segment /data1/gpdb/master/gpseg-1 20210713:17:41:10:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Stopping master standby host smdw_ipv4 mode=fast 20210713:17:41:11:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown standby process on smdw_ipv4 20210713:17:41:11:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Targeting dbid [2, 8, 3, 9, 4, 10, 5, 11, 6, 12, 7, 13] for shutdown 20210713:17:41:11:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel primary segment instance shutdown, please wait... 20210713:17:41:11:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed 20210713:17:41:11:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed 20210713:17:41:11:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait... 20210713:17:41:11:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:----------------------------------------------------- 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments stopped successfully = 12 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments with errors during stop = 0 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:----------------------------------------------------- 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown 12 of 12 segment instances 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Database successfully shutdown with no errors reported 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpmmon process 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpmmon process found 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover shared memory 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[INFO]:-Restarting System... 20210713:17:41:12:028670 gpstop:mdw_ipv4:gpadmin-[CRITICAL]:-Error occurred: non-zero rc: 2 Command was: '$GPHOME/bin/gpstart -a -d /data1/gpdb/master/gpseg-1' rc=2, stdout='20210713:17:41:12:028806 gpstart:mdw_ipv4:gpadmin-[INFO]:-Starting gpstart with args: -a -d /data1/gpdb/master/gpseg-1 20210713:17:41:12:028806 gpstart:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment... 20210713:17:41:12:028806 gpstart:mdw_ipv4:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c' 20210713:17:41:12:028806 gpstart:mdw_ipv4:gpadmin-[INFO]:-Greenplum Catalog Version: '301908232' 20210713:17:41:12:028806 gpstart:mdw_ipv4:gpadmin-[INFO]:-Starting Master instance in admin mode 20210713:17:41:12:028806 gpstart:mdw_ipv4:gpadmin-[CRITICAL]:-Failed to start Master instance in admin mode 20210713:17:41:12:028806 gpstart:mdw_ipv4:gpadmin-[CRITICAL]:-Error occurred: non-zero rc: 1 Command was: 'env GPSESSID=0000000000 GPERA=None $GPHOME/bin/pg_ctl -D /data1/gpdb/master/gpseg-1 -l /data1/gpdb/master/gpseg-1/pg_log/startup.log -w -t 600 -o " -p 5432 -c gp_role=utility " start' rc=1, stdout='waiting for server to start.... stopped waiting ', stderr='pg_ctl: could not start server Examine the log output. ' ', stderr='' [gpadmin@mdw_ipv4 pg_log]$ [gpadmin@mdw_ipv4 pg_log]$

[edespino]

@bala-cg It appears a certain format is required. The following sequences appear to work:

gpconfig -c default_transaction_isolation -v "'repeatable read'"
gpconfig -c default_transaction_isolation -v "'read committed'"

I noticed there is a default_transaction_isolation parameter in the default $MASTER_DATA_DIRECTORY/postgresql.conf that is commented out. The value is surrounded by single quotes. With the commands above I was able to set the allowable values for the parameter. Can you confirm? We should provide examples of setting this parameter in the docs.

[bala-cg]

Yes @edespino, it works this way, I thought it would be the same case with all enum datatype parameters but no it's specific to some parameters.

Yes it will be helpful if we add a note or give a example, as it's different from rest of configuration parameters.

gpadmin=# select count(*) , vartype from pg_settings group by 2; count | vartype -------+--------- 31 | enum 42 | string 111 | bool 140 | integer 12 | real

gpadmin=# select name from pg_settings where vartype = 'enum'; name

backslash_quote bytea_output client_min_messages default_transaction_isolation dynamic_shared_memory_type explain_memory_verbosity gp_autostats_mode gp_autostats_mode_in_functions gp_interconnect_fc_method gp_interconnect_type gp_log_format gp_resgroup_memory_policy gp_resqueue_memory_policy gpperfmon_log_alert_level huge_pages IntervalStyle log_error_verbosity log_min_error_statement log_min_messages log_statement optimizer_join_order optimizer_minidump password_hash_algorithm plan_cache_mode session_replication_role synchronous_commit trace_recovery_messages track_functions wal_level xmlbinary xmloption (31 rows)

• Checked for some values, few are enclosed in '' and other are not.

[gpadmin@mdw_ipv4 ~]$ grep backslash_quote $MASTER_DATA_DIRECTORY/postgresql.conf

backslash_quote = safe_encoding # on, off, or safe_encoding

[gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ grep bytea_output $MASTER_DATA_DIRECTORY/postgresql.conf

bytea_output = 'hex' # hex, escape

[gpadmin@mdw_ipv4 ~]$ grep client_min_messages $MASTER_DATA_DIRECTORY/postgresql.conf

client_min_messages = notice # values in order of decreasing detail: