Client Connection Defaults Category Parameters

This document describes the parameters related to the client connection default values category in the system configuration parameters.

Caution!
To ensure system stability and security, please exercise caution when manually modifying the relevant parameters.

Statement Behavior

bytea_output

Set the output format for bytea type values.

Supported options are hex (default) and escape (traditional PostgreSQL format).

Data type	Default value	Value range	Setting category
enum	hex	hex / escape	segments; session; reload

check_function_bodies

This parameter controls whether to check the syntax and references of the function body when creating a function using the CREATE FUNCTION statement.

This option is enabled by default.
If this option is disabled, the function body will not be checked when the function is created, which may help avoid some issues caused by the function body referencing objects that have not yet been created. However, this also means that if the function body contains syntax errors, they will not be detected during function creation and will only be reported when the function is called.

Data Type	Default Value	Setting Category
boolean	on	master; session; reload

client_min_messages

Control the level of messages sent to the client.

Note: The level of the log value in this parameter is different from that in the log_min_messages parameter.

Data type	Default value	Value range	Setting category
enum	notice	debug5 / debug4 / debug3 / debug2 / debug1 / log / notice / warning / error Each level includes all subsequent levels. The later the level, the fewer messages are sent.	master；session；reload

default_tablespace

This variable specifies the default tablespace for creating objects (tables and indexes) when a CREATE command does not explicitly specify a tablespace.

It also determines the tablespace that guides the partitioning relationships for future partitions.
If the value is an empty string, the current database is specified as the default tablespace. If the value matches an existing tablespace name and has CREATE permissions, the setting is successful; otherwise, the current database is automatically specified as the default tablespace.
This variable is not used for temporary tables; temporary tables use temp_tablespaces.
This variable is also used when creating a database. By default, a new database inherits its tablespace settings from its template database.
For more information about tablespaces, see CREATE TABLESPACE.

Data Type	Default Value	Setting Category
string		master；session；reload

default_transaction_read_only

Whether to grant read-only permissions to each new transaction.

Read-only SQL transactions cannot modify non-temporary tables.
The default is off, which grants both read and write permissions.

Data Type	Default Value	Setting Category
boolean	off	master；session；reload

gin_fuzzy_search_limit

The soft upper limit of the size of the set returned by the GIN index.

Data type	Default value	Value range	Setting category
int	0	0 ～ INT_MAX	master；session；reload

gin_pending_list_limit

The maximum size of the pending list of GIN indexes that can be used when fastupdate is enabled.

If the list grows beyond this maximum size, it is cleaned up by moving items in batches into the main GIN data structure of the index.
This setting can be overridden for individual GIN indexes by changing the index's storage parameters.

Data type	Default value	Value range	Setting category
int	4096	64 ～（INT_MAX/1024）	master；session；reload

gp_vmem_idle_resource_timeout

If a database session remains idle for longer than the specified time (in milliseconds), the session will release system resources (such as shared memory) but will remain connected to the database.

This allows for more concurrent connections to the database at once.
If the value is set to 0, this feature is disabled.

Data type	Default value	Value range	Setting category
int	18000	0 ~ INT_MAX	master；system；reload

idle_in_transaction_session_timeout

Terminate any session with an open transaction that has been idle for longer than the time specified by this parameter.

This causes any locks held by the session to be released, and the connection slot it holds to be reused, allowing tuples visible only to this transaction to be cleaned up.
The default value is 0, which disables the timeout.

Data type	Default value	Value range	Setting category
int	0	0 ～ INT_MAX	master；session；reload

lc_collate

Reports the locale settings for sorting text data.

This value is determined when initializing the YMatrix database cluster.

Data Type	Default Value	Setting Category
	System Dependency	read only

lc_ctype

The report determines the regional settings for character classification.

This value is determined when initializing the YMatrix database cluster.

Data Type	Default Value	Setting Category
	System Dependency	read only

lock_timeout

If any statement waits longer than the time specified by this parameter while attempting to acquire a lock on a table, index, row, or other database object, the statement will be aborted.

This time limit is applied independently to each lock acquisition attempt. The limit applies to explicit lock requests (such as LOCK TABLE or SELECT FOR UPDATE without NOWAIT) and implicitly acquired locks.
Unlike statement_timeout, this timeout only occurs while waiting for a lock.
Note: If statement_timeout is non-zero, setting lock_timeout to the same or a larger value is meaningless, as the transaction timeout will always be triggered first.
If log_min_error_statement is set to error or lower, the timed-out statement will be logged.
We do not recommend setting lock_timeout in postgresql.conf, as it affects all sessions.
The default value is 0, which disables the timeout.

Data type	Default value	Value range	Setting category
int	0	0 ～ INT_MAX	master；session；reload

row_security

This variable controls whether to raise an error instead of applying a row security policy.

When set to on, the policy is applied normally; when set to off, the query fails if at least one policy is applied.
When restricted row visibility leads to incorrect results, you can set it to off. pg_dump makes this change by default.
This variable has no effect on roles that can bypass every row-level security policy (i.e., superusers and roles with the BYPASSRLS attribute).
For more information about row-level security policies, see CREATE POLICY.

Data Type	Default Value	Setting Category
boolean	on	master；system；restart

search_path

When an object (table, data type, function, etc.) is referenced by a simple name without a schema qualifier, this parameter specifies the order in which the schema containing the object is searched.

When objects with the same name exist in different schemas, the first object found in the search path is used.
An object that does not belong to any schema in the search path can only be referenced by specifying the schema containing it using a qualified name (with a dot).
The value of search_path must be a comma-separated list of schema names. Any name that is not an existing schema name, or a schema for which the user does not have USAGE permission, will be ignored without warning.
If one of the list items is the special name $user, the schema with the name returned by CURRENT_USER will replace this special name. If such a special name schema exists, the user has USAGE privileges for that schema (if not, $user is ignored).
Regardless of whether pg_catalog is mentioned in the search path, the system catalog schema is always searched. If it is mentioned in the path, it will be searched in the order specified by the path. If pg_catalog is not in the path, it will be searched before any path items.
Similarly, the current session's temporary table schema pg_temp_nnn is always searched (if it exists). It can be explicitly listed in the path using the alias pg_temp. If it is not listed in the path, it will be searched first (even before pg_catalog). However, the temporary schema is only used to search for relations (tables, views, sequences, etc.) and data type names. It is never used to search for function or operator names.
When objects are created without specifying a specific target schema, they will be placed in the first valid schema in the search_path.
If the search path is empty, an error is reported.
The default value for this parameter is “$user”, public. This configuration supports databases where no user owns a private schema and all users share the public schema, each user has their own private schema, or a combination of both. Other effects can be achieved by modifying the default search path settings globally or per user.
The default configuration is appropriate only when the database has a single user or a small number of mutually trusted users.
The current valid value of the search path can be checked using the SQL function current_schemas. This differs from checking the value of search_path because current_schemas shows how the items currently in search_path are resolved.

Data type	Default value	Value range	Setting category
string	“$user”, public	Comma-separated list of pattern names	master; session; reload

session_replication_role

Controls the triggering of triggers and rules related to replication for the current session.

Superuser privileges are required to set this variable, and doing so will cause any previously cached query plans to be discarded.
The intended use of this setting is for the logical replication system to set it to replica when applying the replicated changes. The effect will be that triggers and rules (without modifications to their default configuration) will not be triggered on the replica. For more information, refer to the ENABLE TRIGGER and ENABLE RULE clauses in the ALTER TABLE statement.
YMatrix internally treats the origin and local settings equally. Third-party replication systems may use these values for their internal purposes, such as using local to mark a session whose changes should not be replicated.
Since foreign keys are implemented as triggers, setting this parameter to replica will also disable all foreign key checks. If used improperly, this may result in inconsistent data.

Data type	Default value	Value range	Setting category
enum	origin	origin / replica / local	segments；session；reload；superuser

statement_timeout

Terminate any statement that has been running for longer than the specified amount of time (in milliseconds).

If log_min_error_statement is set to error or lower, statements that time out will also be logged.
The timeout is calculated from the time the command arrives at the server until it is completed by the server. In the extended query protocol, the timeout begins when any query-related message (parse, bind, execute, describe) arrives and can be canceled by completing the execution or synchronizing the message.
We do not recommend setting this parameter in postgresql.conf, as it affects all sessions.
The default value is 0, which disables the timeout.

Data type	Default value	Value range	Setting category
int	0	0 ～ INT_MAX	master；session；reload

temp_tablespaces

When the CREATE command does not explicitly specify a tablespace, specify the tablespace in which to create temporary objects (temporary tables and indexes on temporary tables).

These tablespaces may also contain temporary files used for purposes such as sorting large datasets.
This value is a comma-separated list of tablespace names. When the list contains multiple tablespace names, YMatrix randomly selects one list member each time a temporary object is created.
An exception applies to transactions, where consecutively created temporary objects are placed in consecutive tablespaces in the list. If the selected element of the list is an empty string, YMatrix automatically uses the default tablespace of the current database.
When setting temp_tablespaces interactively, avoid specifying non-existent tablespaces or tablespaces for which the user does not have CREATE permissions. For non-superusers, superusers must grant them CREATE privileges on temporary tablespaces. When using previously set values (e.g., values in postgresql.conf), non-existent tablespaces are ignored, as are tablespaces for which the user lacks CREATE privileges.
The default value is an empty string, which causes all temporary objects to be created in the default tablespace of the current database.

Data type	Default value	Value range	Setting category
string		One or more tablespace names	master；session；reload

vacuum_cleanup_index_scale_factor

Specifies a fraction of the total number of heap tuples counted during the previous statistics collection process. Inserting tuples up to this quantity will not cause an index scan during the VACUUM cleanup phase.

This setting currently applies only to B-tree indexes.
If no tuples are deleted from the heap, the B-tree index will still be scanned during the VACUUM cleanup phase if at least one of the following conditions is met: the index statistics are stale, or the index contains deleted pages that can be reclaimed during cleanup.
The index information is considered outdated if the number of recently inserted tuples exceeds vacuum_cleanup_index_scale_factor of the total number of heap tuples detected during the last statistics collection.
The total number of heap tuples is stored in the index's meta pages. Note: This data is not included in the meta pages until VACUUM finds dead tuples. Therefore, the B-tree index scan in the cleanup phase can only be skipped if no dead tuples are detected during the second and subsequent VACUUM cycles.
When vacuum_cleanup_index_scale_factor is set to 0, the index scan is not skipped during the VACUUM cleanup.

Data type	Default value	Value range	Setting category
floating point	0.1	0 ～ 10000000000	segments；session；reload

vacuum_freeze_min_age

Specify the cutoff age (within the transaction) that VACUUM should use when deciding whether to replace transaction IDs with FrozenXIDs while scanning tables.

Data type	Default value	Value range	Setting category
int	50000000	0 ~ 100000000000	segments；system；restart

vacuum_freeze_table_age

When the pg_class.relfrozenxid field of a table reaches the age specified by this setting, VACUUM performs an aggressive scan.

An aggressive scan differs from a regular VACUUM in that it accesses every page that may contain unfrozen XIDs or MXIDs, rather than just those that may contain dead tuples.
Before initiating an automatic defragmentation cleanup on a table, there is an opportunity to perform a manual VACUUM on a regular basis.

Data type	Default value	Value range	Setting category
int	150000000	0 ～ 200000000	segments；session；reload

vacuum_multixact_freeze_min_age

Specify VACUUM to determine whether to replace the composite transaction ID with an updated transaction ID or the cutoff age of the composite transaction ID (calculated based on the composite transaction) when scanning the table.

There will be no short intervals between enforced automatic cleaning.

Data Type	Default Value	Value Range	Set Classification
int	5000000	0 ~ 100000000	segments; session; reload

vacuum_multixact_freeze_table_age

If the pg_class.relminmxid field of the table exceeds the age specified by this setting, VACUUM performs an aggressive scan.

The difference between an aggressive scan and a regular VACUUM is that it visits every page that may contain an unfreezen XID or MXID rather than just those that may contain a dead tuple.
There is a chance to be executed regularly before starting a back-volume automatic cleanup on the table.

Data Type	Default Value	Value Range	Set Classification
int	150000000	0 ~ 200000000	segments; session; reload

Locale and Formatting

client_encoding

Set client encoding (character set).

Database encoding is used by default.

Data Type	Default Value	Value Range	Set Classification
string	SQL_ASCII	Supported character sets	master; session; reload

datestyle

Sets the display format for date and time values, and rules for interpreting ambiguity of date input values.

For historical reasons, this variable contains two separate parts: output format declaration (ISO, Postgres, SQL, or German), input/output year/month/day order (DMY, MDY, or YMD). These can be set independently or together. The keywords Euro and European are synonyms for DMY; the keywords US, NonEuro and NonEuropean are synonyms for MDY.
The default value is ISO, MDY, but initdb will initialize the configuration file with settings corresponding to the selected lc_time area behavior.

Data Type	Default Value	Value Range	Set Classification
string	iso, mdy	, <Year/month/day order of input/output>	master; system; restart

default_text_search_config

Select those variants of the text search function to use the text search configuration that does not specify explicit parameters for the configuration.

The default value is pg_catalog.simple, but if the configuration of a matching area can be identified, initdb will initialize the configuration file with the settings corresponding to the selected lc_ctype area.

Data Type	Default Value	Value Range	Set Classification
string	pg_catalog.english	Name of text search configuration	master; session; reload

extra_float_digits

This parameter is used to adjust the number of bits displayed by floating point values, including float4, float8 and geometric data types.

If the value is 1 (default) or higher, the floating point value is output to the shortest precision format. The actual number of bits generated depends only on the value of the output, not on the value of this parameter.
The float8 value requires up to 17 digits, and the float4 value requires up to 9 digits.
This format is fast and precise, retaining the original binary floating point values when read correctly.
For historical compatibility, the maximum allowed value is 3.
If the value is 0 or negative, the output is rounded to the given decimal precision.

Data Type	Default Value	Value Range	Set Classification
int	1	-15 ~ 3	master; session; reload

intervalstyle

Sets the display format of interval values.

The value sql_standard will produce output matching SQL standard interval text
The value iso_8601 will generate output that matches the ISO 8601 [Defined Indicator] (https://www.postgresql.org/docs/8.4/datatype-datetime.html).
The intervalstyle parameter can also affect the interpretation of ambiguity of interval inputs.| Data Type | Default Value | Value Range | Set Classification | | --- | --- | --- | --- | | enum | postgres | sql_standard / postgres / iso_8601 / postgres_verbose | master; session; reload |

lc_messages

Sets the language in which the message is displayed.

The available locale depends on what the operating system is installed - that is, the locale listed using locale -a.
If this variable is set to an empty string (default), then the value will be inherited from the server's execution environment in a system-related way.
On some systems, this regional classification does not exist. You can still set this variable, but it won't have any effect. Likewise, translation messages in the desired language may not exist. In this case, you will continue to see the English message.
Only superusers can change this setting, as it affects messages sent to both the server log and the client. An incorrect value may reduce the readability of the server log.

Data Type	Default Value	Value Range	Set Classification
string		Locale listed using `locale -a`	segments; system; restart; superuser

lc_monetary

Sets the locale for formatting currency amounts, for example using the to_char series function.

The available locale depends on what the operating system is installed - that is, the locale listed using locale -a.
The default value inherits from the execution environment of the server.

Data Type	Default Value	Value Range	Set Classification
string		Locale listed using `locale -a`	segments; system; restart

lc_numeric

Sets the locale for formatting numbers, for example using the to_char series of functions.

The available locale depends on what the operating system is installed - that is, the locale listed using locale -a.
The default value inherits from the execution environment of the server.

Data Type	Default Value	Value Range	Set Classification
string		Locale listed using `locale -a`	segments; system; restart

lc_time

Sets the area used to format dates and times, for example using the to_char series function.

The available locale depends on what the operating system is installed - that is, the locale listed using locale -a.
The default value inherits from the execution environment of the server.

Data Type	Default Value	Value Range	Set Classification
string		Locale listed using `locale -a`	segments; system; restart

timezone

Sets the time zone to display and interpret timestamps.

The default setting is to use any time zone specified by the system environment. For more information, see Time Zone.

Data Type	Default Value	Value Range	Set Classification
string	GMT	time zone abbreviation	segments; restart

timezone_abbreviations

Sets the collection of time zone abbreviations used in datetime input accepted by the server.- The supported option is the name of the configuration file stored in $GPHOME/share/postgresql/timezonesets/, and you can also create your own files under this path. For example: To use a file custom containing the default time zone and the WIB (Waktu Indonesia Barat) time zone.

Copy the file Default from directory $GPHOME/share/postgresql/timezonesets/ to file custom. Add the WIB time zone information in the file Asia.txt to custom.
Copy the file custom to the $GPHOME/share/postgresql/timezonesets/ directory on the YMatrix database Master and Segment hosts.
Set the value of the server configuration parameter timezone_abbreviations to custom.
Reload the server configuration file (mxstop -u).

Data Type	Default Value	Value Range	Set Classification
enum	Default	Default / Australia / India /	master; session; reload

Shared Library Preloading

To load additional functions or to improve performance, multiple settings can be used to preload the shared library to the server. For example, the $libdir/mylib setting may cause mylib.so (or mylib.sl on some platforms) to be preloaded from the installed standard library directory. The difference between these settings is when they take effect and the privileges required to change them.

You can preload YMatrix's procedural language library in this way, usually using the $libdir/plXXX syntax, where XXX is pgsql, perl, tcl, or python. Only shared libraries specifically designed for use with YMatrix can be loaded in this way. Each library supported by YMatrix has a “magic block” that is checked to ensure compatibility. For this reason, non-YMatrix libraries cannot be loaded in this way. You may be able to load them using operating system tools such as LD_PRELOAD.

In summary, please refer to the documentation for the specific module to load it using the recommended method.

jit_provider

This variable indicates the name of the JIT provider library to be used.

This parameter can only be set when the server is started.
If this variable is set to a non-existent library, JIT will be unavailable, but no error will occur.
This feature allows JIT support to be installed separately outside the main YMatrix package.

Data type	Default value	Setting category
string	llvmjit	segments；session；restart；superuser

local_preload_libraries

Any user can use this variable to specify one or more shared libraries to be preloaded at the start of the connection.

It contains a comma-separated list of library names, each of which is resolved as if it were a LOAD command. Spaces between items are ignored. If you need to include spaces or commas in a library name, enclose the name in double quotes.
This parameter value only takes effect at the start of the connection. Subsequent changes will have no effect. If a specified library is not found, the connection attempt will fail.
Any user can set this option. For this reason, libraries that can be loaded in this way are strictly limited to shared libraries located in the plugins subdirectory of the installed standard library directory (it is the database administrator's responsibility to ensure that only “safe” libraries are installed here).
Items in local_preload_libraries can explicitly specify this directory, e.g., $libdir/plugins/mylib, or simply specify the library name — mylib and $libdir/plugins/mylib have the same effect.
The purpose of this feature is to allow non-privileged users to load libraries that are being debugged or used for performance measurement in a specific session without an explicit LOAD command. For this purpose, this parameter is typically set using the client's PGOPTIONS environment variable or ALTER ROLE SET.
However, unless a module is specifically designed to be used in this way by non-superusers, this setting is generally not recommended, and it is recommended to focus on session_preload_libraries instead.

Data type	Default value	Setting category
string		segments；system；restart

session_preload_libraries

Only superusers can use this variable to specify one or more shared libraries to be preloaded at the start of the connection.

It contains a comma-separated list of library names, each of which is resolved as if it were a LOAD command. Spaces between items are ignored; if you need to include spaces or commas in a library name, enclose the name in double quotes.
This parameter value only takes effect at the start of the connection. Subsequent changes will have no effect. If a specified library is not found, the connection attempt will fail.
The purpose of this feature is to allow non-privileged users to load libraries being debugged or used for performance measurement in a specific session without an explicit LOAD command. For example, setting this parameter using ALTER ROLE SET can enable auto_explain for all sessions under a given username.
This parameter can be changed without restarting the server (but it only takes effect when new sessions are started). In this way, even if the change needs to be applied to all sessions, adding new modules becomes easier.
Unlike shared_preload_libraries, loading a library at session startup offers no performance advantage over loading it when it is first used. However, this parameter has some advantages when using a connection pool.

Data type	Default value	Setting category
string		segments；session；reload；superuser

shared_preload_libraries

It contains a comma-separated list of library names, each of which is resolved as if it were a LOAD command. Spaces between items are ignored. If you need to include spaces or commas in a library name, enclose the library name in double quotes.
This parameter can only be set when the server starts. If the specified library is not found, the server will fail to start.
Some libraries require specific operations that can only be performed when postmaster starts, such as allocating shared memory, reserving lightweight locks, or starting background workers. These libraries must be loaded at server startup using this parameter.
Other libraries can also be preloaded. By preloading a shared library, you can avoid the library startup time when it is first used. However, the time required to start each new server process may increase slightly, even if the process never uses the library. Therefore, it is recommended to use this parameter only for libraries that are used in most sessions.
Changing this parameter requires restarting the server, so it is not a good choice for short-term debugging tasks; instead, use session_preload_libraries.
Note: On Windows hosts, preloading a library at server startup does not reduce the time required to start each new server process; each server process will reload the preloaded library. However, for libraries that need to perform operations when postmaster starts, shared_preload_libraries is still useful on Windows hosts.

Data type	Default value	Setting category
string		segments；system；restart

Other Defaults

dynamic_library_path

If you need to load a dynamically loaded module, and the filename specified in the CREATE FUNCTION or LOAD command does not include a directory path (i.e., the name does not contain “/”), the system will search this path to locate the required file.

The value of dynamic_library_path must be a list of absolute directory paths separated by colons (or semicolons on Windows). If a list element starts with a special string, $libdir will be replaced with the directory of precompiled libraries in the PostgreSQL package. This is the module installation location provided by the PostgreSQL distribution (use pg_config --pkglibdir to find the name of this directory).
For example:
```
dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
```
Or in a Windows environment:
```
dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
```
The default value of this parameter is $libdir. If the value is set to an empty string, automatic path search is turned off.
This parameter can be modified by the super user at runtime, but the modified settings can only be maintained until the end of this client connection, so this method should be reserved for development purposes.
We recommend setting this parameter in the postgresql.conf configuration file.

Data Type	Default Value	Value Range	Set Classification
string	$libdir	A colon-separated list of absolute directory paths	segments; system; restart