Re-sending to hopefully avoid admin moderation rule. Fixing top-post as
well.
On Wed, May 21, 2014 at 4:37 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
>> David G Johnston <david.g.johns...@gmail.com> 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 (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
