public inbox for [email protected]help / color / mirror / Atom feed
[9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? 9+ messages / 4 participants [nested] [flat]
* [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-05-21 19:00 David G Johnston <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: David G Johnston @ 2014-05-21 19:00 UTC (permalink / raw) To: pgsql-docs Section 18.1.3 is named "18.1.3. Other Ways to Set Parameters" yet does not reference the ability to use "set_config(...)" to make changes to configuration parameters. Something like (as a final sentence): An alternative to the SQL "SET" command is to use the [link]set_config[link] function. Also, I would suggest modifying the function definitions in table 9-58 to include the data types: current_setting(setting_name text) set_config(setting_name text, new_value text, is_local boolean) David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/9-3-Should-we-mention-set-config-in-18-1-3-in-Server-Configu... Sent from the PostgreSQL - docs mailing list archive at Nabble.com. -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-05-21 20:37 Tom Lane <[email protected]> parent: David G Johnston <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Tom Lane @ 2014-05-21 20:37 UTC (permalink / raw) To: David G Johnston <[email protected]>; +Cc: pgsql-docs David G Johnston <[email protected]> writes: > Section 18.1.3 is named "18.1.3. Other Ways to Set Parameters" yet does not > reference the ability to use "set_config(...)" to make changes to > configuration parameters. Hm, yeah, seems like an oversight. Another thing I've never been terribly happy about is that pg_settings is mentioned only in the next sub-section. Could we merge that sub-section with 18.1.3? regards, tom lane -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-06-08 14:49 David Johnston <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: David Johnston @ 2014-06-08 14:49 UTC (permalink / raw) To: pgsql-docs Re-sending to hopefully avoid admin moderation rule. Fixing top-post as well. On Wed, May 21, 2014 at 4:37 PM, Tom Lane <[email protected]> wrote: > >> David G Johnston <[email protected]> writes: >> > Section 18.1.3 is named "18.1.3. Other Ways to Set Parameters" yet does >> not >> > reference the ability to use "set_config(...)" to make changes to >> > configuration parameters. >> >> Hm, yeah, seems like an oversight. >> >> Another thing I've never been terribly happy about is that pg_settings is >> mentioned only in the next sub-section. Could we merge that sub-section >> with 18.1.3? >> >> regards, tom lane >> > > > I am presuming that posting a doc-only patch here instead of -hackers is > acceptable. > > So I finally decided to setup a development environment and generate > actual patches. I have few in mind but I thought that these two changes > might be quick to implement. I thought wrong - mostly due to my desire to > improve things and not just "add and merge" some content. > > I focused my effort on 18.1.1 to 18.1.4 though the actual coverage of > sections 2-4 has changed considerably. I decided not to minimize > white-space changes in that region since because of the re-organization git > was unable, especially in a couple of instances, to match up before and > after anyway. The file itself does not have a consistent format and so I > just applied 2-space indentations at each level. I plan to go back and do > polishing but I first want to make sure that the end-user product is > acceptable. > > I've only hit this one file for the moment but figure on reviewing all of > the relevant cross-references for tweaks and consistency. I already did so > in passing and would not expect anything as invasive as what I am proposing > here - so if others feel these changes have merit I will plan on taking any > ideas and put forth a v2 patch that will hopefully be ready-to-commit. > > Thank you in advance for any attention and education you can provide. > > David J. > diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 697cf99..6ac48d6 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -11,7 +11,7 @@ <para> There are many configuration parameters that affect the behavior of the database system. In the first section of this chapter, we - describe how to set configuration parameters. The subsequent sections + describe how to interact with configuration parameters. The subsequent sections discuss each parameter in detail. </para> @@ -24,46 +24,103 @@ <para> All parameter names are case-insensitive. Every parameter takes a value of one of five types: Boolean, integer, floating point, - string or enum. Boolean values can be written as <literal>on</literal>, - <literal>off</literal>, <literal>true</literal>, - <literal>false</literal>, <literal>yes</literal>, - <literal>no</literal>, <literal>1</literal>, <literal>0</literal> - (all case-insensitive) or any unambiguous prefix of these. + string or enum. </para> + <itemizedlist> + + <listitem> <para> - Some settings specify a memory or time value. Each of these has an - implicit unit, which is either kilobytes, blocks (typically eight - kilobytes), milliseconds, seconds, or minutes. Default units can be - found by referencing <structname>pg_settings</>.<structfield>unit</>. - For convenience, - a different unit can also be specified explicitly. Valid memory units - are <literal>kB</literal> (kilobytes), <literal>MB</literal> - (megabytes), <literal>GB</literal> (gigabytes), and <literal>TB</literal> (terabytes); valid time units - are <literal>ms</literal> (milliseconds), <literal>s</literal> - (seconds), <literal>min</literal> (minutes), <literal>h</literal> - (hours), and <literal>d</literal> (days). Note that the multiplier - for memory units is 1024, not 1000. + <emphasis>Boolean</emphasis>: Values can be written as + <literal>on</literal>, + <literal>off</literal>, + <literal>true</literal>, + <literal>false</literal>, + <literal>yes</literal>, + <literal>no</literal>, + <literal>1</literal>, + <literal>0</literal> + (all case-insensitive) or any unambiguous prefix of these. </para> + </listitem> + <listitem> <para> - Parameters of type <quote>enum</> are specified in the same way as string - parameters, but are restricted to a limited set of values. The allowed - values can be found - from <structname>pg_settings</>.<structfield>enumvals</>. - Enum parameter values are case-insensitive. + <emphasis>String</emphasis>: + Enclose the value in single-quote. Values are case-insensitive. + If multiple values are allowed separate them with commas. </para> - </sect2> + </listitem> + + <listitem> + <para> + <emphasis>Numeric</emphasis> + (integer and floating point): Do not use single-quotes + (unless otherwise required) + or thousand separators. + Typically memory or time related - see comments in that + section for detail. + </para> + </listitem> - <sect2 id="config-setting-configuration-file"> - <title>Setting Parameters via the Configuration File</title> + <listitem> + <para> + <emphasis>Numeric or String with Unit</emphasis>: Memory & Time. + Both of these have an implicit unit, which is either kilobytes, + blocks (typically eight kilobytes), milliseconds, seconds, or minutes. + An numeric value will use the default, which can be + found by referencing <structname>pg_settings</>.<structfield>unit</>. + For convenience, a different unit can also be specified explicitly + via a string value. It is case-sensitive and + may include a space between the value and the unit + <itemizedlist> + <listitem> + <para> + Valid memory units are + <literal>kB</literal> (kilobytes), + <literal>MB</literal> (megabytes), + <literal>GB</literal> (gigabytes), + and <literal>TB</literal> (terabytes). + The multiplier for memory units is 1024, not 1000. + </para> + </listitem> + <listitem> + <para> + Valid time units are + <literal>ms</literal> (milliseconds), + <literal>s</literal> (seconds), + <literal>min</literal> (minutes), + <literal>h</literal> (hours), + and <literal>d</literal> (days). + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + <listitem> <para> - One way to set these parameters is to edit the file - <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, - which is normally kept in the data directory. (A default copy is - installed there when the database cluster directory is - initialized.) An example of what this file might look like is: + <emphasis><quote>enum</></emphasis>: These specified in the same way as string + parameters, but are restricted to a limited set of values that can be queried + from <structname>pg_settings</>.<structfield>enumvals</>: +<programlisting> +SELECT name, setting, enumvals FROM pg_settings WHERE enumvals IS NOT NULL; +</programlisting> + Enum parameter values are case-insensitive. + </para> + </listitem> + </itemizedlist> + </sect2> + + <sect2 id="config-setting-configuration-file"> + <title>Parameter Interaction via Configuration File</title> + + <para> + The primary way to set these parameters is to edit the file + <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, + which is normally kept in the data directory. (A default copy is + installed there when the database cluster directory is + initialized.) An example of what this file might look like is: <programlisting> # This is a comment log_connections = yes @@ -71,129 +128,209 @@ log_destination = 'syslog' search_path = '"$user", public' shared_buffers = 128MB </programlisting> - One parameter is specified per line. The equal sign between name and - value is optional. Whitespace is insignificant and blank lines are - ignored. Hash marks (<literal>#</literal>) designate the remainder of the - line as a comment. Parameter values that are not simple identifiers or - numbers must be single-quoted. To embed a single quote in a parameter - value, write either two quotes (preferred) or backslash-quote. - </para> + One parameter is specified per line. The equal sign between name and + value is optional. Whitespace is insignificant and blank lines are + ignored. Hash marks (<literal>#</literal>) designate the remainder of the + line as a comment. Parameter values that are not simple identifiers or + numbers must be single-quoted. To embed a single quote in a parameter + value, write either two quotes (preferred) or backslash-quote. + </para> - <para> - <indexterm> - <primary>SIGHUP</primary> - </indexterm> - The configuration file is reread whenever the main server process - receives a <systemitem>SIGHUP</> signal; this is most easily done by - running <literal>pg_ctl reload</> from the command-line or by calling - the SQL function <function>pg_reload_conf()</function>. The main - server process - also propagates this signal to all currently running server - processes so that existing sessions also get the new - value. Alternatively, you can send the signal to a single server - process directly. Some parameters can only be set at server start; - any changes to their entries in the configuration file will be ignored - until the server is restarted. Invalid parameter settings in the - configuration file are likewise ignored (but logged) during - <systemitem>SIGHUP</> processing. - </para> - </sect2> + <para> + Parameters set in this way provide the global default value for the cluster. + The setting actually seen by the connecting user or issued statement + will be this value unless it is overridden. + The next sections describe ways in which the administrator + or user can override these defaults. + </para> - <sect2 id="config-setting-other-methods"> - <title>Other Ways to Set Parameters</title> + <para> + <indexterm> + <primary>SIGHUP</primary> + </indexterm> + The configuration file is reread whenever the main server process + receives a <systemitem>SIGHUP</> signal; this is most easily done by + running <literal>pg_ctl reload</> from the command-line or by calling + the SQL function <function>pg_reload_conf()</function>. The main + server process + also propagates this signal to all currently running server + processes so that existing sessions also get the new + value. Alternatively, you can send the signal to a single server + process directly. Some parameters can only be set at server start; + any changes to their entries in the configuration file will be ignored + until the server is restarted. Invalid parameter settings in the + configuration file are likewise ignored (but logged) during + <systemitem>SIGHUP</> processing. + </para> + </sect2> - <para> - A second way to set these configuration parameters is to give them - as a command-line option to the <command>postgres</command> command, - such as: -<programlisting> -postgres -c log_connections=yes -c log_destination='syslog' -</programlisting> - Command-line options override any conflicting settings in - <filename>postgresql.conf</filename>. Note that this means you won't - be able to change the value on-the-fly by editing - <filename>postgresql.conf</filename>, so while the command-line - method might be convenient, it can cost you flexibility later. - </para> + <sect2 id="config-setting-sql-command-interaction"> + <title>Parameter Interaction via SQL</title> + <para> + <productname>PostgreSQL</productname> provides three SQL commands + to establish configuration defaults + that override those configured globally. + The evaluation of these defaults occurs at the beginning of a + new session, upon the user issuing <xref linkend="SQL-DISCARD">, + or if the server forces the session to reload it configuration after a + <systemitem>SIGHUP</systemitem> + </para> + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-ALTERSYSTEM"> command provides + an SQL-accessible means + to change the global defaults. Since the server must + be running to execute SQL + the timing of when the actual value takes effect depends + on the variable being + changed - but in no case is the current session affected + nor will any change take + effect before the next configuration + reload (<systemitem>SIGHUP</>) by the server. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterdatabase"> command allows the database + administrator to override global settings on a per-database basis. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterrole"> command allows the database administrator + to override both global and per-database settings with user-specific values. + </para> + </listitem> + </itemizedlist> - <para> - Occasionally it is useful to give a command line option to - one particular session only. The environment variable - <envar>PGOPTIONS</envar> can be used for this purpose on the - client side: + <para> + Once a client connects to the database PostgreSQL provides two + additional SQL commands + to interact with session-local system configuration. + Both of these commands have equivalent + system administration functions. + </para> + + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-SHOW"> + command allows inspection of the current value of all parameters. + The corresponding function + is <function>current_setting(setting_name text)</function>. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="SQL-SET"> + command allows modification of the current value some parameters. + The corresponding function is + <function>set_config(setting_name, new_value, is_local)</function>. + </para> + </listitem> + </itemizedlist> + + <para> + Both <emphasis>SELECT</> and <emphasis>UPDATE</> can be issued against the + virtual table <structname>pg_settings</> to view and + affect the session-local configuration. + Its definition can be found in <xref linkend="view-pg-settings">. + </para> + + <itemizedlist> + <listitem> + <para> + <xref linkend="SQL-SELECT">-ing against this relation + is the equivalent of issuing + SHOW but provides considerably more detail as well as + allowing for joining against other relations and specifying filter criteria. + </para> + </listitem> + <listitem> + <para> + <xref linkend="SQL-UPDATE">-ing against this relation, + specifically the <structname>setting</> column + is the equivalent of issuing SET though all values must be single-quoted. + </para> + <para> + Note that the eqivalent of <programlisting> -env PGOPTIONS='-c geqo=off' psql +SET configuration_parameter TO DEFAULT; +</> + would be: +<programlisting> +UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter'; </programlisting> - (This works for any <application>libpq</>-based client application, not - just <application>psql</application>.) Note that this won't work for - parameters that are fixed when the server is started or that must be - specified in <filename>postgresql.conf</filename>. - </para> + </para> + </listitem> + </itemizedlist> - <para> - Furthermore, it is possible to assign a set of parameter settings to - a user or a database. Whenever a session is started, the default - settings for the user and database involved are loaded. The - commands <xref linkend="sql-alterrole"> - and <xref linkend="sql-alterdatabase">, - respectively, are used to configure these settings. Per-database - settings override anything received from the - <command>postgres</command> command-line or the configuration - file, and in turn are overridden by per-user settings; both are - overridden by per-session settings. - </para> + </sect2> - <para> - Some parameters can be changed in individual <acronym>SQL</acronym> - sessions with the <xref linkend="SQL-SET"> - command, for example: -<screen> -SET ENABLE_SEQSCAN TO OFF; -</screen> - If <command>SET</> is allowed, it overrides all other sources of - values for the parameter. Some parameters cannot be changed via - <command>SET</command>: for example, if they control behavior that - cannot be changed without restarting the entire - <productname>PostgreSQL</productname> server. Also, some parameters - require superuser permission to change via <command>SET</command> or - <command>ALTER</>. - </para> + <sect2> + <title>Parameter Interaction via Shell</title> + <para> + In addition to setting global defaults or attaching overrides + at the database or role scope + , you may choose to provide them to + <productname>PostgreSQL</productname> via shell facilities. + Both the server and <application>libpq</> client library + have defined ways to accept + parameter values via the shell. + </para> + <itemizedlist> - <para> - Another way to change configuration parameters persistently is by - use of <xref linkend="SQL-ALTERSYSTEM"> - command, for example: -<screen> -ALTER SYSTEM SET checkpoint_timeout TO 600; -</screen> - This command will allow users to change values persistently - through SQL command. The values will be effective after reload of server configuration - (<acronym>SIGHUP</>) or server startup. The effect of this command is similar to when - user manually changes values in <filename>postgresql.conf</filename>. - </para> - </sect2> + <listitem> + <para> + On the <emphasis>server</emphasis>, command-line options can passed to the + <command>postgres</command> command directly via the "-c" parameter. +<programlisting> +postgres -c log_connections=yes -c log_destination='syslog' +</programlisting> + Settings provided this way override those resolved globally + (via postgresql.conf or ALTER SYSTEM) + but are otherwise treated as being global for the purpose of + database and role overriding. + </para> - <sect2 id="config-setting-examining"> - <title>Examining Parameter Settings</title> + <para> + Typically, a production system will be adminstered via its + <filename>postgresql.conf</filename> file. + Use of this mechanism is suggested only for development and testing. + </para> + </listitem> - <para> - The <xref linkend="SQL-SHOW"> - command allows inspection of the current values of all parameters. - </para> + <listitem> + <para> + On the <emphasis>libpq-client</emphasis>, command-line options are + specified using the + <envar>PGOPTIONS</envar> environment variable. + Upon connecting to a server the contents of this variable are sent + to the server as if they were being + executed via a SQL <xref linkend="SQL-SET"> at the beginning of the session. + </para> - <para> - The virtual table <structname>pg_settings</structname> also allows - displaying and updating session run-time parameters; see <xref - linkend="view-pg-settings"> for details and a description of the - different variable types and when they can be changed. - <structname>pg_settings</structname> is equivalent to <command>SHOW</> - and <command>SET</>, but can be more convenient - to use because it can be joined with other tables, or selected from using - any desired selection condition. It also contains more information about - each parameter than is available from <command>SHOW</>. - </para> + <para> + However, the format for <envar>PGOPTIONS</envar> is similar to that provided when launching + <command>postgres</command> command. + Specifically, the '-c' flag specification is part of the value. +<programlisting> +env PGOPTIONS="-c geqo=off -c statement_timeout='5 min'" psql +</programlisting> + </para> - </sect2> + <para> + Other clients and libraries may provide their own mechanisms, + via the shell or otherwise, that allow the user + to setup the session configuration without requiring the user + to issue SQL commands. Please see their documentation + for details. + </para> + </listitem> + </itemizedlist> + + </sect2> <sect2 id="config-includes"> <title>Configuration File Includes</title> -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs Attachments: [text/plain] config_sgml_18_1_1-4_v1.diff (19.2K, 3-config_sgml_18_1_1-4_v1.diff) download | inline diff: diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 697cf99..6ac48d6 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -11,7 +11,7 @@ <para> There are many configuration parameters that affect the behavior of the database system. In the first section of this chapter, we - describe how to set configuration parameters. The subsequent sections + describe how to interact with configuration parameters. The subsequent sections discuss each parameter in detail. </para> @@ -24,46 +24,103 @@ <para> All parameter names are case-insensitive. Every parameter takes a value of one of five types: Boolean, integer, floating point, - string or enum. Boolean values can be written as <literal>on</literal>, - <literal>off</literal>, <literal>true</literal>, - <literal>false</literal>, <literal>yes</literal>, - <literal>no</literal>, <literal>1</literal>, <literal>0</literal> - (all case-insensitive) or any unambiguous prefix of these. + string or enum. </para> + <itemizedlist> + + <listitem> <para> - Some settings specify a memory or time value. Each of these has an - implicit unit, which is either kilobytes, blocks (typically eight - kilobytes), milliseconds, seconds, or minutes. Default units can be - found by referencing <structname>pg_settings</>.<structfield>unit</>. - For convenience, - a different unit can also be specified explicitly. Valid memory units - are <literal>kB</literal> (kilobytes), <literal>MB</literal> - (megabytes), <literal>GB</literal> (gigabytes), and <literal>TB</literal> (terabytes); valid time units - are <literal>ms</literal> (milliseconds), <literal>s</literal> - (seconds), <literal>min</literal> (minutes), <literal>h</literal> - (hours), and <literal>d</literal> (days). Note that the multiplier - for memory units is 1024, not 1000. + <emphasis>Boolean</emphasis>: Values can be written as + <literal>on</literal>, + <literal>off</literal>, + <literal>true</literal>, + <literal>false</literal>, + <literal>yes</literal>, + <literal>no</literal>, + <literal>1</literal>, + <literal>0</literal> + (all case-insensitive) or any unambiguous prefix of these. </para> + </listitem> + <listitem> <para> - Parameters of type <quote>enum</> are specified in the same way as string - parameters, but are restricted to a limited set of values. The allowed - values can be found - from <structname>pg_settings</>.<structfield>enumvals</>. - Enum parameter values are case-insensitive. + <emphasis>String</emphasis>: + Enclose the value in single-quote. Values are case-insensitive. + If multiple values are allowed separate them with commas. </para> - </sect2> + </listitem> + + <listitem> + <para> + <emphasis>Numeric</emphasis> + (integer and floating point): Do not use single-quotes + (unless otherwise required) + or thousand separators. + Typically memory or time related - see comments in that + section for detail. + </para> + </listitem> - <sect2 id="config-setting-configuration-file"> - <title>Setting Parameters via the Configuration File</title> + <listitem> + <para> + <emphasis>Numeric or String with Unit</emphasis>: Memory & Time. + Both of these have an implicit unit, which is either kilobytes, + blocks (typically eight kilobytes), milliseconds, seconds, or minutes. + An numeric value will use the default, which can be + found by referencing <structname>pg_settings</>.<structfield>unit</>. + For convenience, a different unit can also be specified explicitly + via a string value. It is case-sensitive and + may include a space between the value and the unit + <itemizedlist> + <listitem> + <para> + Valid memory units are + <literal>kB</literal> (kilobytes), + <literal>MB</literal> (megabytes), + <literal>GB</literal> (gigabytes), + and <literal>TB</literal> (terabytes). + The multiplier for memory units is 1024, not 1000. + </para> + </listitem> + <listitem> + <para> + Valid time units are + <literal>ms</literal> (milliseconds), + <literal>s</literal> (seconds), + <literal>min</literal> (minutes), + <literal>h</literal> (hours), + and <literal>d</literal> (days). + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + <listitem> <para> - One way to set these parameters is to edit the file - <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, - which is normally kept in the data directory. (A default copy is - installed there when the database cluster directory is - initialized.) An example of what this file might look like is: + <emphasis><quote>enum</></emphasis>: These specified in the same way as string + parameters, but are restricted to a limited set of values that can be queried + from <structname>pg_settings</>.<structfield>enumvals</>: +<programlisting> +SELECT name, setting, enumvals FROM pg_settings WHERE enumvals IS NOT NULL; +</programlisting> + Enum parameter values are case-insensitive. + </para> + </listitem> + </itemizedlist> + </sect2> + + <sect2 id="config-setting-configuration-file"> + <title>Parameter Interaction via Configuration File</title> + + <para> + The primary way to set these parameters is to edit the file + <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, + which is normally kept in the data directory. (A default copy is + installed there when the database cluster directory is + initialized.) An example of what this file might look like is: <programlisting> # This is a comment log_connections = yes @@ -71,129 +128,209 @@ log_destination = 'syslog' search_path = '"$user", public' shared_buffers = 128MB </programlisting> - One parameter is specified per line. The equal sign between name and - value is optional. Whitespace is insignificant and blank lines are - ignored. Hash marks (<literal>#</literal>) designate the remainder of the - line as a comment. Parameter values that are not simple identifiers or - numbers must be single-quoted. To embed a single quote in a parameter - value, write either two quotes (preferred) or backslash-quote. - </para> + One parameter is specified per line. The equal sign between name and + value is optional. Whitespace is insignificant and blank lines are + ignored. Hash marks (<literal>#</literal>) designate the remainder of the + line as a comment. Parameter values that are not simple identifiers or + numbers must be single-quoted. To embed a single quote in a parameter + value, write either two quotes (preferred) or backslash-quote. + </para> - <para> - <indexterm> - <primary>SIGHUP</primary> - </indexterm> - The configuration file is reread whenever the main server process - receives a <systemitem>SIGHUP</> signal; this is most easily done by - running <literal>pg_ctl reload</> from the command-line or by calling - the SQL function <function>pg_reload_conf()</function>. The main - server process - also propagates this signal to all currently running server - processes so that existing sessions also get the new - value. Alternatively, you can send the signal to a single server - process directly. Some parameters can only be set at server start; - any changes to their entries in the configuration file will be ignored - until the server is restarted. Invalid parameter settings in the - configuration file are likewise ignored (but logged) during - <systemitem>SIGHUP</> processing. - </para> - </sect2> + <para> + Parameters set in this way provide the global default value for the cluster. + The setting actually seen by the connecting user or issued statement + will be this value unless it is overridden. + The next sections describe ways in which the administrator + or user can override these defaults. + </para> - <sect2 id="config-setting-other-methods"> - <title>Other Ways to Set Parameters</title> + <para> + <indexterm> + <primary>SIGHUP</primary> + </indexterm> + The configuration file is reread whenever the main server process + receives a <systemitem>SIGHUP</> signal; this is most easily done by + running <literal>pg_ctl reload</> from the command-line or by calling + the SQL function <function>pg_reload_conf()</function>. The main + server process + also propagates this signal to all currently running server + processes so that existing sessions also get the new + value. Alternatively, you can send the signal to a single server + process directly. Some parameters can only be set at server start; + any changes to their entries in the configuration file will be ignored + until the server is restarted. Invalid parameter settings in the + configuration file are likewise ignored (but logged) during + <systemitem>SIGHUP</> processing. + </para> + </sect2> - <para> - A second way to set these configuration parameters is to give them - as a command-line option to the <command>postgres</command> command, - such as: -<programlisting> -postgres -c log_connections=yes -c log_destination='syslog' -</programlisting> - Command-line options override any conflicting settings in - <filename>postgresql.conf</filename>. Note that this means you won't - be able to change the value on-the-fly by editing - <filename>postgresql.conf</filename>, so while the command-line - method might be convenient, it can cost you flexibility later. - </para> + <sect2 id="config-setting-sql-command-interaction"> + <title>Parameter Interaction via SQL</title> + <para> + <productname>PostgreSQL</productname> provides three SQL commands + to establish configuration defaults + that override those configured globally. + The evaluation of these defaults occurs at the beginning of a + new session, upon the user issuing <xref linkend="SQL-DISCARD">, + or if the server forces the session to reload it configuration after a + <systemitem>SIGHUP</systemitem> + </para> + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-ALTERSYSTEM"> command provides + an SQL-accessible means + to change the global defaults. Since the server must + be running to execute SQL + the timing of when the actual value takes effect depends + on the variable being + changed - but in no case is the current session affected + nor will any change take + effect before the next configuration + reload (<systemitem>SIGHUP</>) by the server. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterdatabase"> command allows the database + administrator to override global settings on a per-database basis. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterrole"> command allows the database administrator + to override both global and per-database settings with user-specific values. + </para> + </listitem> + </itemizedlist> - <para> - Occasionally it is useful to give a command line option to - one particular session only. The environment variable - <envar>PGOPTIONS</envar> can be used for this purpose on the - client side: + <para> + Once a client connects to the database PostgreSQL provides two + additional SQL commands + to interact with session-local system configuration. + Both of these commands have equivalent + system administration functions. + </para> + + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-SHOW"> + command allows inspection of the current value of all parameters. + The corresponding function + is <function>current_setting(setting_name text)</function>. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="SQL-SET"> + command allows modification of the current value some parameters. + The corresponding function is + <function>set_config(setting_name, new_value, is_local)</function>. + </para> + </listitem> + </itemizedlist> + + <para> + Both <emphasis>SELECT</> and <emphasis>UPDATE</> can be issued against the + virtual table <structname>pg_settings</> to view and + affect the session-local configuration. + Its definition can be found in <xref linkend="view-pg-settings">. + </para> + + <itemizedlist> + <listitem> + <para> + <xref linkend="SQL-SELECT">-ing against this relation + is the equivalent of issuing + SHOW but provides considerably more detail as well as + allowing for joining against other relations and specifying filter criteria. + </para> + </listitem> + <listitem> + <para> + <xref linkend="SQL-UPDATE">-ing against this relation, + specifically the <structname>setting</> column + is the equivalent of issuing SET though all values must be single-quoted. + </para> + <para> + Note that the eqivalent of <programlisting> -env PGOPTIONS='-c geqo=off' psql +SET configuration_parameter TO DEFAULT; +</> + would be: +<programlisting> +UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter'; </programlisting> - (This works for any <application>libpq</>-based client application, not - just <application>psql</application>.) Note that this won't work for - parameters that are fixed when the server is started or that must be - specified in <filename>postgresql.conf</filename>. - </para> + </para> + </listitem> + </itemizedlist> - <para> - Furthermore, it is possible to assign a set of parameter settings to - a user or a database. Whenever a session is started, the default - settings for the user and database involved are loaded. The - commands <xref linkend="sql-alterrole"> - and <xref linkend="sql-alterdatabase">, - respectively, are used to configure these settings. Per-database - settings override anything received from the - <command>postgres</command> command-line or the configuration - file, and in turn are overridden by per-user settings; both are - overridden by per-session settings. - </para> + </sect2> - <para> - Some parameters can be changed in individual <acronym>SQL</acronym> - sessions with the <xref linkend="SQL-SET"> - command, for example: -<screen> -SET ENABLE_SEQSCAN TO OFF; -</screen> - If <command>SET</> is allowed, it overrides all other sources of - values for the parameter. Some parameters cannot be changed via - <command>SET</command>: for example, if they control behavior that - cannot be changed without restarting the entire - <productname>PostgreSQL</productname> server. Also, some parameters - require superuser permission to change via <command>SET</command> or - <command>ALTER</>. - </para> + <sect2> + <title>Parameter Interaction via Shell</title> + <para> + In addition to setting global defaults or attaching overrides + at the database or role scope + , you may choose to provide them to + <productname>PostgreSQL</productname> via shell facilities. + Both the server and <application>libpq</> client library + have defined ways to accept + parameter values via the shell. + </para> + <itemizedlist> - <para> - Another way to change configuration parameters persistently is by - use of <xref linkend="SQL-ALTERSYSTEM"> - command, for example: -<screen> -ALTER SYSTEM SET checkpoint_timeout TO 600; -</screen> - This command will allow users to change values persistently - through SQL command. The values will be effective after reload of server configuration - (<acronym>SIGHUP</>) or server startup. The effect of this command is similar to when - user manually changes values in <filename>postgresql.conf</filename>. - </para> - </sect2> + <listitem> + <para> + On the <emphasis>server</emphasis>, command-line options can passed to the + <command>postgres</command> command directly via the "-c" parameter. +<programlisting> +postgres -c log_connections=yes -c log_destination='syslog' +</programlisting> + Settings provided this way override those resolved globally + (via postgresql.conf or ALTER SYSTEM) + but are otherwise treated as being global for the purpose of + database and role overriding. + </para> - <sect2 id="config-setting-examining"> - <title>Examining Parameter Settings</title> + <para> + Typically, a production system will be adminstered via its + <filename>postgresql.conf</filename> file. + Use of this mechanism is suggested only for development and testing. + </para> + </listitem> - <para> - The <xref linkend="SQL-SHOW"> - command allows inspection of the current values of all parameters. - </para> + <listitem> + <para> + On the <emphasis>libpq-client</emphasis>, command-line options are + specified using the + <envar>PGOPTIONS</envar> environment variable. + Upon connecting to a server the contents of this variable are sent + to the server as if they were being + executed via a SQL <xref linkend="SQL-SET"> at the beginning of the session. + </para> - <para> - The virtual table <structname>pg_settings</structname> also allows - displaying and updating session run-time parameters; see <xref - linkend="view-pg-settings"> for details and a description of the - different variable types and when they can be changed. - <structname>pg_settings</structname> is equivalent to <command>SHOW</> - and <command>SET</>, but can be more convenient - to use because it can be joined with other tables, or selected from using - any desired selection condition. It also contains more information about - each parameter than is available from <command>SHOW</>. - </para> + <para> + However, the format for <envar>PGOPTIONS</envar> is similar to that provided when launching + <command>postgres</command> command. + Specifically, the '-c' flag specification is part of the value. +<programlisting> +env PGOPTIONS="-c geqo=off -c statement_timeout='5 min'" psql +</programlisting> + </para> - </sect2> + <para> + Other clients and libraries may provide their own mechanisms, + via the shell or otherwise, that allow the user + to setup the session configuration without requiring the user + to issue SQL commands. Please see their documentation + for details. + </para> + </listitem> + </itemizedlist> + + </sect2> <sect2 id="config-includes"> <title>Configuration File Includes</title> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-06-12 15:32 David Johnston <[email protected]> parent: David Johnston <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: David Johnston @ 2014-06-12 15:32 UTC (permalink / raw) To: pgsql-docs; [email protected] <[email protected]> On Sun, Jun 8, 2014 at 10:49 AM, David Johnston <[email protected]> wrote: > > On Wed, May 21, 2014 at 4:37 PM, Tom Lane <[email protected]> wrote: >> >>> David G Johnston <[email protected]> writes: >>> > Section 18.1.3 is named "18.1.3. Other Ways to Set Parameters" yet >>> does not >>> > reference the ability to use "set_config(...)" to make changes to >>> > configuration parameters. >>> >>> Hm, yeah, seems like an oversight. >>> >>> Another thing I've never been terribly happy about is that pg_settings is >>> mentioned only in the next sub-section. Could we merge that sub-section >>> with 18.1.3? >>> >>> regards, tom lane >>> >> >> > >> I am presuming that posting a doc-only patch here instead of -hackers is >> acceptable. >> >> So I finally decided to setup a development environment and generate >> actual patches. I have few in mind but I thought that these two changes >> might be quick to implement. I thought wrong - mostly due to my desire to >> improve things and not just "add and merge" some content. >> >> I focused my effort on 18.1.1 to 18.1.4 though the actual coverage of >> sections 2-4 has changed considerably. I decided not to minimize >> white-space changes in that region since because of the re-organization git >> was unable, especially in a couple of instances, to match up before and >> after anyway. The file itself does not have a consistent format and so I >> just applied 2-space indentations at each level. I plan to go back and do >> polishing but I first want to make sure that the end-user product is >> acceptable. >> >> I've only hit this one file for the moment but figure on reviewing all of >> the relevant cross-references for tweaks and consistency. I already did so >> in passing and would not expect anything as invasive as what I am proposing >> here - so if others feel these changes have merit I will plan on taking any >> ideas and put forth a v2 patch that will hopefully be ready-to-commit. >> >> Thank you in advance for any attention and education you can provide. >> >> David J. >> > > > Updated the attached PDF (but not the patch) to include a suggested wording to address floating point number rounding and zero treatment. "disabling log_rotation_age feature." http://www.postgresql.org/message-id/[email protected] This addition still needs some work as well as the patch as a whole (see my comments above). The attached PDF is the page that results from "make html"; the original patch has been re-attached as well and -hackers copied given the lack of response on -doc. I am hoping this change would be something that could be included in 9.4; I believe the enhancements would be most beneficial to newcomers who are just getting started with PostgreSQL and would be using 9.4 as their starting point. David J. diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 697cf99..6ac48d6 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -11,7 +11,7 @@ <para> There are many configuration parameters that affect the behavior of the database system. In the first section of this chapter, we - describe how to set configuration parameters. The subsequent sections + describe how to interact with configuration parameters. The subsequent sections discuss each parameter in detail. </para> @@ -24,46 +24,103 @@ <para> All parameter names are case-insensitive. Every parameter takes a value of one of five types: Boolean, integer, floating point, - string or enum. Boolean values can be written as <literal>on</literal>, - <literal>off</literal>, <literal>true</literal>, - <literal>false</literal>, <literal>yes</literal>, - <literal>no</literal>, <literal>1</literal>, <literal>0</literal> - (all case-insensitive) or any unambiguous prefix of these. + string or enum. </para> + <itemizedlist> + + <listitem> <para> - Some settings specify a memory or time value. Each of these has an - implicit unit, which is either kilobytes, blocks (typically eight - kilobytes), milliseconds, seconds, or minutes. Default units can be - found by referencing <structname>pg_settings</>.<structfield>unit</>. - For convenience, - a different unit can also be specified explicitly. Valid memory units - are <literal>kB</literal> (kilobytes), <literal>MB</literal> - (megabytes), <literal>GB</literal> (gigabytes), and <literal>TB</literal> (terabytes); valid time units - are <literal>ms</literal> (milliseconds), <literal>s</literal> - (seconds), <literal>min</literal> (minutes), <literal>h</literal> - (hours), and <literal>d</literal> (days). Note that the multiplier - for memory units is 1024, not 1000. + <emphasis>Boolean</emphasis>: Values can be written as + <literal>on</literal>, + <literal>off</literal>, + <literal>true</literal>, + <literal>false</literal>, + <literal>yes</literal>, + <literal>no</literal>, + <literal>1</literal>, + <literal>0</literal> + (all case-insensitive) or any unambiguous prefix of these. </para> + </listitem> + <listitem> <para> - Parameters of type <quote>enum</> are specified in the same way as string - parameters, but are restricted to a limited set of values. The allowed - values can be found - from <structname>pg_settings</>.<structfield>enumvals</>. - Enum parameter values are case-insensitive. + <emphasis>String</emphasis>: + Enclose the value in single-quote. Values are case-insensitive. + If multiple values are allowed separate them with commas. </para> - </sect2> + </listitem> + + <listitem> + <para> + <emphasis>Numeric</emphasis> + (integer and floating point): Do not use single-quotes + (unless otherwise required) + or thousand separators. + Typically memory or time related - see comments in that + section for detail. + </para> + </listitem> - <sect2 id="config-setting-configuration-file"> - <title>Setting Parameters via the Configuration File</title> + <listitem> + <para> + <emphasis>Numeric or String with Unit</emphasis>: Memory & Time. + Both of these have an implicit unit, which is either kilobytes, + blocks (typically eight kilobytes), milliseconds, seconds, or minutes. + An numeric value will use the default, which can be + found by referencing <structname>pg_settings</>.<structfield>unit</>. + For convenience, a different unit can also be specified explicitly + via a string value. It is case-sensitive and + may include a space between the value and the unit + <itemizedlist> + <listitem> + <para> + Valid memory units are + <literal>kB</literal> (kilobytes), + <literal>MB</literal> (megabytes), + <literal>GB</literal> (gigabytes), + and <literal>TB</literal> (terabytes). + The multiplier for memory units is 1024, not 1000. + </para> + </listitem> + <listitem> + <para> + Valid time units are + <literal>ms</literal> (milliseconds), + <literal>s</literal> (seconds), + <literal>min</literal> (minutes), + <literal>h</literal> (hours), + and <literal>d</literal> (days). + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + <listitem> <para> - One way to set these parameters is to edit the file - <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, - which is normally kept in the data directory. (A default copy is - installed there when the database cluster directory is - initialized.) An example of what this file might look like is: + <emphasis><quote>enum</></emphasis>: These specified in the same way as string + parameters, but are restricted to a limited set of values that can be queried + from <structname>pg_settings</>.<structfield>enumvals</>: +<programlisting> +SELECT name, setting, enumvals FROM pg_settings WHERE enumvals IS NOT NULL; +</programlisting> + Enum parameter values are case-insensitive. + </para> + </listitem> + </itemizedlist> + </sect2> + + <sect2 id="config-setting-configuration-file"> + <title>Parameter Interaction via Configuration File</title> + + <para> + The primary way to set these parameters is to edit the file + <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, + which is normally kept in the data directory. (A default copy is + installed there when the database cluster directory is + initialized.) An example of what this file might look like is: <programlisting> # This is a comment log_connections = yes @@ -71,129 +128,209 @@ log_destination = 'syslog' search_path = '"$user", public' shared_buffers = 128MB </programlisting> - One parameter is specified per line. The equal sign between name and - value is optional. Whitespace is insignificant and blank lines are - ignored. Hash marks (<literal>#</literal>) designate the remainder of the - line as a comment. Parameter values that are not simple identifiers or - numbers must be single-quoted. To embed a single quote in a parameter - value, write either two quotes (preferred) or backslash-quote. - </para> + One parameter is specified per line. The equal sign between name and + value is optional. Whitespace is insignificant and blank lines are + ignored. Hash marks (<literal>#</literal>) designate the remainder of the + line as a comment. Parameter values that are not simple identifiers or + numbers must be single-quoted. To embed a single quote in a parameter + value, write either two quotes (preferred) or backslash-quote. + </para> - <para> - <indexterm> - <primary>SIGHUP</primary> - </indexterm> - The configuration file is reread whenever the main server process - receives a <systemitem>SIGHUP</> signal; this is most easily done by - running <literal>pg_ctl reload</> from the command-line or by calling - the SQL function <function>pg_reload_conf()</function>. The main - server process - also propagates this signal to all currently running server - processes so that existing sessions also get the new - value. Alternatively, you can send the signal to a single server - process directly. Some parameters can only be set at server start; - any changes to their entries in the configuration file will be ignored - until the server is restarted. Invalid parameter settings in the - configuration file are likewise ignored (but logged) during - <systemitem>SIGHUP</> processing. - </para> - </sect2> + <para> + Parameters set in this way provide the global default value for the cluster. + The setting actually seen by the connecting user or issued statement + will be this value unless it is overridden. + The next sections describe ways in which the administrator + or user can override these defaults. + </para> - <sect2 id="config-setting-other-methods"> - <title>Other Ways to Set Parameters</title> + <para> + <indexterm> + <primary>SIGHUP</primary> + </indexterm> + The configuration file is reread whenever the main server process + receives a <systemitem>SIGHUP</> signal; this is most easily done by + running <literal>pg_ctl reload</> from the command-line or by calling + the SQL function <function>pg_reload_conf()</function>. The main + server process + also propagates this signal to all currently running server + processes so that existing sessions also get the new + value. Alternatively, you can send the signal to a single server + process directly. Some parameters can only be set at server start; + any changes to their entries in the configuration file will be ignored + until the server is restarted. Invalid parameter settings in the + configuration file are likewise ignored (but logged) during + <systemitem>SIGHUP</> processing. + </para> + </sect2> - <para> - A second way to set these configuration parameters is to give them - as a command-line option to the <command>postgres</command> command, - such as: -<programlisting> -postgres -c log_connections=yes -c log_destination='syslog' -</programlisting> - Command-line options override any conflicting settings in - <filename>postgresql.conf</filename>. Note that this means you won't - be able to change the value on-the-fly by editing - <filename>postgresql.conf</filename>, so while the command-line - method might be convenient, it can cost you flexibility later. - </para> + <sect2 id="config-setting-sql-command-interaction"> + <title>Parameter Interaction via SQL</title> + <para> + <productname>PostgreSQL</productname> provides three SQL commands + to establish configuration defaults + that override those configured globally. + The evaluation of these defaults occurs at the beginning of a + new session, upon the user issuing <xref linkend="SQL-DISCARD">, + or if the server forces the session to reload it configuration after a + <systemitem>SIGHUP</systemitem> + </para> + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-ALTERSYSTEM"> command provides + an SQL-accessible means + to change the global defaults. Since the server must + be running to execute SQL + the timing of when the actual value takes effect depends + on the variable being + changed - but in no case is the current session affected + nor will any change take + effect before the next configuration + reload (<systemitem>SIGHUP</>) by the server. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterdatabase"> command allows the database + administrator to override global settings on a per-database basis. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterrole"> command allows the database administrator + to override both global and per-database settings with user-specific values. + </para> + </listitem> + </itemizedlist> - <para> - Occasionally it is useful to give a command line option to - one particular session only. The environment variable - <envar>PGOPTIONS</envar> can be used for this purpose on the - client side: + <para> + Once a client connects to the database PostgreSQL provides two + additional SQL commands + to interact with session-local system configuration. + Both of these commands have equivalent + system administration functions. + </para> + + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-SHOW"> + command allows inspection of the current value of all parameters. + The corresponding function + is <function>current_setting(setting_name text)</function>. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="SQL-SET"> + command allows modification of the current value some parameters. + The corresponding function is + <function>set_config(setting_name, new_value, is_local)</function>. + </para> + </listitem> + </itemizedlist> + + <para> + Both <emphasis>SELECT</> and <emphasis>UPDATE</> can be issued against the + virtual table <structname>pg_settings</> to view and + affect the session-local configuration. + Its definition can be found in <xref linkend="view-pg-settings">. + </para> + + <itemizedlist> + <listitem> + <para> + <xref linkend="SQL-SELECT">-ing against this relation + is the equivalent of issuing + SHOW but provides considerably more detail as well as + allowing for joining against other relations and specifying filter criteria. + </para> + </listitem> + <listitem> + <para> + <xref linkend="SQL-UPDATE">-ing against this relation, + specifically the <structname>setting</> column + is the equivalent of issuing SET though all values must be single-quoted. + </para> + <para> + Note that the eqivalent of <programlisting> -env PGOPTIONS='-c geqo=off' psql +SET configuration_parameter TO DEFAULT; +</> + would be: +<programlisting> +UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter'; </programlisting> - (This works for any <application>libpq</>-based client application, not - just <application>psql</application>.) Note that this won't work for - parameters that are fixed when the server is started or that must be - specified in <filename>postgresql.conf</filename>. - </para> + </para> + </listitem> + </itemizedlist> - <para> - Furthermore, it is possible to assign a set of parameter settings to - a user or a database. Whenever a session is started, the default - settings for the user and database involved are loaded. The - commands <xref linkend="sql-alterrole"> - and <xref linkend="sql-alterdatabase">, - respectively, are used to configure these settings. Per-database - settings override anything received from the - <command>postgres</command> command-line or the configuration - file, and in turn are overridden by per-user settings; both are - overridden by per-session settings. - </para> + </sect2> - <para> - Some parameters can be changed in individual <acronym>SQL</acronym> - sessions with the <xref linkend="SQL-SET"> - command, for example: -<screen> -SET ENABLE_SEQSCAN TO OFF; -</screen> - If <command>SET</> is allowed, it overrides all other sources of - values for the parameter. Some parameters cannot be changed via - <command>SET</command>: for example, if they control behavior that - cannot be changed without restarting the entire - <productname>PostgreSQL</productname> server. Also, some parameters - require superuser permission to change via <command>SET</command> or - <command>ALTER</>. - </para> + <sect2> + <title>Parameter Interaction via Shell</title> + <para> + In addition to setting global defaults or attaching overrides + at the database or role scope + , you may choose to provide them to + <productname>PostgreSQL</productname> via shell facilities. + Both the server and <application>libpq</> client library + have defined ways to accept + parameter values via the shell. + </para> + <itemizedlist> - <para> - Another way to change configuration parameters persistently is by - use of <xref linkend="SQL-ALTERSYSTEM"> - command, for example: -<screen> -ALTER SYSTEM SET checkpoint_timeout TO 600; -</screen> - This command will allow users to change values persistently - through SQL command. The values will be effective after reload of server configuration - (<acronym>SIGHUP</>) or server startup. The effect of this command is similar to when - user manually changes values in <filename>postgresql.conf</filename>. - </para> - </sect2> + <listitem> + <para> + On the <emphasis>server</emphasis>, command-line options can passed to the + <command>postgres</command> command directly via the "-c" parameter. +<programlisting> +postgres -c log_connections=yes -c log_destination='syslog' +</programlisting> + Settings provided this way override those resolved globally + (via postgresql.conf or ALTER SYSTEM) + but are otherwise treated as being global for the purpose of + database and role overriding. + </para> - <sect2 id="config-setting-examining"> - <title>Examining Parameter Settings</title> + <para> + Typically, a production system will be adminstered via its + <filename>postgresql.conf</filename> file. + Use of this mechanism is suggested only for development and testing. + </para> + </listitem> - <para> - The <xref linkend="SQL-SHOW"> - command allows inspection of the current values of all parameters. - </para> + <listitem> + <para> + On the <emphasis>libpq-client</emphasis>, command-line options are + specified using the + <envar>PGOPTIONS</envar> environment variable. + Upon connecting to a server the contents of this variable are sent + to the server as if they were being + executed via a SQL <xref linkend="SQL-SET"> at the beginning of the session. + </para> - <para> - The virtual table <structname>pg_settings</structname> also allows - displaying and updating session run-time parameters; see <xref - linkend="view-pg-settings"> for details and a description of the - different variable types and when they can be changed. - <structname>pg_settings</structname> is equivalent to <command>SHOW</> - and <command>SET</>, but can be more convenient - to use because it can be joined with other tables, or selected from using - any desired selection condition. It also contains more information about - each parameter than is available from <command>SHOW</>. - </para> + <para> + However, the format for <envar>PGOPTIONS</envar> is similar to that provided when launching + <command>postgres</command> command. + Specifically, the '-c' flag specification is part of the value. +<programlisting> +env PGOPTIONS="-c geqo=off -c statement_timeout='5 min'" psql +</programlisting> + </para> - </sect2> + <para> + Other clients and libraries may provide their own mechanisms, + via the shell or otherwise, that allow the user + to setup the session configuration without requiring the user + to issue SQL commands. Please see their documentation + for details. + </para> + </listitem> + </itemizedlist> + + </sect2> <sect2 id="config-includes"> <title>Configuration File Includes</title> -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs Attachments: [text/plain] config_sgml_18_1_1-4_v1.diff (19.2K, 3-config_sgml_18_1_1-4_v1.diff) download | inline diff: diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 697cf99..6ac48d6 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -11,7 +11,7 @@ <para> There are many configuration parameters that affect the behavior of the database system. In the first section of this chapter, we - describe how to set configuration parameters. The subsequent sections + describe how to interact with configuration parameters. The subsequent sections discuss each parameter in detail. </para> @@ -24,46 +24,103 @@ <para> All parameter names are case-insensitive. Every parameter takes a value of one of five types: Boolean, integer, floating point, - string or enum. Boolean values can be written as <literal>on</literal>, - <literal>off</literal>, <literal>true</literal>, - <literal>false</literal>, <literal>yes</literal>, - <literal>no</literal>, <literal>1</literal>, <literal>0</literal> - (all case-insensitive) or any unambiguous prefix of these. + string or enum. </para> + <itemizedlist> + + <listitem> <para> - Some settings specify a memory or time value. Each of these has an - implicit unit, which is either kilobytes, blocks (typically eight - kilobytes), milliseconds, seconds, or minutes. Default units can be - found by referencing <structname>pg_settings</>.<structfield>unit</>. - For convenience, - a different unit can also be specified explicitly. Valid memory units - are <literal>kB</literal> (kilobytes), <literal>MB</literal> - (megabytes), <literal>GB</literal> (gigabytes), and <literal>TB</literal> (terabytes); valid time units - are <literal>ms</literal> (milliseconds), <literal>s</literal> - (seconds), <literal>min</literal> (minutes), <literal>h</literal> - (hours), and <literal>d</literal> (days). Note that the multiplier - for memory units is 1024, not 1000. + <emphasis>Boolean</emphasis>: Values can be written as + <literal>on</literal>, + <literal>off</literal>, + <literal>true</literal>, + <literal>false</literal>, + <literal>yes</literal>, + <literal>no</literal>, + <literal>1</literal>, + <literal>0</literal> + (all case-insensitive) or any unambiguous prefix of these. </para> + </listitem> + <listitem> <para> - Parameters of type <quote>enum</> are specified in the same way as string - parameters, but are restricted to a limited set of values. The allowed - values can be found - from <structname>pg_settings</>.<structfield>enumvals</>. - Enum parameter values are case-insensitive. + <emphasis>String</emphasis>: + Enclose the value in single-quote. Values are case-insensitive. + If multiple values are allowed separate them with commas. </para> - </sect2> + </listitem> + + <listitem> + <para> + <emphasis>Numeric</emphasis> + (integer and floating point): Do not use single-quotes + (unless otherwise required) + or thousand separators. + Typically memory or time related - see comments in that + section for detail. + </para> + </listitem> - <sect2 id="config-setting-configuration-file"> - <title>Setting Parameters via the Configuration File</title> + <listitem> + <para> + <emphasis>Numeric or String with Unit</emphasis>: Memory & Time. + Both of these have an implicit unit, which is either kilobytes, + blocks (typically eight kilobytes), milliseconds, seconds, or minutes. + An numeric value will use the default, which can be + found by referencing <structname>pg_settings</>.<structfield>unit</>. + For convenience, a different unit can also be specified explicitly + via a string value. It is case-sensitive and + may include a space between the value and the unit + <itemizedlist> + <listitem> + <para> + Valid memory units are + <literal>kB</literal> (kilobytes), + <literal>MB</literal> (megabytes), + <literal>GB</literal> (gigabytes), + and <literal>TB</literal> (terabytes). + The multiplier for memory units is 1024, not 1000. + </para> + </listitem> + <listitem> + <para> + Valid time units are + <literal>ms</literal> (milliseconds), + <literal>s</literal> (seconds), + <literal>min</literal> (minutes), + <literal>h</literal> (hours), + and <literal>d</literal> (days). + </para> + </listitem> + </itemizedlist> + </para> + </listitem> + <listitem> <para> - One way to set these parameters is to edit the file - <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, - which is normally kept in the data directory. (A default copy is - installed there when the database cluster directory is - initialized.) An example of what this file might look like is: + <emphasis><quote>enum</></emphasis>: These specified in the same way as string + parameters, but are restricted to a limited set of values that can be queried + from <structname>pg_settings</>.<structfield>enumvals</>: +<programlisting> +SELECT name, setting, enumvals FROM pg_settings WHERE enumvals IS NOT NULL; +</programlisting> + Enum parameter values are case-insensitive. + </para> + </listitem> + </itemizedlist> + </sect2> + + <sect2 id="config-setting-configuration-file"> + <title>Parameter Interaction via Configuration File</title> + + <para> + The primary way to set these parameters is to edit the file + <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, + which is normally kept in the data directory. (A default copy is + installed there when the database cluster directory is + initialized.) An example of what this file might look like is: <programlisting> # This is a comment log_connections = yes @@ -71,129 +128,209 @@ log_destination = 'syslog' search_path = '"$user", public' shared_buffers = 128MB </programlisting> - One parameter is specified per line. The equal sign between name and - value is optional. Whitespace is insignificant and blank lines are - ignored. Hash marks (<literal>#</literal>) designate the remainder of the - line as a comment. Parameter values that are not simple identifiers or - numbers must be single-quoted. To embed a single quote in a parameter - value, write either two quotes (preferred) or backslash-quote. - </para> + One parameter is specified per line. The equal sign between name and + value is optional. Whitespace is insignificant and blank lines are + ignored. Hash marks (<literal>#</literal>) designate the remainder of the + line as a comment. Parameter values that are not simple identifiers or + numbers must be single-quoted. To embed a single quote in a parameter + value, write either two quotes (preferred) or backslash-quote. + </para> - <para> - <indexterm> - <primary>SIGHUP</primary> - </indexterm> - The configuration file is reread whenever the main server process - receives a <systemitem>SIGHUP</> signal; this is most easily done by - running <literal>pg_ctl reload</> from the command-line or by calling - the SQL function <function>pg_reload_conf()</function>. The main - server process - also propagates this signal to all currently running server - processes so that existing sessions also get the new - value. Alternatively, you can send the signal to a single server - process directly. Some parameters can only be set at server start; - any changes to their entries in the configuration file will be ignored - until the server is restarted. Invalid parameter settings in the - configuration file are likewise ignored (but logged) during - <systemitem>SIGHUP</> processing. - </para> - </sect2> + <para> + Parameters set in this way provide the global default value for the cluster. + The setting actually seen by the connecting user or issued statement + will be this value unless it is overridden. + The next sections describe ways in which the administrator + or user can override these defaults. + </para> - <sect2 id="config-setting-other-methods"> - <title>Other Ways to Set Parameters</title> + <para> + <indexterm> + <primary>SIGHUP</primary> + </indexterm> + The configuration file is reread whenever the main server process + receives a <systemitem>SIGHUP</> signal; this is most easily done by + running <literal>pg_ctl reload</> from the command-line or by calling + the SQL function <function>pg_reload_conf()</function>. The main + server process + also propagates this signal to all currently running server + processes so that existing sessions also get the new + value. Alternatively, you can send the signal to a single server + process directly. Some parameters can only be set at server start; + any changes to their entries in the configuration file will be ignored + until the server is restarted. Invalid parameter settings in the + configuration file are likewise ignored (but logged) during + <systemitem>SIGHUP</> processing. + </para> + </sect2> - <para> - A second way to set these configuration parameters is to give them - as a command-line option to the <command>postgres</command> command, - such as: -<programlisting> -postgres -c log_connections=yes -c log_destination='syslog' -</programlisting> - Command-line options override any conflicting settings in - <filename>postgresql.conf</filename>. Note that this means you won't - be able to change the value on-the-fly by editing - <filename>postgresql.conf</filename>, so while the command-line - method might be convenient, it can cost you flexibility later. - </para> + <sect2 id="config-setting-sql-command-interaction"> + <title>Parameter Interaction via SQL</title> + <para> + <productname>PostgreSQL</productname> provides three SQL commands + to establish configuration defaults + that override those configured globally. + The evaluation of these defaults occurs at the beginning of a + new session, upon the user issuing <xref linkend="SQL-DISCARD">, + or if the server forces the session to reload it configuration after a + <systemitem>SIGHUP</systemitem> + </para> + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-ALTERSYSTEM"> command provides + an SQL-accessible means + to change the global defaults. Since the server must + be running to execute SQL + the timing of when the actual value takes effect depends + on the variable being + changed - but in no case is the current session affected + nor will any change take + effect before the next configuration + reload (<systemitem>SIGHUP</>) by the server. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterdatabase"> command allows the database + administrator to override global settings on a per-database basis. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="sql-alterrole"> command allows the database administrator + to override both global and per-database settings with user-specific values. + </para> + </listitem> + </itemizedlist> - <para> - Occasionally it is useful to give a command line option to - one particular session only. The environment variable - <envar>PGOPTIONS</envar> can be used for this purpose on the - client side: + <para> + Once a client connects to the database PostgreSQL provides two + additional SQL commands + to interact with session-local system configuration. + Both of these commands have equivalent + system administration functions. + </para> + + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-SHOW"> + command allows inspection of the current value of all parameters. + The corresponding function + is <function>current_setting(setting_name text)</function>. + </para> + </listitem> + <listitem> + <para> + The <xref linkend="SQL-SET"> + command allows modification of the current value some parameters. + The corresponding function is + <function>set_config(setting_name, new_value, is_local)</function>. + </para> + </listitem> + </itemizedlist> + + <para> + Both <emphasis>SELECT</> and <emphasis>UPDATE</> can be issued against the + virtual table <structname>pg_settings</> to view and + affect the session-local configuration. + Its definition can be found in <xref linkend="view-pg-settings">. + </para> + + <itemizedlist> + <listitem> + <para> + <xref linkend="SQL-SELECT">-ing against this relation + is the equivalent of issuing + SHOW but provides considerably more detail as well as + allowing for joining against other relations and specifying filter criteria. + </para> + </listitem> + <listitem> + <para> + <xref linkend="SQL-UPDATE">-ing against this relation, + specifically the <structname>setting</> column + is the equivalent of issuing SET though all values must be single-quoted. + </para> + <para> + Note that the eqivalent of <programlisting> -env PGOPTIONS='-c geqo=off' psql +SET configuration_parameter TO DEFAULT; +</> + would be: +<programlisting> +UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter'; </programlisting> - (This works for any <application>libpq</>-based client application, not - just <application>psql</application>.) Note that this won't work for - parameters that are fixed when the server is started or that must be - specified in <filename>postgresql.conf</filename>. - </para> + </para> + </listitem> + </itemizedlist> - <para> - Furthermore, it is possible to assign a set of parameter settings to - a user or a database. Whenever a session is started, the default - settings for the user and database involved are loaded. The - commands <xref linkend="sql-alterrole"> - and <xref linkend="sql-alterdatabase">, - respectively, are used to configure these settings. Per-database - settings override anything received from the - <command>postgres</command> command-line or the configuration - file, and in turn are overridden by per-user settings; both are - overridden by per-session settings. - </para> + </sect2> - <para> - Some parameters can be changed in individual <acronym>SQL</acronym> - sessions with the <xref linkend="SQL-SET"> - command, for example: -<screen> -SET ENABLE_SEQSCAN TO OFF; -</screen> - If <command>SET</> is allowed, it overrides all other sources of - values for the parameter. Some parameters cannot be changed via - <command>SET</command>: for example, if they control behavior that - cannot be changed without restarting the entire - <productname>PostgreSQL</productname> server. Also, some parameters - require superuser permission to change via <command>SET</command> or - <command>ALTER</>. - </para> + <sect2> + <title>Parameter Interaction via Shell</title> + <para> + In addition to setting global defaults or attaching overrides + at the database or role scope + , you may choose to provide them to + <productname>PostgreSQL</productname> via shell facilities. + Both the server and <application>libpq</> client library + have defined ways to accept + parameter values via the shell. + </para> + <itemizedlist> - <para> - Another way to change configuration parameters persistently is by - use of <xref linkend="SQL-ALTERSYSTEM"> - command, for example: -<screen> -ALTER SYSTEM SET checkpoint_timeout TO 600; -</screen> - This command will allow users to change values persistently - through SQL command. The values will be effective after reload of server configuration - (<acronym>SIGHUP</>) or server startup. The effect of this command is similar to when - user manually changes values in <filename>postgresql.conf</filename>. - </para> - </sect2> + <listitem> + <para> + On the <emphasis>server</emphasis>, command-line options can passed to the + <command>postgres</command> command directly via the "-c" parameter. +<programlisting> +postgres -c log_connections=yes -c log_destination='syslog' +</programlisting> + Settings provided this way override those resolved globally + (via postgresql.conf or ALTER SYSTEM) + but are otherwise treated as being global for the purpose of + database and role overriding. + </para> - <sect2 id="config-setting-examining"> - <title>Examining Parameter Settings</title> + <para> + Typically, a production system will be adminstered via its + <filename>postgresql.conf</filename> file. + Use of this mechanism is suggested only for development and testing. + </para> + </listitem> - <para> - The <xref linkend="SQL-SHOW"> - command allows inspection of the current values of all parameters. - </para> + <listitem> + <para> + On the <emphasis>libpq-client</emphasis>, command-line options are + specified using the + <envar>PGOPTIONS</envar> environment variable. + Upon connecting to a server the contents of this variable are sent + to the server as if they were being + executed via a SQL <xref linkend="SQL-SET"> at the beginning of the session. + </para> - <para> - The virtual table <structname>pg_settings</structname> also allows - displaying and updating session run-time parameters; see <xref - linkend="view-pg-settings"> for details and a description of the - different variable types and when they can be changed. - <structname>pg_settings</structname> is equivalent to <command>SHOW</> - and <command>SET</>, but can be more convenient - to use because it can be joined with other tables, or selected from using - any desired selection condition. It also contains more information about - each parameter than is available from <command>SHOW</>. - </para> + <para> + However, the format for <envar>PGOPTIONS</envar> is similar to that provided when launching + <command>postgres</command> command. + Specifically, the '-c' flag specification is part of the value. +<programlisting> +env PGOPTIONS="-c geqo=off -c statement_timeout='5 min'" psql +</programlisting> + </para> - </sect2> + <para> + Other clients and libraries may provide their own mechanisms, + via the shell or otherwise, that allow the user + to setup the session configuration without requiring the user + to issue SQL commands. Please see their documentation + for details. + </para> + </listitem> + </itemizedlist> + + </sect2> <sect2 id="config-includes"> <title>Configuration File Includes</title> [application/pdf] config_sgml_18_1_setting_parameters.pdf (165.2K, 4-config_sgml_18_1_setting_parameters.pdf) download ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-09-09 19:13 Bruce Momjian <[email protected]> parent: David Johnston <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Bruce Momjian @ 2014-09-09 19:13 UTC (permalink / raw) To: David Johnston <[email protected]>; +Cc: pgsql-docs On Thu, Jun 12, 2014 at 11:32:49AM -0400, David Johnston wrote: > This addition still needs some work as well as the patch as a whole (see my > comments above). The attached PDF is the page that results from "make html"; > the original patch has been re-attached as well and -hackers copied given the > lack of response on -doc. > > I am hoping this change would be something that could be included in 9.4; I > believe the enhancements would be most beneficial to newcomers who are just > getting started with PostgreSQL and would be using 9.4 as their starting point. [hackers CC removed] I have reviewed and updated this reworking of our configuration settings documentation and feel it is ready for application to head and 9.4; attached. -- Bruce Momjian <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs Attachments: [text/x-diff] config.diff (19.3K, 2-config.diff) download | inline diff: diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml new file mode 100644 index 49547ee..156c264 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** *** 11,17 **** <para> There are many configuration parameters that affect the behavior of the database system. In the first section of this chapter, we ! describe how to set configuration parameters. The subsequent sections discuss each parameter in detail. </para> --- 11,17 ---- <para> There are many configuration parameters that affect the behavior of the database system. In the first section of this chapter, we ! describe how to interact with configuration parameters. The subsequent sections discuss each parameter in detail. </para> *************** *** 23,69 **** <para> All parameter names are case-insensitive. Every parameter takes a ! value of one of five types: Boolean, integer, floating point, ! string or enum. Boolean values can be written as <literal>on</literal>, ! <literal>off</literal>, <literal>true</literal>, ! <literal>false</literal>, <literal>yes</literal>, ! <literal>no</literal>, <literal>1</literal>, <literal>0</literal> ! (all case-insensitive) or any unambiguous prefix of these. </para> ! <para> ! Some settings specify a memory or time value. Each of these has an ! implicit unit, which is either kilobytes, blocks (typically eight ! kilobytes), milliseconds, seconds, or minutes. Default units can be ! found by referencing <structname>pg_settings</>.<structfield>unit</>. ! For convenience, ! a different unit can also be specified explicitly. Valid memory units ! are <literal>kB</literal> (kilobytes), <literal>MB</literal> ! (megabytes), <literal>GB</literal> (gigabytes), and <literal>TB</literal> (terabytes); valid time units ! are <literal>ms</literal> (milliseconds), <literal>s</literal> ! (seconds), <literal>min</literal> (minutes), <literal>h</literal> ! (hours), and <literal>d</literal> (days). Note that the multiplier ! for memory units is 1024, not 1000. ! </para> ! <para> ! Parameters of type <quote>enum</> are specified in the same way as string ! parameters, but are restricted to a limited set of values. The allowed ! values can be found ! from <structname>pg_settings</>.<structfield>enumvals</>. ! Enum parameter values are case-insensitive. ! </para> </sect2> <sect2 id="config-setting-configuration-file"> ! <title>Setting Parameters via the Configuration File</title> <para> ! One way to set these parameters is to edit the file <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, which is normally kept in the data directory. (A default copy is ! installed there when the database cluster directory is ! initialized.) An example of what this file might look like is: <programlisting> # This is a comment log_connections = yes --- 23,121 ---- <para> All parameter names are case-insensitive. Every parameter takes a ! value of one of five types: boolean, integer, floating point, ! string, or enum. </para> ! <itemizedlist> ! <listitem> ! <para> ! <emphasis>Boolean</emphasis>: Values can be written as ! <literal>on</literal>, ! <literal>off</literal>, ! <literal>true</literal>, ! <literal>false</literal>, ! <literal>yes</literal>, ! <literal>no</literal>, ! <literal>1</literal>, ! <literal>0</literal> ! (all case-insensitive) or any unambiguous prefix of these. ! </para> ! </listitem> ! ! <listitem> ! <para> ! <emphasis>String:</emphasis> Enclose the value in ! single-quotes. Values are case-insensitive. If multiple values ! are allowed, separate them with commas. ! </para> ! </listitem> ! ! <listitem> ! <para> ! <emphasis>Numeric</emphasis> (integer and floating point): Do not use ! single-quotes (unless otherwise required) or thousand separators. ! </para> ! </listitem> ! ! <listitem> ! <para> ! <emphasis>Numeric or String with Unit (Memory & ! Time):</emphasis> These have an implicit unit, which is ! either kilobytes, blocks (typically eight kilobytes), ! milliseconds, seconds, or minutes. An numeric value ! will use the default, which can be found by referencing ! <structname>pg_settings</>.<structfield>unit</>. For convenience, ! a different unit can also be specified explicitly via a string ! value. It is case-sensitive and may include whitespace between ! the value and the unit. ! ! <itemizedlist> ! <listitem> ! <para> ! Valid memory units are <literal>kB</literal> (kilobytes), ! <literal>MB</literal> (megabytes), <literal>GB</literal> ! (gigabytes), and <literal>TB</literal> (terabytes). ! The multiplier for memory units is 1024, not 1000. ! </para> ! </listitem> ! ! <listitem> ! <para> ! Valid time units are <literal>ms</literal> (milliseconds), ! <literal>s</literal> (seconds), <literal>min</literal> (minutes), ! <literal>h</literal> (hours), and <literal>d</literal> (days). ! </para> ! </listitem> ! </itemizedlist> ! </para> ! </listitem> ! ! <listitem> ! <para> ! <emphasis><quote>enum</>:</emphasis> These are specified ! in the same way as string parameters, but are restricted ! to a limited set of values that can be queried from ! <structname>pg_settings</>.<structfield>enumvals</>: ! <programlisting> ! SELECT name, setting, enumvals FROM pg_settings WHERE enumvals IS NOT NULL; ! </programlisting> ! Enum parameter values are case-insensitive. ! </para> ! </listitem> ! </itemizedlist> </sect2> <sect2 id="config-setting-configuration-file"> ! <title>Parameter Interaction via Configuration File</title> <para> ! The primary way to set these parameters is to edit the file <filename>postgresql.conf</><indexterm><primary>postgresql.conf</></>, which is normally kept in the data directory. (A default copy is ! installed when the database cluster directory is initialized.) ! An example of what this file might look like is: <programlisting> # This is a comment log_connections = yes *************** log_destination = 'syslog' *** 71,197 **** search_path = '"$user", public' shared_buffers = 128MB </programlisting> ! One parameter is specified per line. The equal sign between name and ! value is optional. Whitespace is insignificant and blank lines are ! ignored. Hash marks (<literal>#</literal>) designate the remainder of the ! line as a comment. Parameter values that are not simple identifiers or ! numbers must be single-quoted. To embed a single quote in a parameter ! value, write either two quotes (preferred) or backslash-quote. </para> <para> <indexterm> ! <primary>SIGHUP</primary> </indexterm> The configuration file is reread whenever the main server process receives a <systemitem>SIGHUP</> signal; this is most easily done by running <literal>pg_ctl reload</> from the command-line or by calling the SQL function <function>pg_reload_conf()</function>. The main ! server process ! also propagates this signal to all currently running server ! processes so that existing sessions also get the new ! value. Alternatively, you can send the signal to a single server ! process directly. Some parameters can only be set at server start; ! any changes to their entries in the configuration file will be ignored ! until the server is restarted. Invalid parameter settings in the ! configuration file are likewise ignored (but logged) during ! <systemitem>SIGHUP</> processing. </para> </sect2> ! <sect2 id="config-setting-other-methods"> ! <title>Other Ways to Set Parameters</title> ! <para> ! A second way to set these configuration parameters is to give them ! as a command-line option to the <command>postgres</command> command, ! such as: ! <programlisting> ! postgres -c log_connections=yes -c log_destination='syslog' ! </programlisting> ! Command-line options override any conflicting settings in ! <filename>postgresql.conf</filename>. Note that this means you won't ! be able to change the value on-the-fly by editing ! <filename>postgresql.conf</filename>, so while the command-line ! method might be convenient, it can cost you flexibility later. ! </para> ! <para> ! Occasionally it is useful to give a command line option to ! one particular session only. The environment variable ! <envar>PGOPTIONS</envar> can be used for this purpose on the ! client side: ! <programlisting> ! env PGOPTIONS='-c geqo=off' psql ! </programlisting> ! (This works for any <application>libpq</>-based client application, not ! just <application>psql</application>.) Note that this won't work for ! parameters that are fixed when the server is started or that must be ! specified in <filename>postgresql.conf</filename>. ! </para> ! <para> ! Furthermore, it is possible to assign a set of parameter settings to ! a user or a database. Whenever a session is started, the default ! settings for the user and database involved are loaded. The ! commands <xref linkend="sql-alterrole"> ! and <xref linkend="sql-alterdatabase">, ! respectively, are used to configure these settings. Per-database ! settings override anything received from the ! <command>postgres</command> command-line or the configuration ! file, and in turn are overridden by per-user settings; both are ! overridden by per-session settings. </para> <para> ! Some parameters can be changed in individual <acronym>SQL</acronym> ! sessions with the <xref linkend="SQL-SET"> ! command, for example: ! <screen> ! SET ENABLE_SEQSCAN TO OFF; ! </screen> ! If <command>SET</> is allowed, it overrides all other sources of ! values for the parameter. Some parameters cannot be changed via ! <command>SET</command>: for example, if they control behavior that ! cannot be changed without restarting the entire ! <productname>PostgreSQL</productname> server. Also, some parameters ! require superuser permission to change via <command>SET</command> or ! <command>ALTER</>. </para> ! <para> ! Another way to change configuration parameters persistently is by ! use of <xref linkend="SQL-ALTERSYSTEM"> ! command, for example: ! <screen> ! ALTER SYSTEM SET checkpoint_timeout TO 600; ! </screen> ! This command will allow users to change values persistently ! through SQL command. The values will be effective after reload of server configuration ! (<acronym>SIGHUP</>) or server startup. The effect of this command is similar to when ! user manually changes values in <filename>postgresql.conf</filename>. </para> </sect2> ! <sect2 id="config-setting-examining"> ! <title>Examining Parameter Settings</title> ! <para> ! The <xref linkend="SQL-SHOW"> ! command allows inspection of the current values of all parameters. ! </para> ! <para> ! The virtual table <structname>pg_settings</structname> also allows ! displaying and updating session run-time parameters; see <xref ! linkend="view-pg-settings"> for details and a description of the ! different variable types and when they can be changed. ! <structname>pg_settings</structname> is equivalent to <command>SHOW</> ! and <command>SET</>, but can be more convenient ! to use because it can be joined with other tables, or selected from using ! any desired selection condition. It also contains more information about ! each parameter than is available from <command>SHOW</>. ! </para> </sect2> --- 123,310 ---- search_path = '"$user", public' shared_buffers = 128MB </programlisting> ! One parameter is specified per line. The equal sign between name ! and value is optional. Whitespace is insignificant and blank ! lines are ignored. Hash marks (<literal>#</literal>) designate the ! remainder of lines as comments. Parameter values that are not simple ! identifiers or numbers must be single-quoted. To embed a single ! quote in a parameter value, write either two quotes (preferred) ! or backslash-quote. ! </para> ! ! <para> ! Parameters set in this way provide default values for the cluster. ! The setting seen by active sessions will be this value unless ! it is overridden. The following sections describe ways in which the ! administrator or user can override these defaults. </para> <para> <indexterm> ! <primary>SIGHUP</primary> </indexterm> The configuration file is reread whenever the main server process receives a <systemitem>SIGHUP</> signal; this is most easily done by running <literal>pg_ctl reload</> from the command-line or by calling the SQL function <function>pg_reload_conf()</function>. The main ! server process also propagates this signal to all currently running ! server processes so that existing sessions also get the new value ! when they complete their transactions. Alternatively, you can ! send the signal to a single server process directly. Some parameters ! can only be set at server start; any changes to their entries in the ! configuration file will be ignored until the server is restarted. ! Invalid parameter settings in the configuration file are likewise ! ignored (but logged) during <systemitem>SIGHUP</> processing. </para> </sect2> ! <sect2 id="config-setting-sql-command-interaction"> ! <title>Parameter Interaction via SQL</title> ! <para> ! <productname>PostgreSQL</productname> provides three SQL ! commands to establish configuration defaults that override those ! configured globally. The evaluation of these defaults occurs ! at the beginning of a new session, upon the user issuing <xref ! linkend="SQL-DISCARD">, or if the server forces the session to ! reload its configuration after a <systemitem>SIGHUP</systemitem> ! signal. ! </para> ! <itemizedlist> ! <listitem> ! <para> ! The <xref linkend="SQL-ALTERSYSTEM"> command provides an ! SQL-accessible means of changing global defaults. ! </para> ! </listitem> ! <listitem> ! <para> ! The <xref linkend="sql-alterdatabase"> command allows database ! administrators to override global settings on a per-database basis. ! </para> ! </listitem> ! <listitem> ! <para> ! The <xref linkend="sql-alterrole"> command allows database ! administrators to override both global and per-database settings ! with user-specific values. ! </para> ! </listitem> ! </itemizedlist> ! ! <para> ! Once a client connects to the database PostgreSQL provides ! two additional SQL commands to interact with session-local ! configuration settings. Both of these commands have equivalent ! system administration functions. </para> + <itemizedlist> + <listitem> + <para> + The <xref linkend="SQL-SHOW"> command allows inspection of the + current value of all parameters. The corresponding function is + <function>current_setting(setting_name text)</function>. + </para> + </listitem> + + <listitem> + <para> + The <xref linkend="SQL-SET"> command allows modification of the + current value of some parameters. The corresponding function is + <function>set_config(setting_name, new_value, is_local)</function>. + </para> + </listitem> + </itemizedlist> + <para> ! Both <command>SELECT</> and <command>UPDATE</> ! can be issued against the system view <link ! linkend="view-pg-settings"><structname>pg_settings</></> to view ! and change session-local values. </para> ! <itemizedlist> ! <listitem> ! <para> ! Querying this view is the sames as <command>SHOW</> but provides ! more detail, as well as allowing for joins against other relations ! and the specification of filter criteria. ! </para> ! </listitem> ! ! <listitem> ! <para> ! Using <xref linkend="SQL-UPDATE"> on this relation, specifically ! updating the <structname>setting</> column, is the equivalent ! of issuing SQL <command>SET</>, though all values must be ! single-quoted. Note that the equivalent of ! <programlisting> ! SET configuration_parameter TO DEFAULT; ! </> ! is: ! <programlisting> ! UPDATE pg_settings SET setting = reset_val WHERE name = 'configuration_parameter'; ! </programlisting> </para> + </listitem> + </itemizedlist> + </sect2> ! <sect2> ! <title>Parameter Interaction via Shell</title> ! <para> ! In addition to setting global defaults or attaching ! overrides at the database or role level, you can pass setting to ! <productname>PostgreSQL</productname> via shell facilities. ! Both the server and <application>libpq</> client library ! accept parameter values via the shell. ! </para> ! <itemizedlist> ! <listitem> ! <para> ! On the <emphasis>server</emphasis>, command-line options can be ! passed to the <command>postgres</command> command directly via the ! <option>-c</> parameter. ! <programlisting> ! postgres -c log_connections=yes -c log_destination='syslog' ! </programlisting> ! Settings provided this way override those resolved globally (via ! <filename>postgresql.conf</> or <command>ALTER SYSTEM</>) but ! are otherwise treated as being global for the purpose of database ! and role overrides. ! </para> ! </listitem> ! <listitem> ! <para> ! On the <emphasis>libpq-client</emphasis>, command-line options can be ! specified using the <envar>PGOPTIONS</envar> environment variable. ! When connecting to the server, the contents of this variable are ! sent to the server as if they were being executed via SQL <xref ! linkend="SQL-SET"> at the beginning of the session. ! </para> ! ! <para> ! However, the format of <envar>PGOPTIONS</envar> is similar to that ! used when launching the <command>postgres</command> command. ! Specifically, the <option>-c</> flag must be specified. ! <programlisting> ! env PGOPTIONS="-c geqo=off -c statement_timeout='5 min'" psql ! </programlisting> ! </para> ! ! <para> ! Other clients and libraries might provide their own mechanisms, ! via the shell or otherwise, that allow the user to alter session ! settings without requiring the user to issue SQL commands. ! </para> ! </listitem> ! </itemizedlist> </sect2> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-09-09 19:40 David Johnston <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: David Johnston @ 2014-09-09 19:40 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs On Tue, Sep 9, 2014 at 3:13 PM, Bruce Momjian <[email protected]> wrote: > On Thu, Jun 12, 2014 at 11:32:49AM -0400, David Johnston wrote: > > This addition still needs some work as well as the patch as a whole (see > my > > comments above). The attached PDF is the page that results from "make > html"; > > the original patch has been re-attached as well and -hackers copied > given the > > lack of response on -doc. > > > > I am hoping this change would be something that could be included in > 9.4; I > > believe the enhancements would be most beneficial to newcomers who are > just > > getting started with PostgreSQL and would be using 9.4 as their starting > point. > > [hackers CC removed] > > I have reviewed and updated this reworking of our configuration settings > documentation and feel it is ready for application to head and 9.4; > attached. > > Minor corrections: First block: " In the first section of this chapter, we describe how to set configuration parameters" - remove the comma Second Block: - placement of the colon inside or outside of the emphasis tag is inconsistent (string, unit, enum are inside; boolean and numeric are outside) - units: "An numeric value..." -> maybe "An unadorned numeric value... Third block: "Hash marks (<literal>#</literal>) designate the remainder of lines as comments." -> "...designate the remainder of the line as a comment." I read this at first as "remaining lines [in the file] ... " "To embed a single quote in a parameter value, write either two quotes (preferred) or backslash-quote." -> remove the comma "Querying this view is the sames as" -> "the same as" "In addition to setting global defaults or attaching" <- add a comma after "defaults" A few extra commas here-and-there which seem to have replaced my use of colons but that maybe should just be removed completely. If I didn't note it above it seemed OK to leave in (or I missed it entirely...) David J. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-09-10 20:20 Bruce Momjian <[email protected]> parent: David Johnston <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: Bruce Momjian @ 2014-09-10 20:20 UTC (permalink / raw) To: David Johnston <[email protected]>; +Cc: pgsql-docs On Tue, Sep 9, 2014 at 03:40:45PM -0400, David Johnston wrote: > Minor corrections: > > First block: " > In the first section of this chapter, we > describe how to set configuration parameters" > > - remove the comma > > Second Block: > - placement of the colon inside or outside of the emphasis tag is inconsistent > (string, unit, enum are inside; boolean and numeric are outside) > - units: "An numeric value..." -> maybe "An unadorned numeric value... > > Third block: > "Hash marks (<literal>#</literal>) designate the remainder of lines as > comments." -> "...designate the remainder of the line as a comment." I read > this at first as "remaining lines [in the file] ... " > > "To embed a single quote in a parameter value, write either two quotes > (preferred) or backslash-quote." -> remove the comma > "Querying this view is the sames as" -> "the same as" I agree all of the above are improvements, and changed. > "In addition to setting global defaults or attaching" <- add a comma after > "defaults" I didn't like this change as it causes too many comma phrases. Attaches are the changes since yesterday's patch. The full patch is at: ftp://momjian.us/postgresql/mypatches/config.diff -- Bruce Momjian <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs Attachments: [text/x-diff] change.diff (4.7K, 2-change.diff) download | inline diff: commit 01cbe3a4949664f3b001a7d3be7990d37fc8a291 Author: Bruce Momjian <[email protected]> Date: Wed Sep 10 15:49:06 2014 -0400 dummy commit diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml new file mode 100644 index 156c264..4a97855 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *************** *** 10,16 **** <para> There are many configuration parameters that affect the behavior of ! the database system. In the first section of this chapter, we describe how to interact with configuration parameters. The subsequent sections discuss each parameter in detail. </para> --- 10,16 ---- <para> There are many configuration parameters that affect the behavior of ! the database system. In the first section of this chapter we describe how to interact with configuration parameters. The subsequent sections discuss each parameter in detail. </para> *************** *** 31,37 **** <listitem> <para> ! <emphasis>Boolean</emphasis>: Values can be written as <literal>on</literal>, <literal>off</literal>, <literal>true</literal>, --- 31,37 ---- <listitem> <para> ! <emphasis>Boolean:</emphasis> Values can be written as <literal>on</literal>, <literal>off</literal>, <literal>true</literal>, *************** *** 54,61 **** <listitem> <para> ! <emphasis>Numeric</emphasis> (integer and floating point): Do not use ! single-quotes (unless otherwise required) or thousand separators. </para> </listitem> --- 54,62 ---- <listitem> <para> ! <emphasis>Numeric (integer and floating point):</emphasis> Do ! not use single-quotes (unless otherwise required) or thousand ! separators. </para> </listitem> *************** *** 64,71 **** <emphasis>Numeric or String with Unit (Memory & Time):</emphasis> These have an implicit unit, which is either kilobytes, blocks (typically eight kilobytes), ! milliseconds, seconds, or minutes. An numeric value ! will use the default, which can be found by referencing <structname>pg_settings</>.<structfield>unit</>. For convenience, a different unit can also be specified explicitly via a string value. It is case-sensitive and may include whitespace between --- 65,72 ---- <emphasis>Numeric or String with Unit (Memory & Time):</emphasis> These have an implicit unit, which is either kilobytes, blocks (typically eight kilobytes), ! milliseconds, seconds, or minutes. A unadorned numeric ! value will use the default, which can be found by referencing <structname>pg_settings</>.<structfield>unit</>. For convenience, a different unit can also be specified explicitly via a string value. It is case-sensitive and may include whitespace between *************** log_destination = 'syslog' *** 123,134 **** search_path = '"$user", public' shared_buffers = 128MB </programlisting> ! One parameter is specified per line. The equal sign between name ! and value is optional. Whitespace is insignificant and blank ! lines are ignored. Hash marks (<literal>#</literal>) designate the ! remainder of lines as comments. Parameter values that are not simple identifiers or numbers must be single-quoted. To embed a single ! quote in a parameter value, write either two quotes (preferred) or backslash-quote. </para> --- 124,135 ---- search_path = '"$user", public' shared_buffers = 128MB </programlisting> ! One parameter is specified per line. The equal sign between name and ! value is optional. Whitespace is insignificant and blank lines are ! ignored. Hash marks (<literal>#</literal>) designate the remainder ! of the line as a comment. Parameter values that are not simple identifiers or numbers must be single-quoted. To embed a single ! quote in a parameter value write either two quotes (preferred) or backslash-quote. </para> *************** shared_buffers = 128MB *** 229,235 **** <itemizedlist> <listitem> <para> ! Querying this view is the sames as <command>SHOW</> but provides more detail, as well as allowing for joins against other relations and the specification of filter criteria. </para> --- 230,236 ---- <itemizedlist> <listitem> <para> ! Querying this view is the same as <command>SHOW</> but provides more detail, as well as allowing for joins against other relations and the specification of filter criteria. </para> ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-09-11 00:09 David Johnston <[email protected]> parent: Bruce Momjian <[email protected]> 0 siblings, 1 reply; 9+ messages in thread From: David Johnston @ 2014-09-11 00:09 UTC (permalink / raw) To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs On Wed, Sep 10, 2014 at 4:20 PM, Bruce Momjian <[email protected]> wrote: > On Tue, Sep 9, 2014 at 03:40:45PM -0400, David Johnston wrote: > > > "In addition to setting global defaults or attaching" <- add a comma > after > > "defaults" > > I didn't like this change as it causes too many comma phrases. > > Attaches are the changes since yesterday's patch. The full patch is at: > > ftp://momjian.us/postgresql/mypatches/config.diff > > At minimum the word "setting" in that paragraph needs to be made plural. ", you can pass settings to <productname>PostgreSQL..." I'm not particularly fond of the overly long preamble burying the "shell facilities" - and the resultant comma separating the two - but cannot think of anything better. Not having the transition makes it too short and curt. The proposed second comma helped to at least break it up but it too doesn't sound all that great. I'm good for just making the plural fix and calling it a day. David J. ^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? @ 2014-09-11 00:49 Bruce Momjian <[email protected]> parent: David Johnston <[email protected]> 0 siblings, 0 replies; 9+ messages in thread From: Bruce Momjian @ 2014-09-11 00:49 UTC (permalink / raw) To: David Johnston <[email protected]>; +Cc: pgsql-docs On Wed, Sep 10, 2014 at 08:09:30PM -0400, David Johnston wrote: > On Wed, Sep 10, 2014 at 4:20 PM, Bruce Momjian <[email protected]> wrote: > > On Tue, Sep 9, 2014 at 03:40:45PM -0400, David Johnston wrote: > > > "In addition to setting global defaults or attaching" <- add a comma > after > > "defaults" > > I didn't like this change as it causes too many comma phrases. > > Attaches are the changes since yesterday's patch. The full patch is at: > > ftp://momjian.us/postgresql/mypatches/config.diff > > > > At minimum the word "setting" in that paragraph needs to be made plural. ", > you can pass settings to <productname>PostgreSQL..." Oh, good point, fixed. > I'm not particularly fond of the overly long preamble burying the "shell > facilities" - and the resultant comma separating the two - but cannot think of > anything better. Not having the transition makes it too short and curt. The > proposed second comma helped to at least break it up but it too doesn't sound > all that great. > > I'm good for just making the plural fix and calling it a day. Modified patch applied to head and 9.4. -- Bruce Momjian <[email protected]> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2014-09-11 00:49 UTC | newest] Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2014-05-21 19:00 [9.3] Should we mention "set_config(...)" in 18.1.3 in Server Configuration? David G Johnston <[email protected]> 2014-05-21 20:37 ` Tom Lane <[email protected]> 2014-06-08 14:49 ` David Johnston <[email protected]> 2014-06-12 15:32 ` David Johnston <[email protected]> 2014-09-09 19:13 ` Bruce Momjian <[email protected]> 2014-09-09 19:40 ` David Johnston <[email protected]> 2014-09-10 20:20 ` Bruce Momjian <[email protected]> 2014-09-11 00:09 ` David Johnston <[email protected]> 2014-09-11 00:49 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox