doc: improve configuration management section

Patch by David Johnston

Backpatch through 9.4

1 files changed, 241 insertions, 127 deletions

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 49547ee980d..1e5c328d804 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -10,8 +10,8 @@
 
   <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
+   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,47 +23,100 @@
 
     <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.
+     value of one of five types: boolean, integer, floating point,
+     string, or enum.
     </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>
+    <itemizedlist>
 
-    <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>
+     <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 (integer and floating point):</emphasis> Do
+       not use single-quotes (unless otherwise required) or thousand
+       separators.
+      </para>
+     </listitem>
+
+     <listitem>
+      <para>
+       <emphasis>Numeric or String with Unit (Memory &amp;
+       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
+       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>Setting Parameters via the Configuration File</title>
+    <title>Parameter Interaction via Configuration File</title>
 
     <para>
-     One way to set these parameters is to edit the file
+     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:
+     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
@@ -73,125 +126,186 @@ 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.
+     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>
+     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>
+       <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.
+     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-other-methods">
-    <title>Other Ways to Set Parameters</title>
+   <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>
 
-    <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>
+     <itemizedlist>
+      <listitem>
+       <para>
+        The <xref linkend="SQL-ALTERSYSTEM"> command provides an
+        SQL-accessible means of changing global defaults.
+      </para>
+     </listitem>
 
-    <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>
+     <listitem>
+      <para>
+       The <xref linkend="sql-alterdatabase"> command allows database
+       administrators to override global settings on a per-database basis.
+      </para>
+     </listitem>
 
-    <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.
+     <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>
-     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</>.
+     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>
 
-    <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>.
+    <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>
+     </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 id="config-setting-examining">
-    <title>Examining Parameter Settings</title>
+   <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 settings to
+      <productname>PostgreSQL</productname> via shell facilities.
+      Both the server and <application>libpq</> client library
+      accept parameter values via the shell.
+     </para>
 
-    <para>
-     The <xref linkend="SQL-SHOW">
-     command allows inspection of the current values of all parameters.
-    </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>
 
-    <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>
+    <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>