Client Connection Defaults

This section describes the configuration parameters in the Client Connection Defaults category.

Note!
Manually modifying these parameters should be done with caution to ensure system stability and security.

Statement Behavior

bytea_output

Sets the output format for values of type bytea.

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

Data Type	Default Value	Possible Values	Category
enum	hex	hex / escape	segments; session; reload

check_function_bodies

Controls whether the body of a function is checked for syntax and referenced objects when created using the CREATE FUNCTION statement.

This option is enabled by default.
When disabled, function bodies are not validated during creation, which may help avoid errors caused by references to objects not yet created. However, syntax errors in the function body will only be detected when the function is called.

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

client_min_messages

Sets the message level sent to the client.

Note: The meaning of the log level differs here from its meaning in the log_min_messages parameter.

Data Type	Default Value	Possible Values	Category
enum	notice	debug5 / debug4 / debug3 / debug2 / debug1 / log / notice / warning / error Each level includes all subsequent levels. Higher levels result in fewer messages being sent.	master; session; reload

default_tablespace

Specifies the default tablespace for creating tables and indexes when a CREATE command does not explicitly specify one.

It also determines the tablespace used for future partitions of partitioned tables.
If this value is an empty string, the current database is used as the default tablespace. If it matches an existing tablespace name and the user has CREATE privilege, the setting succeeds; otherwise, the current database is used.
This variable does not apply to temporary tables; use temp_tablespaces instead.
This setting is also used when creating a new database. By default, a new database inherits the tablespace setting from its template database.
For more information about tablespaces, see CREATE TABLESPACE.

Data Type	Default Value	Category
string		master; session; reload

default_transaction_read_only

Determines whether each new transaction is read-only.

Read-only SQL transactions cannot modify non-temporary tables.
Default is off, allowing both read and write operations.

Data Type	Default Value	Category
boolean	off	master; session; reload

gin_fuzzy_search_limit

Soft upper limit on the size of result sets returned by GIN index scans.

Data Type	Default Value	Possible Values	Category
int	0	0 ~ INT_MAX	master; session; reload

gin_pending_list_limit

Sets the maximum size of the pending list for GIN indexes when fastupdate is enabled.

If the list exceeds this size, entries are batch-moved into the main GIN structure.
This setting can be overridden per index via storage parameters.

Data Type	Default Value	Possible Values	Category
int	4096	64 ~ (INT_MAX/1024)	master; session; reload

gp_vmem_idle_resource_timeout

Releases system resources (e.g., shared memory) for a database session if it remains idle longer than the specified time (in milliseconds), while keeping the connection open.

Allows support for more concurrent connections.
A value of 0 disables this feature.

Data Type	Default Value	Possible Values	Category
int	18000	0 ~ INT_MAX	master; system; reload

idle_in_transaction_session_timeout

Terminates any session with an open transaction that has been idle longer than the specified time.

Releases held locks, frees connection slots, and allows cleanup of tuples visible only to the transaction.
Default value is 0, disabling timeout.

Data Type	Default Value	Possible Values	Category
int	0	0 ~ INT_MAX	master; session; reload

lc_collate

Reports the locale setting used for text data sorting.

Determined when initializing the YMatrix database cluster.

Default Value	Possible Values	Category
	system-dependent	read only

lc_ctype

Reports the locale setting used for character classification.

Determined when initializing the YMatrix database cluster.

Default Value	Possible Values	Category
	system-dependent	read only

lock_timeout

Aborts any statement waiting longer than the specified time (in milliseconds) to acquire a lock on a table, index, row, or other database object.

Applies independently to each lock acquisition attempt.
Applies to both explicit (e.g., LOCK TABLE, SELECT FOR UPDATE without NOWAIT) and implicit locks.
Unlike statement_timeout, this timeout applies only during lock waits.
Note: If statement_timeout is non-zero, setting lock_timeout to the same or larger value is ineffective, as transaction timeout will trigger first.
Statements timing out are logged if log_min_error_statement is set to error or lower.
Setting lock_timeout in postgresql.conf is not recommended, as it affects all sessions.
Default value is 0, disabling timeout.

Data Type	Default Value	Possible Values	Category
int	0	0 ~ INT_MAX	master; session; reload

row_security

Controls whether row-level security policies are enforced or cause an error.

When set to on, policies are applied normally.
When set to off, any query affected by a policy fails.
Set to off when restricted row visibility could lead to incorrect results. pg_dump makes this change by default.
This setting has no effect on roles that bypass row security (superusers and roles with BYPASSRLS attribute).
For more information, see CREATE POLICY.

Data Type	Default Value	Category
boolean	on	master; system; restart

search_path

Specifies the order in which schemas are searched when referencing an object (table, type, function, etc.) by an unqualified name.

The first matching object in the path is used when multiple schemas contain objects with the same name.
Objects not in any schema in the path must be referenced with a qualified name (schema.object).
search_path must be a comma-separated list of schema names. Any non-existent schema or schema without USAGE privilege is silently ignored.
The special name $user is replaced by the schema named by CURRENT_USER. This schema is included only if the user has USAGE privilege on it; otherwise, $user is ignored.
The system catalog schema pg_catalog is always searched. If listed in the path, it is searched in order; otherwise, it is searched before any other schema.
The session's temporary schema pg_temp_nnn is also always searched (if it exists). It can be explicitly listed using the alias pg_temp. If not listed, it is searched first (even before pg_catalog). However, the temporary schema is used only for relations (tables, views, sequences) and data types, never for functions or operators.
Newly created objects without an explicit target schema are placed in the first valid schema in search_path.
An error is raised if the search path is empty.
Default value is "$user", public. This supports databases where users either share the public schema, have private schemas, or a mix of both.
This default is suitable only when the database has a single user or a few trusted users.
The effective search path can be checked using the SQL function current_schemas. This differs from checking search_path, as current_schemas shows how items in search_path are resolved.

Data Type	Default Value	Possible Values	Category
string	"$user", public	comma-separated list of schema names	master; session; reload

session_replication_role

Controls the firing of replication-related triggers and rules for the current session.

Requires superuser privileges and causes previously cached query plans to be discarded.
Intended for logical replication systems to set this to ENABLE TRIGGER when applying replicated changes, so triggers and rules (unless modified) do not fire on replicas. See ALTER TABLE clauses ENABLE RULE and origin.
YMatrix treats local and local identically. Third-party replication systems may use these values internally, e.g., replica to mark sessions whose changes should not be replicated.
Foreign keys are implemented as triggers; setting this to log_min_error_statement disables foreign key checks. Misuse may leave data in an inconsistent state.

Data Type	Default Value	Possible Values	Category
enum	origin	origin / replica / local	segments; session; reload; superuser

statement_timeout

Aborts any statement running longer than the specified time (in milliseconds).

Statements timing out are logged if error is set to postgresql.conf or lower.
Timeout starts when the command reaches the server and ends when processing completes. In extended query protocol, timeout begins on any query-related message (parse, bind, execute, describe) and ends on sync or completion.
Not recommended to set in 0, as it affects all sessions.
Default value is CREATE, disabling timeout.

Data Type	Default Value	Possible Values	Category
int	0	0 ~ INT_MAX	master; session; reload

temp_tablespaces

Specifies the tablespace(s) for temporary objects (temporary tables and their indexes) when temp_tablespaces does not explicitly specify one.

These tablespaces may also store temporary files for large sorts.
Value is a comma-separated list of tablespace names. YMatrix randomly selects one when creating temporary objects.
Within a transaction, consecutive temporary objects are placed in successive tablespaces from the list.
If a selected element is an empty string, YMatrix uses the current database’s default tablespace.
Avoid specifying non-existent tablespaces or those without CREATE privilege. Non-superusers require CREATE privilege on the temporary tablespace, granted by a superuser.
Non-existent or inaccessible tablespaces are ignored when using saved values (e.g., from postgresql.conf).
Default is an empty string, placing all temporary objects in the current database’s default tablespace.

Data Type	Default Value	Possible Values	Category
string		one or more tablespace names	master; session; reload

vacuum_cleanup_index_scale_factor

Specifies a fraction of the total heap tuples counted in the previous statistics collection. Insertions below this threshold will not trigger index scanning during the VACUUM cleanup phase.

Currently applies only to B-tree indexes.
If no tuples are deleted from the heap, B-tree index scanning still occurs during VACUUM cleanup if: index statistics are outdated, or the index contains deletable pages.
Index statistics are considered outdated if the number of recently inserted tuples exceeds vacuum_cleanup_index_scale_factor of the total heap tuples at last stats collection.
Total heap tuple count is stored in the index metapage. Note: This data is absent until VACUUM finds no dead tuples. Thus, skipping index scan in cleanup is possible only from the second VACUUM cycle onward when no dead tuples are found.
When vacuum_cleanup_index_scale_factor is set to 0, index scans are never skipped during VACUUM cleanup.

Data Type	Default Value	Possible Values	Category
floating point	0.1	0 ~ 10000000000	segments; session; reload

vacuum_freeze_min_age

Specifies the minimum age (in transactions) that a transaction ID must reach before VACUUM replaces it with FrozenXID during a table scan.

Data Type	Default Value	Possible Values	Category
int	50000000	0 ~ 100000000000	segments; system; restart

vacuum_freeze_table_age

When a table's pg_class.relfrozenxid field reaches this age, VACUUM performs an aggressive scan.

Aggressive scan differs from regular VACUUM by visiting every page that might contain unfrozen XIDs or MXIDs, not just those with dead tuples.
Manual VACUUM can be run periodically before anti-wraparound autovacuum is triggered.

Data Type	Default Value	Possible Values	Category
int	150000000	0 ~ 200000000	segments; session; reload

vacuum_multixact_freeze_min_age

Specifies the cutoff age (in multitransactions) used by VACUUM to decide whether to replace a multitransaction ID with a newer transaction or multitransaction ID.

No forced autovacuum runs occur too close together.

Data Type	Default Value	Possible Values	Category
int	5000000	0 ~ 100000000	segments; session; reload

vacuum_multixact_freeze_table_age

If a table's pg_class.relminmxid field exceeds this age, VACUUM performs an aggressive scan.

Aggressive scan visits every page possibly containing unfrozen XIDs or MXIDs, unlike regular VACUUM, which scans only pages with dead tuples.
Manual VACUUM can be run periodically before anti-wraparound autovacuum is triggered.

Data Type	Default Value	Possible Values	Category
int	150000000	0 ~ 200000000	segments; session; reload

Locale and Formatting

client_encoding

Sets the client encoding (character set).

Defaults to the database encoding.

Data Type	Default Value	Possible Values	Category
string	SQL_ASCII	Supported character sets	master; session; reload

datestyle

Sets the display format for date and time values and rules for interpreting ambiguous date inputs.

For historical reasons, this variable has two parts: output format (ISO, Postgres, SQL, or German) and input/output order of year/month/day (DMY, MDY, or YMD). These can be set independently or together. Keywords Euro and European are synonyms for DMY; US, NonEuro, and NonEuropean are synonyms for MDY.
Default is ISO, MDY, but initdb initializes the setting based on the selected lc_time locale behavior.

Data Type	Default Value	Possible Values	Category
string	iso, mdy	,<year/month/day order>	master; system; restart

default_text_search_config

Selects the text search configuration used by text search functions that do not explicitly specify one.

Default is pg_catalog.simple, but initdb initializes it based on the selected lc_ctype locale if a matching configuration exists.

Data Type	Default Value	Possible Values	Category
string	pg_catalog.english	name of a text search configuration	master; session; reload

extra_float_digits

Adjusts the number of digits displayed for floating-point values, including float4, float8, and geometric types.

If set to 1 (default) or higher, floats are output in shortest precision format. The actual number of digits depends on the value, not the parameter.
float8 values require up to 17 digits; float4 values up to 9.
This format is fast and precise, preserving the original binary float exactly when parsed.
For backward compatibility, values up to 3 are allowed.
If set to 0 or negative, output is rounded to the given decimal precision.

Data Type	Default Value	Possible Values	Category
int	1	-15 ~ 3	master; session; reload

intervalstyle

Sets the display format for interval values.

sql_standard produces output compatible with SQL standard interval literals.
iso_8601 produces output matching ISO 8601 duration format.
intervalstyle may also affect interpretation of ambiguous interval inputs.

Data Type	Default Value	Possible Values	Category
enum	postgres	sql_standard / postgres / iso_8601 / postgres_verbose	master; session; reload

lc_messages

Sets the language for message display.

Available locales depend on the operating system — i.e., those listed by locale -a.
If empty (default), the value is inherited from the server environment in a system-dependent way.
On some systems, this locale category does not exist. The variable can still be set but has no effect. Translations may also be missing, resulting in English messages.
Only superusers can change this setting, as it affects both server logs and client messages. Incorrect values may reduce log readability.

Data Type	Default Value	Possible Values	Category
string		locales listed by `locale -a`	segments; system; restart; superuser

lc_monetary

Sets the locale for formatting monetary amounts, e.g., with to_char functions.

Available locales depend on the OS — i.e., those listed by locale -a.
Default is inherited from the server environment.

Data Type	Default Value	Possible Values	Category
string		locales listed by `locale -a`	segments; system; restart

lc_numeric

Sets the locale for formatting numbers, e.g., with to_char functions.

Available locales depend on the OS — i.e., those listed by locale -a.
Default is inherited from the server environment.

Data Type	Default Value	Possible Values	Category
string		locales listed by `locale -a`	segments; system; restart

lc_time

Sets the locale for formatting dates and times, e.g., with to_char functions.

Available locales depend on the OS — i.e., those listed by locale -a.
Default is inherited from the server environment.

Data Type	Default Value	Possible Values	Category
string		locales listed by `locale -a`	segments; system; restart

timezone

Sets the time zone for displaying and interpreting timestamps.

Default uses the system environment's time zone. See Time Zones for details.

Data Type	Default Value	Possible Values	Category
string	GMT	time zone abbreviations	segments; restart

timezone_abbreviations

Sets the set of time zone abbreviations accepted in datetime input.

Options are names of configuration files stored in $GPHOME/share/postgresql/timezonesets/. You can create custom files in this directory. Example: To use a file custom containing default zones plus WIB (Waktu Indonesia Barat):
1. Copy Default from $GPHOME/share/postgresql/timezonesets/ to custom. Add WIB time zone info to custom.
2. Copy custom to the $GPHOME/share/postgresql/timezonesets/ directory on the MXMaster and MXSegment hosts.
3. Set server parameter timezone_abbreviations to custom.
4. Reload the server configuration (mxstop -u).

Data Type	Default Value	Possible Values	Category
enum	Default	Default / Australia / India /	master; session; reload

Shared Library Preloading

Multiple settings allow preloading shared libraries into the server to add functionality or improve performance. For example, the $libdir/mylib setting may cause mylib.so (or mylib.sl on some platforms) to be preloaded from the standard library directory. These settings differ in when they take effect and required privileges.

YMatrix procedural language libraries can be preloaded using $libdir/plXXX syntax, where XXX is pgsql, perl, tcl, or python.

Only shared libraries specifically designed for YMatrix can be loaded this way. Each supported library contains a "magic block" for compatibility verification. Therefore, non-YMatrix libraries cannot be loaded this way. You may use OS tools like LD_PRELOAD to load them.

Refer to specific module documentation for recommended loading methods.

jit_provider

Specifies the name of the JIT provider library to use.

Can only be set at server startup.
If set to a non-existent library, JIT will be unavailable but no error occurs.
Allows JIT support to be installed separately from the main YMatrix package.

Data Type	Default Value	Category
string	llvmjit	segments; session; restart; superuser

--- SPLIT ---

local_preload_libraries

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

It contains a comma-separated list of library names, where each name is resolved in the same way as the LOAD command. Whitespace between entries is ignored. To include spaces or commas in a library name, enclose the name in double quotes.
This parameter takes effect only at the start of a connection. Subsequent changes have no effect. If a specified library is not found, the connection attempt will fail.
Any user can set this option. Because of this, libraries that can be loaded in this way are strictly limited to shared libraries located in the plugins subdirectory of the standard library directory (it is the database administrator's responsibility to ensure that only "safe" libraries are installed here).
Entries in local_preload_libraries can explicitly specify the full path to the directory, for example $libdir/plugins/mylib, or just 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 for debugging or performance measurement in specific sessions, without requiring an explicit LOAD command. Typically, this parameter is set using the client's PGOPTIONS environment variable or ALTER ROLE SET.
However, unless a module is specifically designed to be used this way by non-superusers, it is generally not recommended to use this setting. Instead, consider using session_preload_libraries.

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 beginning of a connection.

It contains a comma-separated list of library names, where each name is resolved in the same way as the LOAD command. Whitespace between entries is ignored. To include spaces or commas in a library name, enclose the name in double quotes.
This parameter takes effect only at the start of a connection. Subsequent changes 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 for debugging or performance measurement in specific sessions, without requiring an explicit LOAD command. For example, setting this parameter via ALTER ROLE SET enables auto_explain for all sessions under a given username.
This parameter can be changed without restarting the server (though changes apply only to new sessions). This makes it easier to add new modules, even when they need to be applied across all sessions.
Unlike shared_preload_libraries, preloading a library at session start offers no performance benefit over loading it when first used. However, this parameter has advantages when connection pooling is in use.

Data Type	Default Value	Setting Category
string		segments; session; reload; superuser

shared_preload_libraries

It contains a comma-separated list of library names, where each name is resolved in the same way as the LOAD command. Whitespace between entries is ignored. To include spaces or commas in a library name, enclose the name in double quotes.
This parameter can only be set at server startup. If a specified library is not found, the server will fail to start.
Some libraries require operations that can only occur during postmaster startup, such as allocating shared memory, reserving lightweight locks, or launching background workers. Such libraries must be loaded at server startup via this parameter.
Other libraries can also be preloaded. Preloading a shared library avoids the initialization overhead when the library is first used. However, each new server process will incur a small startup cost, even if it never uses the library. Therefore, it is recommended to use this parameter only for libraries used in most sessions.
Changing this parameter requires a server restart. Hence, it is not suitable for short-term debugging tasks; use session_preload_libraries instead.
Note: On Windows hosts, preloading a library at server startup does not reduce the time required to start each new server process — each process will reload the preloaded libraries. However, shared_preload_libraries remains useful on Windows for libraries that must perform actions at postmaster startup.

Data Type	Default Value	Setting Category
string		segments; system; restart

Other Defaults

dynamic_library_path

When loading dynamically loaded modules, if the filename specified in the CREATE FUNCTION or LOAD command does not include a directory component (i.e., the name contains no "/"), the system searches this path for the required file.

The value of dynamic_library_path must be a colon-separated list (or semicolon-separated on Windows) of absolute directory paths. If a list element begins with the special string $libdir, it is replaced with the compiled-in library directory of the PostgreSQL installation. This is where modules provided by the PostgreSQL distribution are installed (use pg_config --pkglibdir to find the name of this directory).
Example:
```
dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir'
```
Or on Windows:
```
dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
```
The default value of this parameter is $libdir. If set to an empty string, automatic path searching is disabled.
This parameter can be modified at runtime by superusers, but such changes last only until the end of the client connection. Therefore, this method should be reserved for development purposes.
We recommend setting this parameter in the postgresql.conf configuration file.

Data Type	Default Value	Valid Values	Setting Category
string	$libdir	Colon-separated list of absolute directory paths	segments; system; restart

← Previous

Connections and authentication

Error reporting and logging

English Русский 简体中文