Updated on 2025-02-22 GMT+08:00

How Do I Set GaussDB GUC Parameters?

Setting GUC Parameters for GaussDB Centralized

GaussDB Centralized provides two methods for setting GUC parameters:

  1. Use gsql, a management platform, or other client tools to connect to GaussDB and run the following SQL statements to set the database-level GUC parameters:
    ALTER DATABASE <database_name> SET behavior_compat_options = 'bind_procedure_searchpath,truncate_numeric_tail_zero,plsql_security_definer,proc_outparam_override,aformat_null_test,rownum_type_compat,allow_procedure_compile_check,proc_implicit_for_loop_variable,plstmt_implicit_savepoint,end_month_calculate,disable_rewrite_nesttable,plpgsql_dependency,display_leading_zero,correct_to_number,unbind_divide_bound,convert_string_digit_to_numeric,hide_tailing_zero,return_null_string,aformat_regexp_match,compat_cursor,enable_funcname_with_argsname,tableof_elem_constraints,merge_update_multi';
    ALTER DATABASE <database_name> SET plsql_compile_check_options = 'plsql_expression_check';
    ALTER DATABASE <database_name> SET sql_beta_feature = 'a_style_coerce';
    ALTER DATABASE <database_name> SET a_format_version = '10c';
    ALTER DATABASE <database_name> SET a_format_dev_version = 's6';
  2. Log in to a GaussDB server and use the gs_guc tool to run the following commands to set the instance-level GUC parameters:
    su - omm
    gs_guc reload -Z coordinator  -Z datanode -N all -I all -c "behavior_compat_options='bind_procedure_searchpath,truncate_numeric_tail_zero,plsql_security_definer,proc_outparam_override,aformat_null_test,rownum_type_compat,allow_procedure_compile_check,proc_implicit_for_loop_variable,plstmt_implicit_savepoint,end_month_calculate,disable_rewrite_nesttable,plpgsql_dependency,display_leading_zero,correct_to_number,unbind_divide_bound,convert_string_digit_to_numeric,hide_tailing_zero,return_null_string,aformat_regexp_match,compat_cursor,enable_funcname_with_argsname,tableof_elem_constraints,merge_update_multi'"
    gs_guc reload -Z coordinator  -Z datanode -N all -I all -c "plsql_compile_check_options='plsql_expression_check'"
    gs_guc reload -Z datanode -N all -I all -c  "sql_beta_feature='a_style_coerce'"
    gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_version='10c'"
    gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_dev_version='s6'"

    The preceding SQL statements and commands are applied to migration from Oracle to GaussDB Centralized V2.0-8.0 Enterprise Edition. For other migration flows, replace the parameter names and values before and after equal signs (=) in the preceding commands with those displayed on the pre-check page.

Setting GUC Parameters for GaussDB Distributed

You cannot modify GUC parameters of distributed GaussDB instances on the cloud. Contact GaussDB O&M personnel to modify the parameters.

For on-premises distributed GaussDB instances, you can only log in to a server and use the gs_guc tool to run the following commands to set the instance-level GUC parameters:
gs_guc reload -Z coordinator -Z datanode -N all -I all -c   "behavior_compat_options='bind_procedure_searchpath,truncate_numeric_tail_zero,plsql_security_definer,proc_outparam_override,aformat_null_test,plstmt_implicit_savepoint,end_month_calculate,disable_rewrite_nesttable,display_leading_zero,correct_to_number,unbind_divide_bound,convert_string_digit_to_numeric,hide_tailing_zero,return_null_string,aformat_regexp_match,enable_funcname_with_argsname,tableof_elem_constraints,merge_update_multi'";
gs_guc reload -Z coordinator  -Z datanode -N all -I all -c "a_format_version='10c'";
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "a_format_dev_version='s6'";
gs_guc reload -Z datanode -N all -I all -c  "sql_beta_feature='a_style_coerce'";

The preceding SQL statements and commands are applied to migration from Oracle to GaussDB Centralized V2.0-8.0 Enterprise Edition. For other migration flows, replace the parameter names and values before and after equal signs (=) in the preceding commands with those displayed on the pre-check page.