Transaction

transaction_isolation

Parameter description: Specifies the isolation level of the current transaction. For a new transaction that is started, the isolation level is fixed to the value of default_transaction_isolation. This parameter can be set at the PDB level.

Parameter type: string.

Unit: none

Value range:

• Generally, the following character strings are supported, which are case-sensitive and space-sensitive:
  • 'serializable': Currently, GaussDB does not support this isolation level. Setting this isolation level has the same effect as 'repeatable read'.
  • 'read committed': Only data of committed transactions is read. Uncommitted data cannot be read.
  • 'read uncommitted': The performance is the same as that of 'read committed'.
  • 'repeatable read': Only the data committed before a transaction starts is read. Uncommitted data or data committed in other concurrent transactions cannot be read.
  • 'default': The value is the same as that of default_transaction_isolation.

• When dbcompatibility is set to 'M' and m_format_dev_version is set to 's2', you can also set this parameter to the following character strings, which are sensitive to spaces:
  • 'SERIALIZABLE' (same as 'serializable'): Currently, GaussDB does not support this isolation level. Setting this isolation level has the same effect as 'REPEATABLE-READ'.
  • 'READ-COMMITTED' (same as 'read committed'): Only data of committed transactions is read. Uncommitted data cannot be read.
  • 'READ-UNCOMMITTED' (same as 'read uncommitted'): The performance is the same as that of 'READ-COMMITTED'.
  • 'REPEATABLE-READ' (same as 'repeatable read'): Only the data committed before a transaction starts is read. Uncommitted data or data committed in other concurrent transactions cannot be read.

Default value: 'default'. In the PDB scenario, if this parameter is not set, the global setting is inherited.

• The default_transaction_isolation parameter uses the default value 'read committed'. Generally, the output of the show transaction_isolation; command is 'read committed'.
• When dbcompatibility is set to 'M', m_format_dev_version is set to 's2', and default_transaction_isolation is set to the default value, the output of the show transaction_isolation; command is READ-COMMITTED.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

• When dbcompatibility is set to 'M', querying tx_isolation has the same effect as querying transaction_isolation.
• When dbcompatibility is set to 'M' and m_format_dev_version is set to 's2', SET [local|session|@@session.] transaction_isolation = value; is executed to modify the session-level transaction feature, that is, change the value of default_transaction_isolation.
• When dbcompatibility is set to 'M' and m_format_dev_version is set to 's2', SET @@transaction_isolation = value; is executed to set the isolation level of the next transaction and cannot be used in an explicit transaction. If an implicit transaction reports an error, that is, a single SQL statement reports an error, the next transaction feature is retained.

transaction_read_only

Parameter description: Specifies whether the current transaction is a read-only transaction. For a new transaction, the value of this parameter is fixed to on during database restoration or on the standby node. Otherwise, the value of this parameter is fixed to the value of default_transaction_read_only in the CDB. This parameter can be set at the PDB level. After pdb_transaction_read_only is set, the value of pdb_transaction_read_only prevails.

Parameter type: Boolean.

Unit: none

Value range:

• on: The current transaction is a read-only transaction.
• off: The current transaction is a read/write transaction.

Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

• When dbcompatibility is set to 'M', querying tx_read_only has the same effect as querying transaction_read_only.
• When dbcompatibility is set to 'M' and m_format_dev_version is set to 's2', SET [local|session|@@session.] transaction_read_only= value is executed to modify the session-level transaction feature, that is, change the value of default_transaction_read_only.
• When dbcompatibility is set to 'M' and m_format_dev_version is set to 's2', SET @@transaction_read_only= value; is executed to set the access mode of the next transaction and cannot be used in an explicit transaction. If an implicit transaction reports an error, that is, a single SQL statement reports an error, the next transaction feature is retained.

autocommit

Parameter description: Specifies whether to enable the automatic commit mode. If this parameter is set to on, sessions will be committed automatically and each individual statement is implicitly committed. If this parameter is set to off, you must run COMMIT to commit a transaction or run ROLLBACK to cancel a transaction. If autocommit is changed from off to on, all unfinished transactions are automatically committed.

Parameter type: Boolean.

Unit: none

Value range:

• on: The automatic commit mode is enabled.
• off: The automatic commit mode is disabled.

Default value: on

Setting method: This parameter is a USERSET parameter. You can set it based on instructions provided in Table 1. The gs_guc tool cannot be used to set this parameter.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If this parameter is set to off, DDL or DCL statements executed in a transaction will not be automatically committed.

• When dbcompatibility is set to 'M', this parameter can be set to off. Otherwise, this parameter is set to on.
• In GaussDB, this parameter cannot be queried in the pg_settings view. You can run the SHOW autocommit; command to query it.
• If the autocommit parameter is used, the internal automatic commit mode must be enabled. You can run \echo :AUTOCOMMIT to check whether the internal automatic commit mode is enabled. If the automatic commit mode is not used, run \set AUTOCOMMIT on to enable the automatic commit mode.

xc_maintenance_mode

Parameter description: Specifies whether the system is in maintenance mode.

Parameter type: Boolean.

Unit: none

Value range:

• on: The maintenance mode is used.
• off: The maintenance mode is not used.

Enable this function with caution to avoid data inconsistencies in the database.

Default value: off

Setting method: This parameter is a SUSET parameter. You can set it only using method 3 described in Table 2 Methods for setting GUC parameters.

Setting suggestion: This parameter is used by maintenance personnel to locate faults. You are advised not to enable this parameter.

Risks and impacts of improper settings: Changing the value of this parameter may cause unexpected results in the database. Retain the default value.

allow_concurrent_tuple_update

Parameter description: Specifies whether to allow concurrent update. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

• on: Concurrent updates are allowed.
• off: Concurrent updates are not allowed.

Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_show_any_tuples

Parameter description: This parameter is available only in a read-only transaction and is used for analysis. When this parameter is set to on, all versions of tuples in the table are displayed. This parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

• on: All versions of tuples in the table are displayed.
• off: No versions of tuples in the table are displayed.

Default value: off. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

In the TOAST scenario, if DML operations (mainly INSERT+UPDATE or INSERT+DELETE) are performed before and after the REINDEX operation, and the read-only transaction is started and the GUC parameter is enabled after the REINDEX operation, historical data in the TOAST table or TOAST index table can be queried separately in the released version. However, when historical data in the TOAST column is queried in the main table, the error message "missing chunk number xxx" is displayed.

replication_type

Parameter description: Specifies whether the current database is deployed in standalone or one-primary-multiple-standby mode.

Parameter type: integer.

Unit: none

Value range: 0 to 3

• 0: reserved parameter.
• 1: one-primary-multiple-standby mode, covering all scenarios. This mode is recommended.
• 2: single primary mode. In this mode, the standby node cannot be expanded.
• 3: reserved parameter.

Default value: 1

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1. This parameter is used for CM deployment and cannot be set.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

pgxc_node_name

Parameter description: Specifies the name of a node.

When a standby node requests to replicate logs on the primary node, if the application_name parameter is not set, the pgxc_node_name parameter is used as the name of the streaming replication slot of the standby node on the primary node. The streaming replication slot is named in the following format: Value of this parameter_IP address of the standby node_Port number of the standby node. The IP address and port number of the standby node are obtained from the IP address and port number of the standby node specified by the replconninfo parameter. The maximum length of a streaming replication slot name is 61 characters. If the length of the concatenated string exceeds 61 characters, the truncated pgxc_node_name will be used for concatenation to ensure that the length of the streaming replication slot name is less than or equal to 61 characters.

Parameter type: string.

Unit: none

Value range: a string

Default value: current node name.

Setting method: This is a POSTMASTER parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: After the modification, the database instance will fail to be connected.

enable_defer_calculate_snapshot

Parameter description: Delays the calculation of xmin and oldestxmin of a snapshot. The calculation is triggered only after 1000 transactions are executed or the calculation interval is 1s.

Parameter type: Boolean.

Unit: none

Value range:

• on indicates that snapshots xmin and oldestxmin are calculated with a delay.
• off indicates that snapshots xmin and oldestxmin are calculated in real time.

Default value: on

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: If this parameter is set to on, the snapshot calculation overhead can be reduced in high load scenarios. However, oldestxmin is pushed slowly, affecting garbage tuple collection. If this parameter is set to off, xmin and oldestxmin can be pushed in real time, but the overhead for calculating snapshots increases.

seqscan_csn_cache_num

Parameter description: Specifies the size of the CSN cache. The cache is used only when the Seq Scan scans the heap table page through the MVCC snapshot to determine the visibility.

Parameter type: integer.

Unit: none

Value range: 0 to 1000. If this parameter is set to 0, the CSN cache mechanism is not used.

Default value: 100

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_interp_reuse_tran

Parameter description: Specifies whether subtransactions of nested exception blocks can be reused in stored procedures. This parameter takes effect only when plstmt_implicit_savepoint of the behavior_compat_options parameter is enabled. In the multi-tenancy scenario, this parameter can be set at the PDB level.

Parameter type: Boolean.

Unit: none

Value range:

• on: The stored procedure supports transaction reuse of nested exception blocks. This function takes effect only when plstmt_implicit_savepoint of the behavior_compat_options parameter is enabled.
• off: The stored procedure does not support transaction reuse of nested exception blocks.

Default value: on. In the PDB scenario, if this parameter is not set, the global setting is inherited.

Setting method: This is a USERSET parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.

enable_lightweight_transaction

Parameter description: Specifies whether to enable lightweight transactions. Lightweight transactions refer to the performance optimization feature of query statements. Query statements that can use lightweight transactions do not contain subqueries, SELECT FOR UPDATE, function calls, write operations, or JOIN operations. These query statements are not executed in transactions or stored procedures.

Parameter type: Boolean.

Unit: none

Value range:

• on: enables the lightweight transaction check.
• off: disables the lightweight transaction check.

Default value: on. If this parameter is not set, the global setting is inherited.

Setting method: This is a SIGHUP parameter. Set it based on instructions provided in Table 1.

Setting suggestion: Retain the default value.

Risks and impacts of improper settings: Change the parameter value after fully understanding the parameter meaning and verifying it through testing.