pg_settings
pg_settings
is a system catalogue view which displays the current server configuration settings; additionally it can be updated, making it functionally the equivalent of a session-local SET
command.
pg_settings
was added in PostgreSQL 7.3.
Definition by PostgreSQL version
pg_settings (PostgreSQL 14)
View "pg_catalog.pg_settings" Column | Type | Collation | Nullable | Default -----------------+---------+-----------+----------+--------- name | text | | | setting | text | | | unit | text | | | category | text | | | short_desc | text | | | extra_desc | text | | | context | text | | | vartype | text | | | source | text | | | min_val | text | | | max_val | text | | | enumvals | text[] | | | boot_val | text | | | reset_val | text | | | sourcefile | text | | | sourceline | integer | | | pending_restart | boolean | | |
Documentation: pg_settings
pg_settings (PostgreSQL 13)
View "pg_catalog.pg_settings" Column | Type | Collation | Nullable | Default -----------------+---------+-----------+----------+--------- name | text | | | setting | text | | | unit | text | | | category | text | | | short_desc | text | | | extra_desc | text | | | context | text | | | vartype | text | | | source | text | | | min_val | text | | | max_val | text | | | enumvals | text[] | | | boot_val | text | | | reset_val | text | | | sourcefile | text | | | sourceline | integer | | | pending_restart | boolean | | |
Documentation: pg_settings
pg_settings (PostgreSQL 12)
View "pg_catalog.pg_settings" Column | Type | Collation | Nullable | Default -----------------+---------+-----------+----------+--------- name | text | | | setting | text | | | unit | text | | | category | text | | | short_desc | text | | | extra_desc | text | | | context | text | | | vartype | text | | | source | text | | | min_val | text | | | max_val | text | | | enumvals | text[] | | | boot_val | text | | | reset_val | text | | | sourcefile | text | | | sourceline | integer | | | pending_restart | boolean | | |
Documentation: pg_settings
pg_settings (PostgreSQL 11)
View "pg_catalog.pg_settings" Column | Type | Collation | Nullable | Default -----------------+---------+-----------+----------+--------- name | text | | | setting | text | | | unit | text | | | category | text | | | short_desc | text | | | extra_desc | text | | | context | text | | | vartype | text | | | source | text | | | min_val | text | | | max_val | text | | | enumvals | text[] | | | boot_val | text | | | reset_val | text | | | sourcefile | text | | | sourceline | integer | | | pending_restart | boolean | | |
Documentation: pg_settings
pg_settings (PostgreSQL 10)
View "pg_catalog.pg_settings" Column | Type | Collation | Nullable | Default -----------------+---------+-----------+----------+--------- name | text | | | setting | text | | | unit | text | | | category | text | | | short_desc | text | | | extra_desc | text | | | context | text | | | vartype | text | | | source | text | | | min_val | text | | | max_val | text | | | enumvals | text[] | | | boot_val | text | | | reset_val | text | | | sourcefile | text | | | sourceline | integer | | | pending_restart | boolean | | |
Documentation: pg_settings
pg_settings (PostgreSQL 9.6)
View "pg_catalog.pg_settings" Column | Type | Modifiers -----------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer | pending_restart | boolean |
Documentation: pg_settings
pg_settings (PostgreSQL 9.5)
View "pg_catalog.pg_settings" Column | Type | Modifiers -----------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer | pending_restart | boolean |
Documentation: pg_settings
pg_settings (PostgreSQL 9.4)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer |
Documentation: pg_settings
pg_settings (PostgreSQL 9.3)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer |
Documentation: pg_settings
pg_settings (PostgreSQL 9.2)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer |
Documentation: pg_settings
pg_settings (PostgreSQL 9.1)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer |
Documentation: pg_settings
pg_settings (PostgreSQL 9.0)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer |
Documentation: pg_settings
pg_settings (PostgreSQL 8.4)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer | View definition: SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings() a(name, setting, unit, category, short_desc, extra_desc, context, vartype, source, min_val, max_val, enumvals, boot_val, reset_val, sourcefile, sourceline); Rules: pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING pg_settings_u AS ON UPDATE TO pg_settings WHERE new.name = old.name DO SELECT set_config(old.name, new.setting, false) AS set_config
Documentation: pg_settings
pg_settings (PostgreSQL 8.3)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | View definition: SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text); Rules: pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING pg_settings_u AS ON UPDATE TO pg_settings WHERE new.name = old.name DO SELECT set_config(old.name, new.setting, false) AS set_config
Documentation: pg_settings
pg_settings (PostgreSQL 8.2)
View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | View definition: SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text); Rules: pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING pg_settings_u AS ON UPDATE TO pg_settings WHERE new.name = old.name DO SELECT set_config(old.name, new.setting, false) AS set_config
Documentation: pg_settings
Change history
- PostgreSQL 9.5
- column
pending_restart
added (commit a486e357)
- column
- PostgreSQL 8.4
- PostgreSQL 8.2
- column
unit
added (commit b517e653)
- column
- PostgreSQL 8.0
- following columns added (commit 66989aa2)
category
short_desc
extra_desc
- following columns added (commit 66989aa2)
- PostgreSQL 7.3
- added (commit 45e25445)
pg_settings
was originally available via the contrib module tablefunc
as function show_all_settings()
.
Source code
pg_settings
is derived from the internal function pg_show_all_settings()
(src/backend/catalog/system_views.sql):
CREATE VIEW pg_settings AS SELECT * FROM pg_show_all_settings() AS A; CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE new.name = old.name DO SELECT set_config(old.name, new.setting, 'f'); CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
Examples
postgres=# SELECT * FROM pg_settings WHERE name = 'port'; -[ RECORD 1 ]---+----------------------------------------------------- name | port setting | 5433 unit | category | Connections and Authentication / Connection Settings short_desc | Sets the TCP port the server listens on. extra_desc | context | postmaster vartype | integer source | configuration file min_val | 1 max_val | 65535 enumvals | boot_val | 5432 reset_val | 5433 sourcefile | /var/lib/pgsql/postgresql.local.conf sourceline | 3 pending_restart | f
References
- PostgreSQL documentation: pg_settings