pg_settings - PolarDB - Alibaba Cloud Documentation Center

The pg_settings view provides access to runtime parameters of the server.

Overview

The pg_settings view is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.

The pg_settings view contains the following columns:

Column name	Type	Description
name	text	The name of the runtime configuration parameter.
setting	text	The current value of the parameter.
unit	text	The implicit unit of the parameter.
category	text	The logical group of the parameter.
short_desc	text	The short description of the parameter.
extra_desc	text	A more detailed description of the parameters.
context	text	The context required to set the parameter value.
vartype	text	The parameter type. Valid values: `bool`, `enum`, `integer`, `real`, and `string`.
source	text	The source of the current parameter value.
min_val	text	The minimum allowed value of the parameter. This value is empty for non-numeric values.
max_val	text	The maximum allowed value of the parameter. This value is empty for non-numeric values.
enumvals	text[]	The allowed values for an enumeration parameter. This value is empty for non-numeric values.
boot_val	text	The parameter value that is assigned at server startup if the parameter is not modified afterwards.
reset_val	text	The value to which `RESET` resets the parameter in the current session.
sourcefile	text	The configuration file in which the current value is set. The value is empty if the value comes from sources other than configuration files, or when examined by a user who is not a superuser or a member of `pg_read_all_settings`. This value is used when using the `include` directives in configuration files.
sourceline	int4	The line number in the configuration file where the current value is set. The value is empty if the value comes from sources other than configuration files, or when examined by a user who is not a superuser or a member of `pg_read_all_settings`.
pending_restart	bool	`true` if the value is modified in the configuration file and a restart is required. Otherwise, the value is `false`.

Valid values for context:

internal: These settings cannot be changed directly. They reflect internally determined values. Some of the settings may be adjustable by rebuilding the server with different configuration options, or by changing the options of initdb.
postmaster: These settings can only be applied when the server starts. If you change these settings, you must restart the server. Values for these settings are usually stored in the postgresql.conf file, or passed on the command line when starting the server. Settings of any type that is lower than the context type can also be set at server start time.
sighup: Changes to these settings can be made in postgresql.conf and do not require a server restart. Send a SIGHUP signal to the postmaster to let it read postgresql.conf and apply the changes. The postmaster also forwards the SIGHUP signal to its child processes so that they all pick up the new value.
superuser-backend: Changes to these settings can be made in postgresql.conf and do not require a server restart. They can also be set for a particular session in the connection request packet (for example, via the PGOPTIONS environment variable of libpq), but only if the connecting user is a superuser. However, these settings do not change in a session after it is started. If you change the settings in postgresql.conf, send a SIGHUP signal to the postmaster to let it read postgresql.conf. The new values take effect only in sessions that are started afterwards.
backend: Changes to these settings can be made in postgresql.conf and do not require a server restart. They can also be set for a particular session in the connection request packet (for example, via the PGOPTIONS environment variable of libpq). Any user can make such changes for their session. However, these settings do not change in a session after it is started. If you change the settings in postgresql.conf, send a SIGHUP signal to the postmaster to let it to re-read postgresql.conf. The new values take effect only in sessions that are started afterwards.
superuser: These settings can be set from postgresql.conf, or within a session by using the SET command. Changes in postgresql.conf take effect in existing sessions only if no session-local value is configured by using the SET command.
user: These settings can be set from postgresql.conf, or within a session by using the SET command. Any user is allowed to change their session-local value. Changes in postgresql.conf take effect in existing sessions only if no session-local value is configured by using the SET.

Note

You cannot insert data to or delete data from the pg_settings view. However, you can update the view. An UPDATE command that is applied to a row of pg_settings is equivalent to executing the SET command on that parameter. The change only affects the value used by the current session. If an UPDATE command is issued in a transaction that is later aborted, the effects of the UPDATE command disappear when the transaction is rolled back. Once the surrounding transaction is committed, the effects persist until the end of the session, unless the change is overridden by another UPDATE or SET command.