Should we put command options in alphabetical order in the doc?
Over on [1]/messages/by-id/16845cb1-b228-e157-f293-5892bced9253@enterprisedb.com, Peter mentions that we might want to consider putting the
VACUUM options into some order that's better than the apparent random
order that they're currently in.
VACUUM is certainly one command that's grown a fairly good number of
options over the years and it appears we've not given much
consideration to what order to put those in in the documentation.
It's not just VACUUM that has this issue. I see 6 commands using the
following text:
$ git grep "option</replaceable> can be one of"
src/sgml/ref/analyze.sgml: ...
src/sgml/ref/cluster.sgml: ...
src/sgml/ref/copy.sgml: ...
src/sgml/ref/explain.sgml: ...
src/sgml/ref/reindex.sgml: ...
src/sgml/ref/vacuum.sgml: ...
(maybe there's more we should consider adjusting?)
Likely if we do opt to put these options in a more well-defined order,
we should apply that to at least the 6 commands listed above.
For the case of reindex.sgml, I do see that the existing parameter
order lists INDEX | TABLE | SCHEMA | DATABASE | SYSTEM first which is
the target of the reindex. I wondered if that was worth keeping. I'm
just thinking that since all of these are under the "Parameters"
heading that we should class them all as equals and just make the
order alphabetical. I feel that if we don't do that then the order to
add any new parameters is just not going to be obvious and we'll end
up with things getting out of order again quite quickly.
I've attached a patch which makes the changes as I propose them.
David
[1]: /messages/by-id/16845cb1-b228-e157-f293-5892bced9253@enterprisedb.com
Attachments:
alphabetical_order_for_parameter_names.patchapplication/octet-stream; name=alphabetical_order_for_parameter_names.patchDownload
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 20c6f9939f..07dbc9cf44 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -26,9 +26,9 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
- VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
- SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
BUFFER_USAGE_LIMIT [ <replaceable class="parameter">size</replaceable> ]
+ SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
+ VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -70,10 +70,21 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
<variablelist>
<varlistentry>
- <term><literal>VERBOSE</literal></term>
+ <term><literal>BUFFER_USAGE_LIMIT</literal></term>
<listitem>
<para>
- Enables display of progress messages.
+ Specifies the
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ ring buffer size for <command>ANALYZE</command>. This size is used to
+ calculate the number of shared buffers which will be reused as part of
+ this strategy. <literal>0</literal> disables use of a
+ <literal>Buffer Access Strategy</literal>. When this option is not
+ specified, <command>ANALYZE</command> uses the value from
+ <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
+ allow <command>ANALYZE</command> to run more quickly, but having too
+ large a setting may cause too many other useful pages to be evicted from
+ shared buffers. The minimum value is <literal>128 kB</literal> and the
+ maximum value is <literal>16 GB</literal>.
</para>
</listitem>
</varlistentry>
@@ -97,21 +108,10 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</varlistentry>
<varlistentry>
- <term><literal>BUFFER_USAGE_LIMIT</literal></term>
+ <term><literal>VERBOSE</literal></term>
<listitem>
<para>
- Specifies the
- <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
- ring buffer size for <command>ANALYZE</command>. This size is used to
- calculate the number of shared buffers which will be reused as part of
- this strategy. <literal>0</literal> disables use of a
- <literal>Buffer Access Strategy</literal>. When this option is not
- specified, <command>ANALYZE</command> uses the value from
- <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
- allow <command>ANALYZE</command> to run more quickly, but having too
- large a setting may cause too many other useful pages to be evicted from
- shared buffers. The minimum value is <literal>128 kB</literal> and the
- maximum value is <literal>16 GB</literal>.
+ Enables display of progress messages.
</para>
</listitem>
</varlistentry>
@@ -130,6 +130,15 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">column_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a specific column to analyze. Defaults to all columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">size</replaceable></term>
<listitem>
@@ -156,15 +165,6 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
</para>
</listitem>
</varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">column_name</replaceable></term>
- <listitem>
- <para>
- The name of a specific column to analyze. Defaults to all columns.
- </para>
- </listitem>
- </varlistentry>
</variablelist>
</refsect1>
diff --git a/doc/src/sgml/ref/cluster.sgml b/doc/src/sgml/ref/cluster.sgml
index 29f0f1fd90..b81542cf70 100644
--- a/doc/src/sgml/ref/cluster.sgml
+++ b/doc/src/sgml/ref/cluster.sgml
@@ -87,42 +87,42 @@ CLUSTER [VERBOSE]
<variablelist>
<varlistentry>
- <term><replaceable class="parameter">table_name</replaceable></term>
+ <term><literal>VERBOSE</literal></term>
<listitem>
<para>
- The name (possibly schema-qualified) of a table.
+ Prints a progress report as each table is clustered.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">index_name</replaceable></term>
+ <term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
<para>
- The name of an index.
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</literal>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>VERBOSE</literal></term>
+ <term><replaceable class="parameter">index_name</replaceable></term>
<listitem>
<para>
- Prints a progress report as each table is clustered.
+ The name of an index.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">boolean</replaceable></term>
+ <term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
<para>
- Specifies whether the selected option should be turned on or off.
- You can write <literal>TRUE</literal>, <literal>ON</literal>, or
- <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
- <literal>OFF</literal>, or <literal>0</literal> to disable it. The
- <replaceable class="parameter">boolean</replaceable> value can also
- be omitted, in which case <literal>TRUE</literal> is assumed.
+ The name (possibly schema-qualified) of a table.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 5e591ed2e6..0cbcb7bccd 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -32,18 +32,18 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
- FORMAT <replaceable class="parameter">format_name</replaceable>
- FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
+ DEFAULT '<replaceable class="parameter">default_string</replaceable>'
DELIMITER '<replaceable class="parameter">delimiter_character</replaceable>'
- NULL '<replaceable class="parameter">null_string</replaceable>'
- HEADER [ <replaceable class="parameter">boolean</replaceable> | MATCH ]
- QUOTE '<replaceable class="parameter">quote_character</replaceable>'
+ ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
ESCAPE '<replaceable class="parameter">escape_character</replaceable>'
- FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
FORCE_NOT_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
FORCE_NULL ( <replaceable class="parameter">column_name</replaceable> [, ...] )
- ENCODING '<replaceable class="parameter">encoding_name</replaceable>'
- DEFAULT '<replaceable class="parameter">default_string</replaceable>'
+ FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * }
+ FORMAT <replaceable class="parameter">format_name</replaceable>
+ FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
+ HEADER [ <replaceable class="parameter">boolean</replaceable> | MATCH ]
+ NULL '<replaceable class="parameter">null_string</replaceable>'
+ QUOTE '<replaceable class="parameter">quote_character</replaceable>'
</synopsis>
</refsynopsisdiv>
@@ -96,105 +96,95 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
<variablelist>
<varlistentry>
- <term><replaceable class="parameter">table_name</replaceable></term>
+ <term><literal>DEFAULT</literal></term>
<listitem>
<para>
- The name (optionally schema-qualified) of an existing table.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">column_name</replaceable></term>
- <listitem>
- <para>
- An optional list of columns to be copied. If no column list is
- specified, all columns of the table except generated columns will be
- copied.
+ Specifies the string that represents a default value. Each time the string
+ is found in the input file, the default value of the corresponding column
+ will be used.
+ This option is allowed only in <command>COPY FROM</command>, and only when
+ not using <literal>binary</literal> format.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">query</replaceable></term>
+ <term><literal>DELIMITER</literal></term>
<listitem>
<para>
- A <link linkend="sql-select"><command>SELECT</command></link>,
- <link linkend="sql-values"><command>VALUES</command></link>,
- <link linkend="sql-insert"><command>INSERT</command></link>,
- <link linkend="sql-update"><command>UPDATE</command></link>, or
- <link linkend="sql-delete"><command>DELETE</command></link> command whose results are to be
- copied. Note that parentheses are required around the query.
- </para>
- <para>
- For <command>INSERT</command>, <command>UPDATE</command> and
- <command>DELETE</command> queries a RETURNING clause must be provided,
- and the target relation must not have a conditional rule, nor
- an <literal>ALSO</literal> rule, nor an <literal>INSTEAD</literal> rule
- that expands to multiple statements.
+ Specifies the character that separates columns within each row
+ (line) of the file. The default is a tab character in text format,
+ a comma in <literal>CSV</literal> format.
+ This must be a single one-byte character.
+ This option is not allowed when using <literal>binary</literal> format.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">filename</replaceable></term>
+ <term><literal>ENCODING</literal></term>
<listitem>
<para>
- The path name of the input or output file. An input file name can be
- an absolute or relative path, but an output file name must be an absolute
- path. Windows users might need to use an <literal>E''</literal> string and
- double any backslashes used in the path name.
+ Specifies that the file is encoded in the <replaceable
+ class="parameter">encoding_name</replaceable>. If this option is
+ omitted, the current client encoding is used. See the Notes below
+ for more details.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>PROGRAM</literal></term>
+ <term><literal>ESCAPE</literal></term>
<listitem>
<para>
- A command to execute. In <command>COPY FROM</command>, the input is
- read from standard output of the command, and in <command>COPY TO</command>,
- the output is written to the standard input of the command.
- </para>
- <para>
- Note that the command is invoked by the shell, so if you need to pass
- any arguments that come from an untrusted source, you
- must be careful to strip or escape any special characters that might
- have a special meaning for the shell. For security reasons, it is best
- to use a fixed command string, or at least avoid including any user input
- in it.
+ Specifies the character that should appear before a
+ data character that matches the <literal>QUOTE</literal> value.
+ The default is the same as the <literal>QUOTE</literal> value (so that
+ the quoting character is doubled if it appears in the data).
+ This must be a single one-byte character.
+ This option is allowed only when using <literal>CSV</literal> format.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>STDIN</literal></term>
+ <term><literal>FORCE_NOT_NULL</literal></term>
<listitem>
<para>
- Specifies that input comes from the client application.
+ Do not match the specified columns' values against the null string.
+ In the default case where the null string is empty, this means that
+ empty values will be read as zero-length strings rather than nulls,
+ even when they are not quoted.
+ This option is allowed only in <command>COPY FROM</command>, and only when
+ using <literal>CSV</literal> format.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>STDOUT</literal></term>
+ <term><literal>FORCE_NULL</literal></term>
<listitem>
<para>
- Specifies that output goes to the client application.
+ Match the specified columns' values against the null string, even
+ if it has been quoted, and if a match is found set the value to
+ <literal>NULL</literal>. In the default case where the null string is empty,
+ this converts a quoted empty string into NULL.
+ This option is allowed only in <command>COPY FROM</command>, and only when
+ using <literal>CSV</literal> format.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">boolean</replaceable></term>
+ <term><literal>FORCE_QUOTE</literal></term>
<listitem>
<para>
- Specifies whether the selected option should be turned on or off.
- You can write <literal>TRUE</literal>, <literal>ON</literal>, or
- <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
- <literal>OFF</literal>, or <literal>0</literal> to disable it. The
- <replaceable class="parameter">boolean</replaceable> value can also
- be omitted, in which case <literal>TRUE</literal> is assumed.
+ Forces quoting to be
+ used for all non-<literal>NULL</literal> values in each specified column.
+ <literal>NULL</literal> output is never quoted. If <literal>*</literal> is specified,
+ non-<literal>NULL</literal> values will be quoted in all columns.
+ This option is allowed only in <command>COPY TO</command>, and only when
+ using <literal>CSV</literal> format.
</para>
</listitem>
</varlistentry>
@@ -235,14 +225,19 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
</varlistentry>
<varlistentry>
- <term><literal>DELIMITER</literal></term>
+ <term><literal>HEADER</literal></term>
<listitem>
<para>
- Specifies the character that separates columns within each row
- (line) of the file. The default is a tab character in text format,
- a comma in <literal>CSV</literal> format.
- This must be a single one-byte character.
+ Specifies that the file contains a header line with the names of each
+ column in the file. On output, the first line contains the column
+ names from the table. On input, the first line is discarded when this
+ option is set to <literal>true</literal> (or equivalent Boolean value).
+ If this option is set to <literal>MATCH</literal>, the number and names
+ of the columns in the header line must match the actual column names of
+ the table, in order; otherwise an error is raised.
This option is not allowed when using <literal>binary</literal> format.
+ The <literal>MATCH</literal> option is only valid for <command>COPY
+ FROM</command> commands.
</para>
</listitem>
</varlistentry>
@@ -272,19 +267,20 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
</varlistentry>
<varlistentry>
- <term><literal>HEADER</literal></term>
+ <term><literal>PROGRAM</literal></term>
<listitem>
<para>
- Specifies that the file contains a header line with the names of each
- column in the file. On output, the first line contains the column
- names from the table. On input, the first line is discarded when this
- option is set to <literal>true</literal> (or equivalent Boolean value).
- If this option is set to <literal>MATCH</literal>, the number and names
- of the columns in the header line must match the actual column names of
- the table, in order; otherwise an error is raised.
- This option is not allowed when using <literal>binary</literal> format.
- The <literal>MATCH</literal> option is only valid for <command>COPY
- FROM</command> commands.
+ A command to execute. In <command>COPY FROM</command>, the input is
+ read from standard output of the command, and in <command>COPY TO</command>,
+ the output is written to the standard input of the command.
+ </para>
+ <para>
+ Note that the command is invoked by the shell, so if you need to pass
+ any arguments that come from an untrusted source, you
+ must be careful to strip or escape any special characters that might
+ have a special meaning for the shell. For security reasons, it is best
+ to use a fixed command string, or at least avoid including any user input
+ in it.
</para>
</listitem>
</varlistentry>
@@ -302,109 +298,113 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
</varlistentry>
<varlistentry>
- <term><literal>ESCAPE</literal></term>
+ <term><literal>STDIN</literal></term>
<listitem>
<para>
- Specifies the character that should appear before a
- data character that matches the <literal>QUOTE</literal> value.
- The default is the same as the <literal>QUOTE</literal> value (so that
- the quoting character is doubled if it appears in the data).
- This must be a single one-byte character.
- This option is allowed only when using <literal>CSV</literal> format.
+ Specifies that input comes from the client application.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>FORCE_QUOTE</literal></term>
+ <term><literal>STDOUT</literal></term>
<listitem>
<para>
- Forces quoting to be
- used for all non-<literal>NULL</literal> values in each specified column.
- <literal>NULL</literal> output is never quoted. If <literal>*</literal> is specified,
- non-<literal>NULL</literal> values will be quoted in all columns.
- This option is allowed only in <command>COPY TO</command>, and only when
- using <literal>CSV</literal> format.
+ Specifies that output goes to the client application.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>FORCE_NOT_NULL</literal></term>
+ <term><literal>WHERE</literal></term>
+ <listitem>
+ <para>
+ The optional <literal>WHERE</literal> clause has the general form
+<synopsis>
+WHERE <replaceable class="parameter">condition</replaceable>
+</synopsis>
+ where <replaceable class="parameter">condition</replaceable> is
+ any expression that evaluates to a result of type
+ <type>boolean</type>. Any row that does not satisfy this
+ condition will not be inserted to the table. A row satisfies the
+ condition if it returns true when the actual row values are
+ substituted for any variable references.
+ </para>
+
+ <para>
+ Currently, subqueries are not allowed in <literal>WHERE</literal>
+ expressions, and the evaluation does not see any changes made by the
+ <command>COPY</command> itself (this matters when the expression
+ contains calls to <literal>VOLATILE</literal> functions).
+ </para>
+
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">boolean</replaceable></term>
<listitem>
<para>
- Do not match the specified columns' values against the null string.
- In the default case where the null string is empty, this means that
- empty values will be read as zero-length strings rather than nulls,
- even when they are not quoted.
- This option is allowed only in <command>COPY FROM</command>, and only when
- using <literal>CSV</literal> format.
+ Specifies whether the selected option should be turned on or off.
+ You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+ <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+ <literal>OFF</literal>, or <literal>0</literal> to disable it. The
+ <replaceable class="parameter">boolean</replaceable> value can also
+ be omitted, in which case <literal>TRUE</literal> is assumed.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>FORCE_NULL</literal></term>
- <listitem>
+ <term><replaceable class="parameter">column_name</replaceable></term>
+ <listitem>
<para>
- Match the specified columns' values against the null string, even
- if it has been quoted, and if a match is found set the value to
- <literal>NULL</literal>. In the default case where the null string is empty,
- this converts a quoted empty string into NULL.
- This option is allowed only in <command>COPY FROM</command>, and only when
- using <literal>CSV</literal> format.
+ An optional list of columns to be copied. If no column list is
+ specified, all columns of the table except generated columns will be
+ copied.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>ENCODING</literal></term>
+ <term><replaceable class="parameter">filename</replaceable></term>
<listitem>
<para>
- Specifies that the file is encoded in the <replaceable
- class="parameter">encoding_name</replaceable>. If this option is
- omitted, the current client encoding is used. See the Notes below
- for more details.
+ The path name of the input or output file. An input file name can be
+ an absolute or relative path, but an output file name must be an absolute
+ path. Windows users might need to use an <literal>E''</literal> string and
+ double any backslashes used in the path name.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>DEFAULT</literal></term>
+ <term><replaceable class="parameter">query</replaceable></term>
<listitem>
<para>
- Specifies the string that represents a default value. Each time the string
- is found in the input file, the default value of the corresponding column
- will be used.
- This option is allowed only in <command>COPY FROM</command>, and only when
- not using <literal>binary</literal> format.
+ A <link linkend="sql-select"><command>SELECT</command></link>,
+ <link linkend="sql-values"><command>VALUES</command></link>,
+ <link linkend="sql-insert"><command>INSERT</command></link>,
+ <link linkend="sql-update"><command>UPDATE</command></link>, or
+ <link linkend="sql-delete"><command>DELETE</command></link> command whose results are to be
+ copied. Note that parentheses are required around the query.
+ </para>
+ <para>
+ For <command>INSERT</command>, <command>UPDATE</command> and
+ <command>DELETE</command> queries a RETURNING clause must be provided,
+ and the target relation must not have a conditional rule, nor
+ an <literal>ALSO</literal> rule, nor an <literal>INSTEAD</literal> rule
+ that expands to multiple statements.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>WHERE</literal></term>
+ <term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
- <para>
- The optional <literal>WHERE</literal> clause has the general form
-<synopsis>
-WHERE <replaceable class="parameter">condition</replaceable>
-</synopsis>
- where <replaceable class="parameter">condition</replaceable> is
- any expression that evaluates to a result of type
- <type>boolean</type>. Any row that does not satisfy this
- condition will not be inserted to the table. A row satisfies the
- condition if it returns true when the actual row values are
- substituted for any variable references.
- </para>
-
- <para>
- Currently, subqueries are not allowed in <literal>WHERE</literal>
- expressions, and the evaluation does not see any changes made by the
- <command>COPY</command> itself (this matters when the expression
- contains calls to <literal>VOLATILE</literal> functions).
- </para>
-
+ <para>
+ The name (optionally schema-qualified) of an existing table.
+ </para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 410490951b..4df028ffd6 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -37,15 +37,15 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replac
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
- VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+ BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
COSTS [ <replaceable class="parameter">boolean</replaceable> ]
- SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]
+ FORMAT { TEXT | XML | JSON | YAML }
GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ]
- BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
- WAL [ <replaceable class="parameter">boolean</replaceable> ]
- TIMING [ <replaceable class="parameter">boolean</replaceable> ]
+ SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]
SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
- FORMAT { TEXT | XML | JSON | YAML }
+ TIMING [ <replaceable class="parameter">boolean</replaceable> ]
+ VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+ WAL [ <replaceable class="parameter">boolean</replaceable> ]
</synopsis>
</refsynopsisdiv>
@@ -131,17 +131,30 @@ ROLLBACK;
</varlistentry>
<varlistentry>
- <term><literal>VERBOSE</literal></term>
+ <term><literal>BUFFERS</literal></term>
<listitem>
<para>
- Display additional information regarding the plan. Specifically, include
- the output column list for each node in the plan tree, schema-qualify
- table and function names, always label variables in expressions with
- their range table alias, and always print the name of each trigger for
- which statistics are displayed. The query identifier will also be
- displayed if one has been computed, see <xref
- linkend="guc-compute-query-id"/> for more details. This parameter
- defaults to <literal>FALSE</literal>.
+ Include information on buffer usage. Specifically, include the number of
+ shared blocks hit, read, dirtied, and written, the number of local blocks
+ hit, read, dirtied, and written, the number of temp blocks read and
+ written, and the time spent reading and writing data file blocks and
+ temporary file blocks (in milliseconds) if
+ <xref linkend="guc-track-io-timing"/> is enabled. A
+ <emphasis>hit</emphasis> means that a read was avoided because the block
+ was found already in cache when needed.
+ Shared blocks contain data from regular tables and indexes;
+ local blocks contain data from temporary tables and indexes;
+ while temporary blocks contain short-term working data used in sorts,
+ hashes, Materialize plan nodes, and similar cases.
+ The number of blocks <emphasis>dirtied</emphasis> indicates the number of
+ previously unmodified blocks that were changed by this query; while the
+ number of blocks <emphasis>written</emphasis> indicates the number of
+ previously-dirtied blocks evicted from cache by this backend during
+ query processing.
+ The number of blocks shown for an
+ upper-level node includes those used by all its child nodes. In text
+ format, only non-zero values are printed. This parameter defaults to
+ <literal>FALSE</literal>.
</para>
</listitem>
</varlistentry>
@@ -159,12 +172,13 @@ ROLLBACK;
</varlistentry>
<varlistentry>
- <term><literal>SETTINGS</literal></term>
+ <term><literal>FORMAT</literal></term>
<listitem>
<para>
- Include information on configuration parameters. Specifically, include
- options affecting query planning with value different from the built-in
- default value. This parameter defaults to <literal>FALSE</literal>.
+ Specify the output format, which can be TEXT, XML, JSON, or YAML.
+ Non-text output contains the same information as the text output
+ format, but is easier for programs to parse. This parameter defaults to
+ <literal>TEXT</literal>.
</para>
</listitem>
</varlistentry>
@@ -186,43 +200,27 @@ ROLLBACK;
</varlistentry>
<varlistentry>
- <term><literal>BUFFERS</literal></term>
+ <term><literal>SETTINGS</literal></term>
<listitem>
<para>
- Include information on buffer usage. Specifically, include the number of
- shared blocks hit, read, dirtied, and written, the number of local blocks
- hit, read, dirtied, and written, the number of temp blocks read and
- written, and the time spent reading and writing data file blocks and
- temporary file blocks (in milliseconds) if
- <xref linkend="guc-track-io-timing"/> is enabled. A
- <emphasis>hit</emphasis> means that a read was avoided because the block
- was found already in cache when needed.
- Shared blocks contain data from regular tables and indexes;
- local blocks contain data from temporary tables and indexes;
- while temporary blocks contain short-term working data used in sorts,
- hashes, Materialize plan nodes, and similar cases.
- The number of blocks <emphasis>dirtied</emphasis> indicates the number of
- previously unmodified blocks that were changed by this query; while the
- number of blocks <emphasis>written</emphasis> indicates the number of
- previously-dirtied blocks evicted from cache by this backend during
- query processing.
- The number of blocks shown for an
- upper-level node includes those used by all its child nodes. In text
- format, only non-zero values are printed. This parameter defaults to
- <literal>FALSE</literal>.
+ Include information on configuration parameters. Specifically, include
+ options affecting query planning with value different from the built-in
+ default value. This parameter defaults to <literal>FALSE</literal>.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>WAL</literal></term>
+ <term><literal>SUMMARY</literal></term>
<listitem>
<para>
- Include information on WAL record generation. Specifically, include the
- number of records, number of full page images (fpi) and the amount of WAL
- generated in bytes. In text format, only non-zero values are printed.
- This parameter may only be used when <literal>ANALYZE</literal> is also
- enabled. It defaults to <literal>FALSE</literal>.
+ Include summary information (e.g., totaled timing information) after the
+ query plan. Summary information is included by default when
+ <literal>ANALYZE</literal> is used but otherwise is not included by
+ default, but can be enabled using this option. Planning time in
+ <command>EXPLAIN EXECUTE</command> includes the time required to fetch
+ the plan from the cache and the time required for re-planning, if
+ necessary.
</para>
</listitem>
</varlistentry>
@@ -245,28 +243,30 @@ ROLLBACK;
</varlistentry>
<varlistentry>
- <term><literal>SUMMARY</literal></term>
+ <term><literal>VERBOSE</literal></term>
<listitem>
<para>
- Include summary information (e.g., totaled timing information) after the
- query plan. Summary information is included by default when
- <literal>ANALYZE</literal> is used but otherwise is not included by
- default, but can be enabled using this option. Planning time in
- <command>EXPLAIN EXECUTE</command> includes the time required to fetch
- the plan from the cache and the time required for re-planning, if
- necessary.
+ Display additional information regarding the plan. Specifically, include
+ the output column list for each node in the plan tree, schema-qualify
+ table and function names, always label variables in expressions with
+ their range table alias, and always print the name of each trigger for
+ which statistics are displayed. The query identifier will also be
+ displayed if one has been computed, see <xref
+ linkend="guc-compute-query-id"/> for more details. This parameter
+ defaults to <literal>FALSE</literal>.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>FORMAT</literal></term>
+ <term><literal>WAL</literal></term>
<listitem>
<para>
- Specify the output format, which can be TEXT, XML, JSON, or YAML.
- Non-text output contains the same information as the text output
- format, but is easier for programs to parse. This parameter defaults to
- <literal>TEXT</literal>.
+ Include information on WAL record generation. Specifically, include the
+ number of records, number of full page images (fpi) and the amount of WAL
+ generated in bytes. In text format, only non-zero values are printed.
+ This parameter may only be used when <literal>ANALYZE</literal> is also
+ enabled. It defaults to <literal>FALSE</literal>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 71455dfdc7..eb7fcbcc0b 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -88,48 +88,55 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DA
<variablelist>
<varlistentry>
- <term><literal>INDEX</literal></term>
+ <term><literal>CONCURRENTLY</literal></term>
<listitem>
<para>
- Recreate the specified index. This form of <command>REINDEX</command>
- cannot be executed inside a transaction block when used with a
- partitioned index.
+ When this option is used, <productname>PostgreSQL</productname> will rebuild the
+ index without taking any locks that prevent concurrent inserts,
+ updates, or deletes on the table; whereas a standard index rebuild
+ locks out writes (but not reads) on the table until it's done.
+ There are several caveats to be aware of when using this option
+ — see <xref linkend="sql-reindex-concurrently"/> below.
+ </para>
+ <para>
+ For temporary tables, <command>REINDEX</command> is always
+ non-concurrent, as no other session can access them, and
+ non-concurrent reindex is cheaper.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>TABLE</literal></term>
+ <term><literal>DATABASE</literal></term>
<listitem>
<para>
- Recreate all indexes of the specified table. If the table has a
- secondary <quote>TOAST</quote> table, that is reindexed as well.
+ Recreate all indexes within the current database, except system
+ catalogs.
+ Indexes on system catalogs are not processed.
This form of <command>REINDEX</command> cannot be executed inside a
- transaction block when used with a partitioned table.
+ transaction block.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>SCHEMA</literal></term>
+ <term><literal>INDEX</literal></term>
<listitem>
<para>
- Recreate all indexes of the specified schema. If a table of this
- schema has a secondary <quote>TOAST</quote> table, that is reindexed as
- well. Indexes on shared system catalogs are also processed.
- This form of <command>REINDEX</command> cannot be executed inside a
- transaction block.
+ Recreate the specified index. This form of <command>REINDEX</command>
+ cannot be executed inside a transaction block when used with a
+ partitioned index.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>DATABASE</literal></term>
+ <term><literal>SCHEMA</literal></term>
<listitem>
<para>
- Recreate all indexes within the current database, except system
- catalogs.
- Indexes on system catalogs are not processed.
+ Recreate all indexes of the specified schema. If a table of this
+ schema has a secondary <quote>TOAST</quote> table, that is reindexed as
+ well. Indexes on shared system catalogs are also processed.
This form of <command>REINDEX</command> cannot be executed inside a
transaction block.
</para>
@@ -150,33 +157,13 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DA
</varlistentry>
<varlistentry>
- <term><replaceable class="parameter">name</replaceable></term>
- <listitem>
- <para>
- The name of the specific index, table, or database to be
- reindexed. Index and table names can be schema-qualified.
- Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command>
- can only reindex the current database. Their parameter is optional,
- and it must match the current database's name.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>CONCURRENTLY</literal></term>
+ <term><literal>TABLE</literal></term>
<listitem>
<para>
- When this option is used, <productname>PostgreSQL</productname> will rebuild the
- index without taking any locks that prevent concurrent inserts,
- updates, or deletes on the table; whereas a standard index rebuild
- locks out writes (but not reads) on the table until it's done.
- There are several caveats to be aware of when using this option
- — see <xref linkend="sql-reindex-concurrently"/> below.
- </para>
- <para>
- For temporary tables, <command>REINDEX</command> is always
- non-concurrent, as no other session can access them, and
- non-concurrent reindex is cheaper.
+ Recreate all indexes of the specified table. If the table has a
+ secondary <quote>TOAST</quote> table, that is reindexed as well.
+ This form of <command>REINDEX</command> cannot be executed inside a
+ transaction block when used with a partitioned table.
</para>
</listitem>
</varlistentry>
@@ -213,6 +200,19 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DA
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name of the specific index, table, or database to be
+ reindexed. Index and table names can be schema-qualified.
+ Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command>
+ can only reindex the current database. Their parameter is optional,
+ and it must match the current database's name.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">new_tablespace</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index 57bc4c23ec..a3a4224ec7 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -26,20 +26,20 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
- FULL [ <replaceable class="parameter">boolean</replaceable> ]
- FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
- VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
ANALYZE [ <replaceable class="parameter">boolean</replaceable> ]
+ BUFFER_USAGE_LIMIT [ <replaceable class="parameter">size</replaceable> ]
DISABLE_PAGE_SKIPPING [ <replaceable class="parameter">boolean</replaceable> ]
- SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
+ FREEZE [ <replaceable class="parameter">boolean</replaceable> ]
+ FULL [ <replaceable class="parameter">boolean</replaceable> ]
INDEX_CLEANUP { AUTO | ON | OFF }
+ ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
+ PARALLEL <replaceable class="parameter">integer</replaceable>
PROCESS_MAIN [ <replaceable class="parameter">boolean</replaceable> ]
PROCESS_TOAST [ <replaceable class="parameter">boolean</replaceable> ]
- TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
- PARALLEL <replaceable class="parameter">integer</replaceable>
SKIP_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
- ONLY_DATABASE_STATS [ <replaceable class="parameter">boolean</replaceable> ]
- BUFFER_USAGE_LIMIT [ <replaceable class="parameter">size</replaceable> ]
+ SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
+ TRUNCATE [ <replaceable class="parameter">boolean</replaceable> ]
+ VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
@@ -106,50 +106,35 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<variablelist>
<varlistentry>
- <term><literal>FULL</literal></term>
- <listitem>
- <para>
- Selects <quote>full</quote> vacuum, which can reclaim more
- space, but takes much longer and exclusively locks the table.
- This method also requires extra disk space, since it writes a
- new copy of the table and doesn't release the old copy until
- the operation is complete. Usually this should only be used when a
- significant amount of space needs to be reclaimed from within the table.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>FREEZE</literal></term>
- <listitem>
- <para>
- Selects aggressive <quote>freezing</quote> of tuples.
- Specifying <literal>FREEZE</literal> is equivalent to performing
- <command>VACUUM</command> with the
- <xref linkend="guc-vacuum-freeze-min-age"/> and
- <xref linkend="guc-vacuum-freeze-table-age"/> parameters
- set to zero. Aggressive freezing is always performed when the
- table is rewritten, so this option is redundant when <literal>FULL</literal>
- is specified.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>VERBOSE</literal></term>
+ <term><literal>ANALYZE</literal></term>
<listitem>
<para>
- Prints a detailed vacuum activity report for each table.
+ Updates statistics used by the planner to determine the most
+ efficient way to execute a query.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>ANALYZE</literal></term>
+ <term><literal>BUFFER_USAGE_LIMIT</literal></term>
<listitem>
<para>
- Updates statistics used by the planner to determine the most
- efficient way to execute a query.
+ Specifies the
+ <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+ ring buffer size for <command>VACUUM</command>. This size is used to
+ calculate the number of shared buffers which will be reused as part of
+ this strategy. <literal>0</literal> disables use of a
+ <literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>
+ is also specified, the <option>BUFFER_USAGE_LIMIT</option> value is used
+ for both the vacuum and analyze stages. This option can't be used with
+ the <option>FULL</option> option except if <option>ANALYZE</option> is
+ also specified. When this option is not specified,
+ <command>VACUUM</command> uses the value from
+ <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
+ allow <command>VACUUM</command> to run more quickly, but having too
+ large a setting may cause too many other useful pages to be evicted from
+ shared buffers. The minimum value is <literal>128 kB</literal> and the
+ maximum value is <literal>16 GB</literal>.
</para>
</listitem>
</varlistentry>
@@ -174,21 +159,31 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</varlistentry>
<varlistentry>
- <term><literal>SKIP_LOCKED</literal></term>
+ <term><literal>FREEZE</literal></term>
<listitem>
<para>
- Specifies that <command>VACUUM</command> should not wait for any
- conflicting locks to be released when beginning work on a relation:
- if a relation cannot be locked immediately without waiting, the relation
- is skipped. Note that even with this option,
- <command>VACUUM</command> may still block when opening the relation's
- indexes. Additionally, <command>VACUUM ANALYZE</command> may still
- block when acquiring sample rows from partitions, table inheritance
- children, and some types of foreign tables. Also, while
- <command>VACUUM</command> ordinarily processes all partitions of
- specified partitioned tables, this option will cause
- <command>VACUUM</command> to skip all partitions if there is a
- conflicting lock on the partitioned table.
+ Selects aggressive <quote>freezing</quote> of tuples.
+ Specifying <literal>FREEZE</literal> is equivalent to performing
+ <command>VACUUM</command> with the
+ <xref linkend="guc-vacuum-freeze-min-age"/> and
+ <xref linkend="guc-vacuum-freeze-table-age"/> parameters
+ set to zero. Aggressive freezing is always performed when the
+ table is rewritten, so this option is redundant when <literal>FULL</literal>
+ is specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>FULL</literal></term>
+ <listitem>
+ <para>
+ Selects <quote>full</quote> vacuum, which can reclaim more
+ space, but takes much longer and exclusively locks the table.
+ This method also requires extra disk space, since it writes a
+ new copy of the table and doesn't release the old copy until
+ the operation is complete. Usually this should only be used when a
+ significant amount of space needs to be reclaimed from within the table.
</para>
</listitem>
</varlistentry>
@@ -241,45 +236,15 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</varlistentry>
<varlistentry>
- <term><literal>PROCESS_MAIN</literal></term>
- <listitem>
- <para>
- Specifies that <command>VACUUM</command> should attempt to process the
- main relation. This is usually the desired behavior and is the default.
- Setting this option to false may be useful when it is only necessary to
- vacuum a relation's corresponding <literal>TOAST</literal> table.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>PROCESS_TOAST</literal></term>
- <listitem>
- <para>
- Specifies that <command>VACUUM</command> should attempt to process the
- corresponding <literal>TOAST</literal> table for each relation, if one
- exists. This is usually the desired behavior and is the default.
- Setting this option to false may be useful when it is only necessary to
- vacuum the main relation. This option is required when the
- <literal>FULL</literal> option is used.
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>TRUNCATE</literal></term>
+ <term><literal>ONLY_DATABASE_STATS</literal></term>
<listitem>
<para>
- Specifies that <command>VACUUM</command> should attempt to
- truncate off any empty pages at the end of the table and allow
- the disk space for the truncated pages to be returned to
- the operating system. This is normally the desired behavior
- and is the default unless the <literal>vacuum_truncate</literal>
- option has been set to false for the table to be vacuumed.
- Setting this option to false may be useful to avoid
- <literal>ACCESS EXCLUSIVE</literal> lock on the table that
- the truncation requires. This option is ignored if the
- <literal>FULL</literal> option is used.
+ Specifies that <command>VACUUM</command> should do nothing except
+ update the database-wide statistics about oldest unfrozen XIDs.
+ When this option is specified,
+ the <replaceable class="parameter">table_and_columns</replaceable>
+ list must be empty, and no other option may be enabled
+ except <literal>VERBOSE</literal>.
</para>
</listitem>
</varlistentry>
@@ -311,6 +276,32 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>PROCESS_MAIN</literal></term>
+ <listitem>
+ <para>
+ Specifies that <command>VACUUM</command> should attempt to process the
+ main relation. This is usually the desired behavior and is the default.
+ Setting this option to false may be useful when it is only necessary to
+ vacuum a relation's corresponding <literal>TOAST</literal> table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>PROCESS_TOAST</literal></term>
+ <listitem>
+ <para>
+ Specifies that <command>VACUUM</command> should attempt to process the
+ corresponding <literal>TOAST</literal> table for each relation, if one
+ exists. This is usually the desired behavior and is the default.
+ Setting this option to false may be useful when it is only necessary to
+ vacuum the main relation. This option is required when the
+ <literal>FULL</literal> option is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SKIP_DATABASE_STATS</literal></term>
<listitem>
@@ -333,39 +324,48 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</varlistentry>
<varlistentry>
- <term><literal>ONLY_DATABASE_STATS</literal></term>
+ <term><literal>SKIP_LOCKED</literal></term>
<listitem>
<para>
- Specifies that <command>VACUUM</command> should do nothing except
- update the database-wide statistics about oldest unfrozen XIDs.
- When this option is specified,
- the <replaceable class="parameter">table_and_columns</replaceable>
- list must be empty, and no other option may be enabled
- except <literal>VERBOSE</literal>.
+ Specifies that <command>VACUUM</command> should not wait for any
+ conflicting locks to be released when beginning work on a relation:
+ if a relation cannot be locked immediately without waiting, the relation
+ is skipped. Note that even with this option,
+ <command>VACUUM</command> may still block when opening the relation's
+ indexes. Additionally, <command>VACUUM ANALYZE</command> may still
+ block when acquiring sample rows from partitions, table inheritance
+ children, and some types of foreign tables. Also, while
+ <command>VACUUM</command> ordinarily processes all partitions of
+ specified partitioned tables, this option will cause
+ <command>VACUUM</command> to skip all partitions if there is a
+ conflicting lock on the partitioned table.
</para>
</listitem>
</varlistentry>
<varlistentry>
- <term><literal>BUFFER_USAGE_LIMIT</literal></term>
+ <term><literal>TRUNCATE</literal></term>
<listitem>
<para>
- Specifies the
- <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
- ring buffer size for <command>VACUUM</command>. This size is used to
- calculate the number of shared buffers which will be reused as part of
- this strategy. <literal>0</literal> disables use of a
- <literal>Buffer Access Strategy</literal>. If <option>ANALYZE</option>
- is also specified, the <option>BUFFER_USAGE_LIMIT</option> value is used
- for both the vacuum and analyze stages. This option can't be used with
- the <option>FULL</option> option except if <option>ANALYZE</option> is
- also specified. When this option is not specified,
- <command>VACUUM</command> uses the value from
- <xref linkend="guc-vacuum-buffer-usage-limit"/>. Higher settings can
- allow <command>VACUUM</command> to run more quickly, but having too
- large a setting may cause too many other useful pages to be evicted from
- shared buffers. The minimum value is <literal>128 kB</literal> and the
- maximum value is <literal>16 GB</literal>.
+ Specifies that <command>VACUUM</command> should attempt to
+ truncate off any empty pages at the end of the table and allow
+ the disk space for the truncated pages to be returned to
+ the operating system. This is normally the desired behavior
+ and is the default unless the <literal>vacuum_truncate</literal>
+ option has been set to false for the table to be vacuumed.
+ Setting this option to false may be useful to avoid
+ <literal>ACCESS EXCLUSIVE</literal> lock on the table that
+ the truncation requires. This option is ignored if the
+ <literal>FULL</literal> option is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>VERBOSE</literal></term>
+ <listitem>
+ <para>
+ Prints a detailed vacuum activity report for each table.
</para>
</listitem>
</varlistentry>
@@ -384,6 +384,17 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">column_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a specific column to analyze. Defaults to all columns.
+ If a column list is specified, <literal>ANALYZE</literal> must also be
+ specified.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">integer</replaceable></term>
<listitem>
@@ -416,17 +427,6 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
</para>
</listitem>
</varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">column_name</replaceable></term>
- <listitem>
- <para>
- The name of a specific column to analyze. Defaults to all columns.
- If a column list is specified, <literal>ANALYZE</literal> must also be
- specified.
- </para>
- </listitem>
- </varlistentry>
</variablelist>
</refsect1>
On Mon, Apr 17, 2023 at 10:45 PM David Rowley <dgrowleyml@gmail.com> wrote:
For the case of reindex.sgml, I do see that the existing parameter
order lists INDEX | TABLE | SCHEMA | DATABASE | SYSTEM first which is
the target of the reindex. I wondered if that was worth keeping. I'm
just thinking that since all of these are under the "Parameters"
heading that we should class them all as equals and just make the
order alphabetical. I feel that if we don't do that then the order to
add any new parameters is just not going to be obvious and we'll end
up with things getting out of order again quite quickly.
I don't think that alphabetical order makes much sense. Surely some
parameters are more important than others. Surely there is some kind
of natural grouping that makes somewhat more sense than alphabetical
order.
Take the VACUUM command. Right now FULL, FREEZE, and VERBOSE all come
first. Those options are approximately the most important options --
especially VERBOSE. But your patch places VERBOSE dead last.
--
Peter Geoghegan
On Tue, 18 Apr 2023 at 18:53, Peter Geoghegan <pg@bowt.ie> wrote:
Take the VACUUM command. Right now FULL, FREEZE, and VERBOSE all come
first. Those options are approximately the most important options --
especially VERBOSE. But your patch places VERBOSE dead last.
hmm, how can we verify that the options are kept in order of
importance? What guidance can we provide to developers adding options
about where they should slot in the new option to the docs?
"Importance order" just seems horribly subjective to me. I'd be
interested to know if you could tell me if SKIP_LOCKED has more
importance than INDEX_CLEANUP, for example. If you can, it would seem
like trying to say apples are more important than oranges, or
vice-versa.
David
On Tue, Apr 18, 2023 at 4:18 PM David Rowley <dgrowleyml@gmail.com> wrote:
"Importance order" just seems horribly subjective to me.
Alphabetical order seems objectively bad. At least to me.
I'd be interested to know if you could tell me if SKIP_LOCKED has more
importance than INDEX_CLEANUP, for example. If you can, it would seem
like trying to say apples are more important than oranges, or
vice-versa.
I don't accept your premise that the only thing that matters (or the
most important thing) is adherence to some unambiguous and consistent
order.
--
Peter Geoghegan
On Tue, Apr 18, 2023 at 4:30 PM Peter Geoghegan <pg@bowt.ie> wrote:
I'd be interested to know if you could tell me if SKIP_LOCKED has more
importance than INDEX_CLEANUP, for example. If you can, it would seem
like trying to say apples are more important than oranges, or
vice-versa.I don't accept your premise that the only thing that matters (or the
most important thing) is adherence to some unambiguous and consistent
order.
In the case of VACUUM, the current devel order is:
FULL, FREEZE, VERBOSE, ANALYZE, DISABLE_PAGE_SKIPPING, SKIP_LOCKED,
INDEX_CLEANUP, PROCESS_MAIN, PROCESS_TOAST,
TRUNCATE, PARALLEL, SKIP_DATABASE_STATS, ONLY_DATABASE_STATS, BUFFER_USAGE_LIMIT
I think that this order is far superior to alphabetical order, which
is tantamount to random order. The first 4 items are indeed the really
important ones to users, in my experience.
I do have some minor quibbles beyond that, though. These are:
* PARALLEL deserves to be at the start, maybe 4th or 5th overall.
* DISABLE_PAGE_SKIPPING should be later, since it's really only a
testing option that probably never proved useful in production. In
particular, it has little business being before SKIP_LOCKED, which is
much more important and relevant.
* TRUNCATE and INDEX_CLEANUP are similar options, and ought to be side
by side. I would put PROCESS_MAIN and PROCESS_TOAST after those two
for the same reason.
While I'm certain that nobody will agree with me on every little
detail, I have to imagine that most would find my preferred ordering
quite understandable and unsurprising, at a high level -- this is not
a hopelessly idiosyncratic ranking, that could just as easily have
been generated by a PRNG. People may not easily agree that "apples are
more important than oranges, or vice-versa", but what does it matter?
I've really only put each option into buckets of items with *roughly*
the same importance. All of the details beyond that don't matter to
me, at all.
--
Peter Geoghegan
On 2023-Apr-18, Peter Geoghegan wrote:
While I'm certain that nobody will agree with me on every little
detail, I have to imagine that most would find my preferred ordering
quite understandable and unsurprising, at a high level -- this is not
a hopelessly idiosyncratic ranking, that could just as easily have
been generated by a PRNG. People may not easily agree that "apples are
more important than oranges, or vice-versa", but what does it matter?
I've really only put each option into buckets of items with *roughly*
the same importance. All of the details beyond that don't matter to
me, at all.
I agree with you that roughly bucketing items is a good approach.
Within each bucket we can then sort alphabetically.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"If you have nothing to say, maybe you need just the right tool to help you
not say it." (New York Times, about Microsoft PowerPoint)
On 19.04.23 01:30, Peter Geoghegan wrote:
I'd be interested to know if you could tell me if SKIP_LOCKED has more
importance than INDEX_CLEANUP, for example. If you can, it would seem
like trying to say apples are more important than oranges, or
vice-versa.I don't accept your premise that the only thing that matters (or the
most important thing) is adherence to some unambiguous and consistent
order.
My thinking is, if I want to look up FREEZE on the VACUUM man page, I
would welcome some easily identifiable way of locating it. At that
point, I don't know whether FREEZE is important or what kind of option
it is. For reference material, easy lookup should be a priority. For a
narrative chapter on VACUUM, you can introduce the options in any other
suitable order.
On 19 Apr 2023, at 10:52, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
For reference material, easy lookup should be a priority.
+1. Alphabetical ordering is consistent with POLA.
For a narrative chapter on VACUUM, you can introduce the options in any other
suitable order.
I would even phrase it such that in this case one *should* present the options
in the order most suitable to educate the reader.
--
Daniel Gustafsson
On Wed, Apr 19, 2023 at 3:04 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
While I'm certain that nobody will agree with me on every little
detail, I have to imagine that most would find my preferred ordering
quite understandable and unsurprising, at a high level -- this is not
a hopelessly idiosyncratic ranking, that could just as easily have
been generated by a PRNG. People may not easily agree that "apples are
more important than oranges, or vice-versa", but what does it matter?
I've really only put each option into buckets of items with *roughly*
the same importance. All of the details beyond that don't matter to
me, at all.I agree with you that roughly bucketing items is a good approach.
Within each bucket we can then sort alphabetically.
I think of these buckets as working at a logarithmic scale. The FULL,
FREEZE, VERBOSE, and ANALYZE options are multiple orders of magnitude
more important than most of the other options, and maybe one order of
magnitude more important than the PARALLEL, TRUNCATE, and
INDEX_CLEANUP options. With differences that big, you have a structure
that generalizes across all users quite well. This doesn't seem
particularly subjective.
--
Peter Geoghegan
On Wed, Apr 19, 2023 at 2:39 PM Peter Geoghegan <pg@bowt.ie> wrote:
On Wed, Apr 19, 2023 at 3:04 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
While I'm certain that nobody will agree with me on every little
detail, I have to imagine that most would find my preferred ordering
quite understandable and unsurprising, at a high level -- this is not
a hopelessly idiosyncratic ranking, that could just as easily have
been generated by a PRNG. People may not easily agree that "apples are
more important than oranges, or vice-versa", but what does it matter?
I've really only put each option into buckets of items with *roughly*
the same importance. All of the details beyond that don't matter to
me, at all.I agree with you that roughly bucketing items is a good approach.
Within each bucket we can then sort alphabetically.I think of these buckets as working at a logarithmic scale. The FULL,
FREEZE, VERBOSE, and ANALYZE options are multiple orders of magnitude
more important than most of the other options, and maybe one order of
magnitude more important than the PARALLEL, TRUNCATE, and
INDEX_CLEANUP options. With differences that big, you have a structure
that generalizes across all users quite well. This doesn't seem
particularly subjective.
I actually favor query/command order followed by alphabetical order for
most of the commands David included in his patch.
Of course the parameter argument types, like boolean and integer, should
be grouped together separate from the main parameters. David fit this
into the alphabetical paradigm by doing uppercase alphabetical followed
by lowercase alphabetical. There are some specific cases where I think
this isn't working quite as intended in his patch. I've called those out
in my command-by-command code review below.
I actually think we should consider having a single location which
defines all argument types for all SQL command parameters. Then we
wouldn't need to define them for each command. We could simply link to
the definition from the synopsis. That would clean up these lists quite
a bit. Perhaps there is some variation from command to command in the
actual definitions, though (I haven't checked). I would be happy to try
and write this patch if folks are interested in the idea.
As for alphabetical ordering vs importance ordering: while I do think
that if a user does not know what parameter they are looking for, an
alphabetical ordering is unhelpful, I also think the primary issue with
grouping them by "importance" is that it is difficult to maintain. Doing
so requires a discussion of importance for every new option added. That
seems like an annoying bit of overhead to give ourselves. Having a
subjective ordering seems worse than having a rule-based ordering. I
think command/query order followed by alphabetical order is a reasonable
rule-based ordering.
I went and took a look at some of the other SQL commands' documentation
and noticed that they are all pretty different (for good reason).
ALTER ROLE parameters [1]https://www.postgresql.org/docs/devel/sql-alterrole.html, for example, have a seemingly meaningless
order except for the fact that there are pairs of parameters. SUPERUSER
and NOSUPERUSER, INHERIT and NOINHERIT, etc. It might be a bit odd for
these to follow an absolute alphabetical ordering rule.
Many of the CREATE type SQL commands don't really have this problem
because there are only one or two options within each section of the
command and otherwise the order the parameters must appear in the query
dictates their order [2]https://www.postgresql.org/docs/devel/sql-createindex.html.
Others, like EXPLAIN [3]https://www.postgresql.org/docs/devel/sql-explain.html, for example, obviously benefit from an
alphabetical ordering of parameters -- which David has done in the
patch. I think most of the commands that David has patched here are
good candidates for alphabetical ordering.
Below I've reviewed each command in the patch specifically:
For ANALYZE, I think this looks good in its new alphabetized form.
Though table_name is alphabetically last for the lower case parameters
and thus doesn't pose an issue, if it were alphabetically earlier, I
would still favor putting it at the end to maintain a query order then
alphabetical order ordering.
For CLUSTER, I think alphabetical order isn't working well. I think we
should maintain query order followed by alphabetical order. Even though
table_name is optional, in the event that it is included, it would
precede index_name. So, perhaps the order should be VERBOSE, boolean,
table_name, index_name -- which pretty much cancels out alphabetizing.
For COPY, I think the new ordering of COPY has some issues. table_name
is no longer first even though for COPY FROM it is required before the
other parameters. I think this is confusing. Perhaps the options should
be after the other parameters are defined. I think having the options
alphabetized at the end of the others would be nice. So, my suggested
ordering is table_name, column_name, filename, PROGRAM, STDIN, STDOUT,
then the WITH options alphabetically, WHERE, and then the parameter
argument types alphabetically. The last one (where to put the parameter
argument types) I'm not so sure about.
EXPLAIN looks good to me as is.
For REINDEX, I would again suggest a query ordering followed by
alphabetical ordering. CONCURRENTLY, TABLESPACE, VERBOSE, DATABASE,
INDEX, SCHEMA, SYSTEM, TABLE, name, then all of the parameter argument
types alphabetically. (Also, you can put CONCURRENTLY in two different
places in the REINDEX command?)
For VACUUM, I'd perhaps suggest the options in alphabetical order
followed by table_name and then column_name and then putting the
parameter argument types at the end alphabetically.
Of course, we could decide VACUUM is special and group its options by
importance because this is especially helpful for users. I think that
there are other SQL commands whose options' importance is not
particularly worth debating.
I do think we should consider deprecating and dropping documentation of
the options that are supported without parentheses (relevant to commands
like ANALYZE, CLUSTER, VACUUM, and others). It is fine if we keep the
code to make ANALYZE VERBOSE work, but I don't think it is useful to
keep that documented. That is not a concern of this patch, however.
- Melanie
[1]: https://www.postgresql.org/docs/devel/sql-alterrole.html
[2]: https://www.postgresql.org/docs/devel/sql-createindex.html
[3]: https://www.postgresql.org/docs/devel/sql-explain.html
Melanie Plageman <melanieplageman@gmail.com> writes:
I do think we should consider deprecating and dropping documentation of
the options that are supported without parentheses (relevant to commands
like ANALYZE, CLUSTER, VACUUM, and others). It is fine if we keep the
code to make ANALYZE VERBOSE work, but I don't think it is useful to
keep that documented. That is not a concern of this patch, however.
I doubt it's a great idea to de-document syntax that's still allowed
and will still be widely used for years to come; that just promotes
confusion. However, we could do something similar to what we did
for COPY years ago, and move the un-parenthesized syntax to the
"Compatibility" section.
regards, tom lane
On Wed, Apr 19, 2023 at 2:33 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
As for alphabetical ordering vs importance ordering: while I do think
that if a user does not know what parameter they are looking for, an
alphabetical ordering is unhelpful, I also think the primary issue with
grouping them by "importance" is that it is difficult to maintain. Doing
so requires a discussion of importance for every new option added.
Not really. It's a matter that requires some amount of individual
judgement, in some cases. It may require effort, but I think that
that's likely to be worth it.
I won't be the one that quibbles over every little thing.
For VACUUM, I'd perhaps suggest the options in alphabetical order
followed by table_name and then column_name and then putting the
parameter argument types at the end alphabetically.Of course, we could decide VACUUM is special and group its options by
importance because this is especially helpful for users. I think that
there are other SQL commands whose options' importance is not
particularly worth debating.
That's very likely true -- it may be that most individual commands
really wouldn't be any worse off if they just used a standard
alphabetical order. I agree that consistency can be a virtue. But it's
not the highest virtue. There will be a number of important
exceptions, which will have outsized impact. VACUUM, ANALYZE, maybe
CREATE INDEX. So if there is going to be a new standard, there should
also be significant wiggle-room. Kind of like with the guidelines for
rmgr desc authors discussion.
--
Peter Geoghegan
On Wed, Apr 19, 2023 at 05:33:47PM -0400, Melanie Plageman wrote:
On Wed, Apr 19, 2023 at 2:39 PM Peter Geoghegan <pg@bowt.ie> wrote:
On Wed, Apr 19, 2023 at 3:04 AM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
While I'm certain that nobody will agree with me on every little
detail, I have to imagine that most would find my preferred ordering
quite understandable and unsurprising, at a high level -- this is not
a hopelessly idiosyncratic ranking, that could just as easily have
been generated by a PRNG. People may not easily agree that "apples are
more important than oranges, or vice-versa", but what does it matter?
I've really only put each option into buckets of items with *roughly*
the same importance. All of the details beyond that don't matter to
me, at all.I agree with you that roughly bucketing items is a good approach.
Within each bucket we can then sort alphabetically.I think of these buckets as working at a logarithmic scale. The FULL,
FREEZE, VERBOSE, and ANALYZE options are multiple orders of magnitude
more important than most of the other options, and maybe one order of
magnitude more important than the PARALLEL, TRUNCATE, and
INDEX_CLEANUP options. With differences that big, you have a structure
that generalizes across all users quite well. This doesn't seem
particularly subjective.I actually favor query/command order followed by alphabetical order for
most of the commands David included in his patch.Of course the parameter argument types, like boolean and integer, should
be grouped together separate from the main parameters. David fit this
into the alphabetical paradigm by doing uppercase alphabetical followed
by lowercase alphabetical. There are some specific cases where I think
this isn't working quite as intended in his patch. I've called those out
in my command-by-command code review below.I actually think we should consider having a single location which
defines all argument types for all SQL command parameters. Then we
wouldn't need to define them for each command. We could simply link to
the definition from the synopsis. That would clean up these lists quite
a bit. Perhaps there is some variation from command to command in the
actual definitions, though (I haven't checked). I would be happy to try
and write this patch if folks are interested in the idea.
I looked into this and it isn't a good idea. Out of the 183 SQL
commands, really only ANALYZE, VACUUM, COPY, CLUSTER, EXPLAIN, and
REINDEX have parameter argument types that are context-independent. And
out of those, boolean is the only type shared by all. VACUUM is the only
one with more than one parameter argument "type". So, it is basically
just a bad idea. Oh well...
- Melanie
On Wed, 19 Apr 2023 at 22:04, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
On 2023-Apr-18, Peter Geoghegan wrote:
While I'm certain that nobody will agree with me on every little
detail, I have to imagine that most would find my preferred ordering
quite understandable and unsurprising, at a high level -- this is not
a hopelessly idiosyncratic ranking, that could just as easily have
been generated by a PRNG. People may not easily agree that "apples are
more important than oranges, or vice-versa", but what does it matter?
I've really only put each option into buckets of items with *roughly*
the same importance. All of the details beyond that don't matter to
me, at all.I agree with you that roughly bucketing items is a good approach.
Within each bucket we can then sort alphabetically.
If these "buckets" were subcategories, then it might be ok. I see "man
grep" categorises the command line options and then sorts
alphabetically within the category. If we could come up with a way of
categorising the options then this would satisfy what Melanie
mentioned about having the argument types listed separately. However,
I'm really not sure which categories we could have. I really don't
have any concrete ideas here, but I'll attempt to at least start
something:
Behavioral:
ANALYZE
DISABLE_PAGE_SKIPPING
FREEZE
FULL
INDEX_CLEANUP
ONLY_DATABASE_STATS
PROCESS_MAIN
PROCESS_TOAST
SKIP_DATABASE_STATS
SKIP_LOCKED
TRUNCATE
Resource Usage:
BUFFER_USAGE_LIMIT
PARALLEL
Informational:
VERBOSE
Option Parameters:
boolean
column_name
integer
size
table_name
I'm just not sure if we have enough options to have a need to
categorise them. Also, going by the categories I attempted to come up
with, it just feels like "Behavioral" contains too many and
"Informational" is likely only ever going to contain VERBOSE. So I'm
not very happy with them.
I'm not really feeling excited enough about this to even come up with
a draft patch. I thought I'd send out this anyway to see if anyone can
think of anything better.
FWIW, vacuumdb --help has its options in alphabetical order using the
abbreviated form of the option.
David
On 20 Apr 2023, at 14:40, David Rowley <dgrowleyml@gmail.com> wrote:
I see "man grep" categorises the command line options and then sorts
alphabetically within the category.
On FreeBSD and macOS "man grep" lists all options alphabetically.
FWIW, vacuumdb --help has its options in alphabetical order using the
abbreviated form of the option.
It does (as most of our binaries do) group "Connection options" separately
though, and in initdb --help and pg_dump --help we have other groupings as
well.
--
Daniel Gustafsson