diff options
Diffstat (limited to 'doc/src/sgml/ref')
26 files changed, 516 insertions, 555 deletions
diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml index 9da8920e12e..1fc051e11a3 100644 --- a/doc/src/sgml/ref/alter_database.sgml +++ b/doc/src/sgml/ref/alter_database.sgml @@ -83,7 +83,7 @@ ALTER DATABASE <replaceable class="parameter">name</replaceable> RESET ALL must be empty for this database, and no one can be connected to the database. Tables and indexes in non-default tablespaces are unaffected. The method used to copy files to the new tablespace - is affected by the <xref linkend="guc_file_copy_method"/> setting. + is affected by the <xref linkend="guc-file-copy-method"/> setting. </para> <para> diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index fdc648d007f..d48cdc76bd3 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -235,8 +235,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < <link linkend="sql-createsubscription-params-with-password-required"><literal>password_required</literal></link>, <link linkend="sql-createsubscription-params-with-run-as-owner"><literal>run_as_owner</literal></link>, <link linkend="sql-createsubscription-params-with-origin"><literal>origin</literal></link>, - <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>, and - <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>. + <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>, + <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>, and + <link linkend="sql-createsubscription-params-with-retain-dead-tuples"><literal>retain_dead_tuples</literal></link>. Only a superuser can set <literal>password_required = false</literal>. </para> @@ -261,8 +262,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < </para> <para> - The <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link> - and <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link> + The <link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>, + <link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>, and + <link linkend="sql-createsubscription-params-with-retain-dead-tuples"><literal>retain_dead_tuples</literal></link> parameters can only be altered when the subscription is disabled. </para> @@ -285,6 +287,14 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO < option is changed from <literal>true</literal> to <literal>false</literal>, the publisher will replicate the transactions again when they are committed. </para> + + <para> + If the <link linkend="sql-createsubscription-params-with-retain-dead-tuples"><literal>retain_dead_tuples</literal></link> + option is altered to <literal>false</literal> and no other subscription + has this option enabled, the replication slot named + <quote><literal>pg_conflict_detection</literal></quote>, created to retain + dead tuples for conflict detection, will be dropped. + </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index d63f3a621ac..541e093a519 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -240,9 +240,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM provided none of the records in the table contain a <literal>NULL</literal> value for the column. Ordinarily this is checked during the <literal>ALTER TABLE</literal> by scanning the - entire table; however, if a valid <literal>CHECK</literal> constraint is - found which proves no <literal>NULL</literal> can exist, then the - table scan is skipped. + entire table, unless <literal>NOT VALID</literal> is specified; + however, if a valid <literal>CHECK</literal> constraint exists + (and is not dropped in the same command) which proves no + <literal>NULL</literal> can exist, then the table scan is skipped. If a column has an invalid not-null constraint, <literal>SET NOT NULL</literal> validates it. </para> @@ -460,8 +461,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <para> This form adds a new constraint to a table using the same constraint syntax as <link linkend="sql-createtable"><command>CREATE TABLE</command></link>, plus the option <literal>NOT - VALID</literal>, which is currently only allowed for foreign key, - <literal>CHECK</literal> constraints and not-null constraints. + VALID</literal>, which is currently only allowed for foreign-key, + <literal>CHECK</literal>, and not-null constraints. </para> <para> @@ -469,7 +470,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM existing rows in the table satisfy the new constraint. But if the <literal>NOT VALID</literal> option is used, this potentially-lengthy scan is skipped. The constraint will still be - enforced against subsequent inserts or updates (that is, they'll fail + applied against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys, or they'll fail unless the new row matches the specified check condition). But the @@ -591,7 +592,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM This form validates a foreign key, check, or not-null constraint that was previously created as <literal>NOT VALID</literal>, by scanning the table to ensure there are no rows for which the constraint is not - satisfied. If the constraint is not enforced, an error is thrown. + satisfied. If the constraint was set to <literal>NOT ENFORCED</literal>, an error is thrown. Nothing happens if the constraint is already marked valid. (See <xref linkend="sql-altertable-notes"/> below for an explanation of the usefulness of this command.) @@ -852,7 +853,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <para> <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for - fillfactor, toast and autovacuum storage parameters, as well as the + fillfactor, TOAST and autovacuum storage parameters, as well as the planner parameter <varname>parallel_workers</varname>. </para> </listitem> @@ -1466,11 +1467,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> - Adding an enforced <literal>CHECK</literal> or <literal>NOT NULL</literal> + Adding a <literal>CHECK</literal> or <literal>NOT NULL</literal> constraint requires scanning the table to verify that existing rows meet the constraint, but does not require a table rewrite. If a <literal>CHECK</literal> - constraint is added as <literal>NOT ENFORCED</literal>, the validation will - not be performed. + constraint is added as <literal>NOT ENFORCED</literal>, no verification will + be performed. </para> <para> @@ -1485,7 +1486,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> - Scanning a large table to verify a new foreign key or check constraint + Scanning a large table to verify new foreign-key, check, or not-null constraints can take a long time, and other updates to the table are locked out until the <command>ALTER TABLE ADD CONSTRAINT</command> command is committed. The main purpose of the <literal>NOT VALID</literal> diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml index db011a47d04..cd981cf2cab 100644 --- a/doc/src/sgml/ref/checkpoint.sgml +++ b/doc/src/sgml/ref/checkpoint.sgml @@ -21,7 +21,12 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -CHECKPOINT +CHECKPOINT [ ( option [, ...] ) ] + +<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase> + + FLUSH_UNLOGGED [ <replaceable class="parameter">boolean</replaceable> ] + MODE { FAST | SPREAD } </synopsis> </refsynopsisdiv> @@ -37,15 +42,25 @@ CHECKPOINT </para> <para> - The <command>CHECKPOINT</command> command forces an immediate + By default, the <command>CHECKPOINT</command> command forces a fast checkpoint when the command is issued, without waiting for a regular checkpoint scheduled by the system (controlled by the settings in <xref linkend="runtime-config-wal-checkpoints"/>). + To request the checkpoint be spread over a longer interval, set the + <literal>MODE</literal> option to <literal>SPREAD</literal>. <command>CHECKPOINT</command> is not intended for use during normal operation. </para> <para> + The server may consolidate concurrently requested checkpoints. Such + consolidated requests will contain a combined set of options. For example, + if one session requests a fast checkpoint and another requests a spread + checkpoint, the server may combine those requests and perform one fast + checkpoint. + </para> + + <para> If executed during recovery, the <command>CHECKPOINT</command> command will force a restartpoint (see <xref linkend="wal-configuration"/>) rather than writing a new checkpoint. @@ -59,6 +74,55 @@ CHECKPOINT </refsect1> <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><literal>FLUSH_UNLOGGED</literal></term> + <listitem> + <para> + Normally, <command>CHECKPOINT</command> does not flush dirty buffers of + unlogged relations. This option, which is disabled by default, enables + flushing unlogged relations to disk. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>MODE</literal></term> + <listitem> + <para> + When set to <literal>FAST</literal>, which is the default, the requested + checkpoint will be completed as fast as possible, which may result in a + significantly higher rate of I/O during the checkpoint. + </para> + <para> + <literal>MODE</literal> can also be set to <literal>SPREAD</literal> to + request the checkpoint be spread over a longer interval (controlled via + the settings in <xref linkend="runtime-config-wal-checkpoints"/>), like a + regular checkpoint scheduled by the system. This can reduce the rate of + I/O during the checkpoint. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">boolean</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. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> <title>Compatibility</title> <para> diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 8433344e5b6..c2d1fbc1fbe 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -37,7 +37,7 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable DELIMITER '<replaceable class="parameter">delimiter_character</replaceable>' NULL '<replaceable class="parameter">null_string</replaceable>' DEFAULT '<replaceable class="parameter">default_string</replaceable>' - HEADER [ <replaceable class="parameter">boolean</replaceable> | MATCH ] + HEADER [ <replaceable class="parameter">boolean</replaceable> | <replaceable class="parameter">integer</replaceable> | MATCH ] QUOTE '<replaceable class="parameter">quote_character</replaceable>' ESCAPE '<replaceable class="parameter">escape_character</replaceable>' FORCE_QUOTE { ( <replaceable class="parameter">column_name</replaceable> [, ...] ) | * } @@ -213,6 +213,15 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable </varlistentry> <varlistentry> + <term><replaceable class="parameter">integer</replaceable></term> + <listitem> + <para> + Specifies a non-negative integer value passed to the selected option. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>FORMAT</literal></term> <listitem> <para> @@ -303,16 +312,25 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable <term><literal>HEADER</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. + On output, if this option is set to <literal>true</literal> + (or an equivalent Boolean value), the first line of the output will + contain the column names from the table. + Integer values <literal>0</literal> and <literal>1</literal> are + accepted as Boolean values, but other integers are not allowed for + <command>COPY TO</command> commands. + </para> + <para> + On input, if this option is set to <literal>true</literal> + (or an equivalent Boolean value), the first line of the input is + discarded. If set to a non-negative integer, that number of + lines are discarded. If set to <literal>MATCH</literal>, the first line + is discarded, and it must contain column names that exactly match the + table's columns, in both number and order; otherwise, an error is raised. + The <literal>MATCH</literal> value is only valid for + <command>COPY FROM</command> commands. + </para> + <para> 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> diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 640c0425fae..3544b15efda 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -140,7 +140,7 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> after the creation of the new database. In some situations, this may have a noticeable negative impact on overall system performance. The <literal>FILE_COPY</literal> strategy is affected by the <xref - linkend="guc_file_copy_method"/> setting. + linkend="guc-file-copy-method"/> setting. </para> </listitem> </varlistentry> @@ -150,12 +150,12 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> <para> Sets the default collation order and character classification in the new database. Collation affects the sort order applied to strings, - e.g., in queries with <literal>ORDER BY</literal>, as well as the order used in indexes - on text columns. Character classification affects the categorization - of characters, e.g., lower, upper, and digit. Also sets the - associated aspects of the operating system environment, - <literal>LC_COLLATE</literal> and <literal>LC_CTYPE</literal>. The - default is the same setting as the template database. See <xref + e.g., in queries with <literal>ORDER BY</literal>, as well as the + order used in indexes on text columns. Character classification + affects the categorization of characters, e.g., lower, upper, and + digit. Also sets the <literal>LC_CTYPE</literal> aspect of the + operating system environment. The default is the same setting as the + template database. See <xref linkend="collation-managing-create-libc"/> and <xref linkend="collation-managing-create-icu"/> for details. </para> @@ -189,17 +189,16 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> <term><replaceable class="parameter">lc_collate</replaceable></term> <listitem> <para> - Sets <literal>LC_COLLATE</literal> in the database server's operating - system environment. The default is the setting of <xref - linkend="create-database-locale"/> if specified, otherwise the same - setting as the template database. See below for additional - restrictions. + If <xref linkend="create-database-locale-provider"/> is + <literal>libc</literal>, sets the default collation order to use in + the new database, overriding the setting <xref + linkend="create-database-locale"/>. Otherwise, this setting is + ignored. </para> <para> - If <xref linkend="create-database-locale-provider"/> is - <literal>libc</literal>, also sets the default collation order to use - in the new database, overriding the setting <xref - linkend="create-database-locale"/>. + The default is the setting of <xref linkend="create-database-locale"/> + if specified, otherwise the same setting as the template database. + See below for additional restrictions. </para> </listitem> </varlistentry> @@ -208,16 +207,18 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable> <listitem> <para> Sets <literal>LC_CTYPE</literal> in the database server's operating - system environment. The default is the setting of <xref - linkend="create-database-locale"/> if specified, otherwise the same - setting as the template database. See below for additional - restrictions. + system environment. </para> <para> If <xref linkend="create-database-locale-provider"/> is - <literal>libc</literal>, also sets the default character - classification to use in the new database, overriding the setting - <xref linkend="create-database-locale"/>. + <literal>libc</literal>, sets the default character classification to + use in the new database, overriding the setting <xref + linkend="create-database-locale"/>. + </para> + <para> + The default is the setting of <xref linkend="create-database-locale"/> + if specified, otherwise the same setting as the template database. + See below for additional restrictions. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml index d08834ac9d2..009fa46532b 100644 --- a/doc/src/sgml/ref/create_foreign_table.sgml +++ b/doc/src/sgml/ref/create_foreign_table.sgml @@ -232,7 +232,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <term><literal>INCLUDING COMMENTS</literal></term> <listitem> <para> - Comments for the copied columns, constraints, and indexes will be + Comments for the copied columns and constraints will be copied. The default behavior is to exclude comments, resulting in the copied columns and constraints in the new table having no comments. diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 147a8f7587c..b9c679c41e8 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -814,7 +814,7 @@ Indexes: leveraging multiple CPUs in order to process the table rows faster. This feature is known as <firstterm>parallel index build</firstterm>. For index methods that support building indexes - in parallel (currently, B-tree and BRIN), + in parallel (currently, B-tree, GIN, and BRIN), <varname>maintenance_work_mem</varname> specifies the maximum amount of memory that can be used by each index build operation as a whole, regardless of how many worker processes were started. diff --git a/doc/src/sgml/ref/create_operator.sgml b/doc/src/sgml/ref/create_operator.sgml index 3553d364541..d2ffb1b2a50 100644 --- a/doc/src/sgml/ref/create_operator.sgml +++ b/doc/src/sgml/ref/create_operator.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation <synopsis> CREATE OPERATOR <replaceable>name</replaceable> ( {FUNCTION|PROCEDURE} = <replaceable class="parameter">function_name</replaceable> - [, LEFTARG = <replaceable class="parameter">left_type</replaceable> ] [, RIGHTARG = <replaceable class="parameter">right_type</replaceable> ] + [, LEFTARG = <replaceable class="parameter">left_type</replaceable> ] , RIGHTARG = <replaceable class="parameter">right_type</replaceable> [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ] [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ] [, HASHES ] [, MERGES ] @@ -88,8 +88,8 @@ CREATE OPERATOR <replaceable>name</replaceable> ( <para> For binary operators, both <literal>LEFTARG</literal> and - <literal>RIGHTARG</literal> must be defined. For prefix operators only - <literal>RIGHTARG</literal> should be defined. + <literal>RIGHTARG</literal> must be defined. For prefix operators, only + <literal>RIGHTARG</literal> must be defined. The <replaceable class="parameter">function_name</replaceable> function must have been previously defined using <command>CREATE FUNCTION</command> and must be defined to accept the correct number diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 57dec28a5df..247c5bd2604 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -169,7 +169,9 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl <listitem> <para> Name of the publisher's replication slot to use. The default is - to use the name of the subscription for the slot name. + to use the name of the subscription for the slot name. The name cannot + be <literal>pg_conflict_detection</literal> as it is reserved for the + conflict detection. </para> <para> @@ -435,6 +437,90 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl </para> </listitem> </varlistentry> + + <varlistentry id="sql-createsubscription-params-with-retain-dead-tuples"> + <term><literal>retain_dead_tuples</literal> (<type>boolean</type>)</term> + <listitem> + <para> + Specifies whether the information (e.g., dead tuples, commit + timestamps, and origins) required for conflict detection on the + subscriber is retained. The default is <literal>false</literal>. + If set to <literal>true</literal>, the detection of + <xref linkend="conflict-update-deleted"/> is enabled, and a physical + replication slot named <quote><literal>pg_conflict_detection</literal></quote> + created on the subscriber to prevent the information for detecting + conflicts from being removed. + </para> + + <para> + Note that the information useful for conflict detection is retained + only after the creation of the slot. You can verify the existence of + this slot by querying <link linkend="view-pg-replication-slots">pg_replication_slots</link>. + And even if multiple subscriptions on one node enable this option, + only one replication slot will be created. Also, + <varname>wal_level</varname> must be set to <literal>replica</literal> + or higher to allow the replication slot to be used. + </para> + + <caution> + <para> + Note that the information for conflict detection cannot be purged if + the subscription is disabled; thus, the information will accumulate + until the subscription is enabled. To prevent excessive accumulation, + it is recommended to disable <literal>retain_dead_tuples</literal> + if the subscription will be inactive for an extended period. + </para> + + <para> + Additionally when enabling <literal>retain_dead_tuples</literal> for + conflict detection in logical replication, it is important to design the + replication topology to balance data retention requirements with + overall system performance. This option provides minimal performance + overhead when applied appropriately. The following scenarios illustrate + effective usage patterns when enabling this option. + </para> + + <para> + a. Large Tables with Bidirectional Writes: + For large tables subject to concurrent writes on both publisher and + subscriber nodes, publishers can define row filters when creating + publications to segment data. This allows multiple subscriptions + to replicate exclusive subsets of the table in parallel, optimizing + the throughput. + </para> + + <para> + b. Write-Enabled Subscribers: + If a subscriber node is expected to perform write operations, replication + can be structured using multiple publications and subscriptions. By + distributing tables across these publications, the workload is spread among + several apply workers, improving concurrency and reducing contention. + </para> + + <para> + c. Read-Only Subscribers: + In configurations involving single or multiple publisher nodes + performing concurrent write operations, read-only subscriber nodes may + replicate changes without seeing a performance impact if it does index + scan. However, if the subscriber is impacted due to replication lag or + scan performance (say due to sequential scans), it needs to follow one + of the two previous strategies to distribute the workload on the + subscriber. + </para> + </caution> + + <para> + This option cannot be enabled if the publisher is a physical standby. + </para> + + <para> + Enabling this option ensures retention of information useful for + conflict detection solely for changes occurring locally on the + publisher. For the changes originating from different origins, + reliable conflict detection cannot be guaranteed. + </para> + </listitem> + </varlistentry> </variablelist></para> </listitem> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 4a41b2f5530..dc000e913c1 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -448,11 +448,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> - Partitioned tables do not support <literal>EXCLUDE</literal> constraints; - however, you can define these constraints on individual partitions. - </para> - - <para> See <xref linkend="ddl-partitioning"/> for more discussion on table partitioning. </para> @@ -929,6 +924,15 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM not other generated columns. Any functions and operators used must be immutable. References to other tables are not allowed. </para> + + <para> + A virtual generated column cannot have a user-defined type, and the + generation expression of a virtual generated column must not reference + user-defined functions or types, that is, it can only use built-in + functions or types. This applies also indirectly, such as for functions + or types that underlie operators or casts. (This restriction does not + exist for stored generated columns.) + </para> </listitem> </varlistentry> @@ -1162,6 +1166,18 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM exclusion constraint on a subset of the table; internally this creates a partial index. Note that parentheses are required around the predicate. </para> + + <para> + When establishing an exclusion constraint for a multi-level partition + hierarchy, all the columns in the partition key of the target + partitioned table, as well as those of all its descendant partitioned + tables, must be included in the constraint definition. Additionally, + those columns must be compared using the equality operator. These + restrictions ensure that potentially-conflicting rows will exist in the + same partition. The constraint may also refer to other columns which + are not a part of any partition key, which can be compared using any + appropriate operator. + </para> </listitem> </varlistentry> @@ -1687,7 +1703,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <varlistentry id="reloption-vacuum-truncate" xreflabel="vacuum_truncate"> <term><literal>vacuum_truncate</literal>, <literal>toast.vacuum_truncate</literal> (<type>boolean</type>) <indexterm> - <primary><varname>vacuum_truncate</varname> storage parameter</primary> + <primary><varname>vacuum_truncate</varname></primary> + <secondary>storage parameter</secondary> </indexterm> </term> <listitem> diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 982ab6f3ee4..0d8d463479b 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -29,7 +29,7 @@ PostgreSQL documentation CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name</replaceable> { BEFORE | AFTER | INSTEAD OF } { <replaceable class="parameter">event</replaceable> [ OR ... ] } ON <replaceable class="parameter">table_name</replaceable> [ FROM <replaceable class="parameter">referenced_table_name</replaceable> ] - [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] + [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ ENFORCED ] [ REFERENCING { { OLD | NEW } TABLE [ AS ] <replaceable class="parameter">transition_relation_name</replaceable> } [ ... ] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( <replaceable class="parameter">condition</replaceable> ) ] @@ -197,9 +197,11 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for - an <literal>AFTER</literal> trigger that is not a constraint trigger; also, if - the trigger is an <literal>UPDATE</literal> trigger, it must not specify - a <replaceable class="parameter">column_name</replaceable> list. + an <literal>AFTER</literal> trigger on a plain table (not a foreign table). + The trigger should not be a constraint trigger. Also, if the trigger is + an <literal>UPDATE</literal> trigger, it must not specify + a <replaceable class="parameter">column_name</replaceable> list when using + this option. <literal>OLD TABLE</literal> may only be specified once, and only for a trigger that can fire on <literal>UPDATE</literal> or <literal>DELETE</literal>; it creates a transition relation containing the <firstterm>before-images</firstterm> of all rows @@ -322,6 +324,15 @@ UPDATE OF <replaceable>column_name1</replaceable> [, <replaceable>column_name2</ </varlistentry> <varlistentry> + <term><literal>ENFORCED</literal></term> + <listitem> + <para> + This is a noise word. Constraint triggers are always enforced. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>REFERENCING</literal></term> <listitem> <para> diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml index 5c4e0465ed9..2ccbe13f390 100644 --- a/doc/src/sgml/ref/createdb.sgml +++ b/doc/src/sgml/ref/createdb.sgml @@ -136,7 +136,8 @@ PostgreSQL documentation <term><option>--lc-collate=<replaceable class="parameter">locale</replaceable></option></term> <listitem> <para> - Specifies the LC_COLLATE setting to be used in this database. + Specifies the LC_COLLATE setting to be used in this database (ignored + unless the locale provider is <literal>libc</literal>). </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml index ecbcd8345d8..c2e181066a4 100644 --- a/doc/src/sgml/ref/merge.sgml +++ b/doc/src/sgml/ref/merge.sgml @@ -23,37 +23,37 @@ PostgreSQL documentation <synopsis> [ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ] MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ] -USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable> -<replaceable class="parameter">when_clause</replaceable> [...] -[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ] - { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] + USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable> + <replaceable class="parameter">when_clause</replaceable> [...] + [ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ] + { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] <phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase> -{ [ ONLY ] <replaceable class="parameter">source_table_name</replaceable> [ * ] | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ] + { [ ONLY ] <replaceable class="parameter">source_table_name</replaceable> [ * ] | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ] <phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase> -{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | - WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | - WHEN NOT MATCHED [ BY TARGET ] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } } + { WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | + WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } | + WHEN NOT MATCHED [ BY TARGET ] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } } <phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase> -INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )] -[ OVERRIDING { SYSTEM | USER } VALUE ] -{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES } + INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )] + [ OVERRIDING { SYSTEM | USER } VALUE ] + { VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES } <phrase>and <replaceable class="parameter">merge_update</replaceable> is:</phrase> -UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | - ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | - ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> ) - } [, ...] + UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | + ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | + ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> ) + } [, ...] <phrase>and <replaceable class="parameter">merge_delete</replaceable> is:</phrase> -DELETE + DELETE </synopsis> </refsynopsisdiv> @@ -106,10 +106,11 @@ DELETE to compute and return value(s) based on each row inserted, updated, or deleted. Any expression using the source or target table's columns, or the <link linkend="merge-action"><function>merge_action()</function></link> - function can be computed. When an <command>INSERT</command> or + function can be computed. By default, when an <command>INSERT</command> or <command>UPDATE</command> action is performed, the new values of the target - table's columns are used. When a <command>DELETE</command> is performed, - the old values of the target table's columns are used. The syntax of the + table's columns are used, and when a <command>DELETE</command> is performed, + the old values of the target table's columns are used, but it is also + possible to explicitly request old and new values. The syntax of the <literal>RETURNING</literal> list is identical to that of the output list of <command>SELECT</command>. </para> diff --git a/doc/src/sgml/ref/pg_amcheck.sgml b/doc/src/sgml/ref/pg_amcheck.sgml index 6bfe28799c4..ef2bdfd19ae 100644 --- a/doc/src/sgml/ref/pg_amcheck.sgml +++ b/doc/src/sgml/ref/pg_amcheck.sgml @@ -41,7 +41,7 @@ PostgreSQL documentation </para> <para> - Only ordinary and toast table relations, materialized views, sequences, and + Only ordinary and TOAST table relations, materialized views, sequences, and btree indexes are currently supported. Other relation types are silently skipped. </para> @@ -276,7 +276,7 @@ PostgreSQL documentation <term><option>--no-dependent-toast</option></term> <listitem> <para> - By default, if a table is checked, its toast table, if any, will also + By default, if a table is checked, its TOAST table, if any, will also be checked, even if it is not explicitly selected by an option such as <literal>--table</literal> or <literal>--relation</literal>. This option suppresses that behavior. @@ -306,9 +306,9 @@ PostgreSQL documentation <term><option>--exclude-toast-pointers</option></term> <listitem> <para> - By default, whenever a toast pointer is encountered in a table, + By default, whenever a TOAST pointer is encountered in a table, a lookup is performed to ensure that it references apparently-valid - entries in the toast table. These checks can be quite slow, and this + entries in the TOAST table. These checks can be quite slow, and this option can be used to skip them. </para> </listitem> @@ -368,9 +368,9 @@ PostgreSQL documentation End checking at the specified block number. An error will occur if the table relation being checked has fewer than this number of blocks. This option does not apply to indexes, and is probably only useful when - checking a single table relation. If both a regular table and a toast + checking a single table relation. If both a regular table and a TOAST table are checked, this option will apply to both, but higher-numbered - toast blocks may still be accessed while validating toast pointers, + TOAST blocks may still be accessed while validating TOAST pointers, unless that is suppressed using <option>--exclude-toast-pointers</option>. </para> diff --git a/doc/src/sgml/ref/pg_basebackup.sgml b/doc/src/sgml/ref/pg_basebackup.sgml index 9659f76042c..fecee08b0a5 100644 --- a/doc/src/sgml/ref/pg_basebackup.sgml +++ b/doc/src/sgml/ref/pg_basebackup.sgml @@ -500,8 +500,9 @@ PostgreSQL documentation <term><option>--checkpoint={fast|spread}</option></term> <listitem> <para> - Sets checkpoint mode to fast (immediate) or spread (the default) + Sets checkpoint mode to fast or spread (see <xref linkend="backup-lowlevel-base-backup"/>). + The default is spread. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml index 4b1d08d5f16..bb9cc72576c 100644 --- a/doc/src/sgml/ref/pg_createsubscriber.sgml +++ b/doc/src/sgml/ref/pg_createsubscriber.sgml @@ -170,36 +170,6 @@ PostgreSQL documentation </varlistentry> <varlistentry> - <term><option>-R <replaceable class="parameter">objtype</replaceable></option></term> - <term><option>--remove=<replaceable class="parameter">objtype</replaceable></option></term> - <listitem> - <para> - Remove all objects of the specified type from specified databases on the - target server. - </para> - <para> - <itemizedlist> - <listitem> - <para> - <literal>publications</literal>: - The <literal>FOR ALL TABLES</literal> publications established for this - subscriber are always removed; specifying this object type causes all - other publications replicated from the source server to be dropped as - well. - </para> - </listitem> - </itemizedlist> - </para> - <para> - The objects selected to be dropped are individually logged, including during - a <option>--dry-run</option>. There is no opportunity to affect or stop the - dropping of the selected objects, so consider taking a backup of them - using <application>pg_dump</application>. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><option>-s <replaceable class="parameter">dir</replaceable></option></term> <term><option>--socketdir=<replaceable class="parameter">dir</replaceable></option></term> <listitem> @@ -260,6 +230,35 @@ PostgreSQL documentation </varlistentry> <varlistentry> + <term><option>--clean=<replaceable class="parameter">objtype</replaceable></option></term> + <listitem> + <para> + Drop all objects of the specified type from specified databases on the + target server. + </para> + <para> + <itemizedlist> + <listitem> + <para> + <literal>publications</literal>: + The <literal>FOR ALL TABLES</literal> publications established for this + subscriber are always dropped; specifying this object type causes all + other publications replicated from the source server to be dropped as + well. + </para> + </listitem> + </itemizedlist> + </para> + <para> + The objects selected to be dropped are individually logged, including during + a <option>--dry-run</option>. There is no opportunity to affect or stop the + dropping of the selected objects, so consider taking a backup of them + using <application>pg_dump</application>. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><option>--config-file=<replaceable class="parameter">filename</replaceable></option></term> <listitem> <para> diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index d7595a7e546..0bc7609bdf8 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -18,7 +18,7 @@ PostgreSQL documentation <refname>pg_dump</refname> <refpurpose> - extract a <productname>PostgreSQL</productname> database into a script file or other archive file + export a <productname>PostgreSQL</productname> database as an SQL script or to other formats </refpurpose> </refnamediv> @@ -1277,11 +1277,11 @@ PostgreSQL documentation </para> <para> The data section contains actual table data, large-object - contents, statistics for tables and materialized views and - sequence values. + contents, sequence values, and statistics for tables, + materialized views, and foreign tables. Post-data items include definitions of indexes, triggers, rules, statistics for indexes, and constraints other than validated check - constraints. + and not-null constraints. Pre-data items include all other data definition items. </para> </listitem> @@ -1355,11 +1355,21 @@ PostgreSQL documentation </varlistentry> <varlistentry> + <term><option>--statistics</option></term> + <listitem> + <para> + Dump statistics. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><option>--statistics-only</option></term> <listitem> <para> Dump only the statistics, not the schema (data definitions) or data. - Statistics for tables, materialized views, and indexes are dumped. + Statistics for tables, materialized views, foreign tables, + and indexes are dumped. </para> </listitem> </varlistentry> @@ -1440,33 +1450,6 @@ PostgreSQL documentation </varlistentry> <varlistentry> - <term><option>--with-data</option></term> - <listitem> - <para> - Dump data. This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><option>--with-schema</option></term> - <listitem> - <para> - Dump schema (data definitions). This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><option>--with-statistics</option></term> - <listitem> - <para> - Dump statistics. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><option>-?</option></term> <term><option>--help</option></term> <listitem> @@ -1681,7 +1664,7 @@ CREATE DATABASE foo WITH TEMPLATE template0; </para> <para> - If <option>--with-statistics</option> is specified, + If <option>--statistics</option> is specified, <command>pg_dump</command> will include most optimizer statistics in the resulting dump file. However, some statistics may not be included, such as those created explicitly with <xref linkend="sql-createstatistics"/> or diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index 723a466cfaa..364442f00f2 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -16,7 +16,7 @@ PostgreSQL documentation <refnamediv> <refname>pg_dumpall</refname> - <refpurpose>extract a <productname>PostgreSQL</productname> database cluster using a specified dump format</refpurpose> + <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose> </refnamediv> <refsynopsisdiv> @@ -33,7 +33,7 @@ PostgreSQL documentation <para> <application>pg_dumpall</application> is a utility for writing out (<quote>dumping</quote>) all <productname>PostgreSQL</productname> databases - of a cluster into an archive. The archive contains + of a cluster into one script file. The script file contains <acronym>SQL</acronym> commands that can be used as input to <xref linkend="app-psql"/> to restore the databases. It does this by calling <xref linkend="app-pgdump"/> for each database in the cluster. @@ -52,17 +52,12 @@ PostgreSQL documentation </para> <para> - Plain text SQL scripts will be written to the standard output. Use the + The SQL script will be written to the standard output. Use the <option>-f</option>/<option>--file</option> option or shell operators to redirect it into a file. </para> <para> - Archives in other formats will be placed in a directory named using the - <option>-f</option>/<option>--file</option>, which is required in this case. - </para> - - <para> <application>pg_dumpall</application> needs to connect several times to the <productname>PostgreSQL</productname> server (once per database). If you use password authentication it will ask for @@ -126,86 +121,11 @@ PostgreSQL documentation <para> Send output to the specified file. If this is omitted, the standard output is used. - Note: This option can only be omitted when <option>--format</option> is plain </para> </listitem> </varlistentry> <varlistentry> - <term><option>-F <replaceable class="parameter">format</replaceable></option></term> - <term><option>--format=<replaceable class="parameter">format</replaceable></option></term> - <listitem> - <para> - Specify the format of dump files. In plain format, all the dump data is - sent in a single text stream. This is the default. - - In all other modes, <application>pg_dumpall</application> first creates two files: - <filename>global.dat</filename> and <filename>map.dat</filename>, in the directory - specified by <option>--file</option>. - The first file contains global data, such as roles and tablespaces. The second - contains a mapping between database oids and names. These files are used by - <application>pg_restore</application>. Data for individual databases is placed in - <filename>databases</filename> subdirectory, named using the database's <type>oid</type>. - - <variablelist> - <varlistentry> - <term><literal>d</literal></term> - <term><literal>directory</literal></term> - <listitem> - <para> - Output directory-format archives for each database, - suitable for input into pg_restore. The directory - will have database <type>oid</type> as its name. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>p</literal></term> - <term><literal>plain</literal></term> - <listitem> - <para> - Output a plain-text SQL script file (the default). - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>c</literal></term> - <term><literal>custom</literal></term> - <listitem> - <para> - Output a custom-format archive for each database, - suitable for input into pg_restore. The archive - will be named <filename>dboid.dmp</filename> where <type>dboid</type> is the - <type>oid</type> of the database. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><literal>t</literal></term> - <term><literal>tar</literal></term> - <listitem> - <para> - Output a tar-format archive for each database, - suitable for input into pg_restore. The archive - will be named <filename>dboid.tar</filename> where <type>dboid</type> is the - <type>oid</type> of the database. - </para> - </listitem> - </varlistentry> - - </variablelist> - - Note: see <xref linkend="app-pgdump"/> for details - of how the various non plain text archives work. - - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><option>-g</option></term> <term><option>--globals-only</option></term> <listitem> @@ -686,11 +606,21 @@ exclude database <replaceable class="parameter">PATTERN</replaceable> </varlistentry> <varlistentry> + <term><option>--statistics</option></term> + <listitem> + <para> + Dump statistics. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><option>--statistics-only</option></term> <listitem> <para> Dump only the statistics, not the schema (data definitions) or data. - Statistics for tables, materialized views, and indexes are dumped. + Statistics for tables, materialized views, foreign tables, + and indexes are dumped. </para> </listitem> </varlistentry> @@ -720,33 +650,6 @@ exclude database <replaceable class="parameter">PATTERN</replaceable> </varlistentry> <varlistentry> - <term><option>--with-data</option></term> - <listitem> - <para> - Dump data. This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><option>--with-schema</option></term> - <listitem> - <para> - Dump schema (data definitions). This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><option>--with-statistics</option></term> - <listitem> - <para> - Dump statistics. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><option>-?</option></term> <term><option>--help</option></term> <listitem> @@ -957,7 +860,7 @@ exclude database <replaceable class="parameter">PATTERN</replaceable> </para> <para> - If <option>--with-statistics</option> is specified, + If <option>--statistics</option> is specified, <command>pg_dumpall</command> will include most optimizer statistics in the resulting dump file. However, some statistics may not be included, such as those created explicitly with <xref linkend="sql-createstatistics"/> or diff --git a/doc/src/sgml/ref/pg_recvlogical.sgml b/doc/src/sgml/ref/pg_recvlogical.sgml index 63a45c7018a..263ebdeeab4 100644 --- a/doc/src/sgml/ref/pg_recvlogical.sgml +++ b/doc/src/sgml/ref/pg_recvlogical.sgml @@ -53,6 +53,16 @@ PostgreSQL documentation (<keycombo action="simul"><keycap>Control</keycap><keycap>C</keycap></keycombo>) or <systemitem>SIGTERM</systemitem> signal. </para> + + <para> + When <application>pg_recvlogical</application> receives + a <systemitem>SIGHUP</systemitem> signal, it closes the current output file + and opens a new one using the filename specified by + the <option>--file</option> option. This allows us to rotate + the output file by first renaming the current file and then sending + a <systemitem>SIGHUP</systemitem> signal to + <application>pg_recvlogical</application>. + </para> </refsect1> <refsect1> @@ -79,8 +89,8 @@ PostgreSQL documentation </para> <para> - The <option>--two-phase</option> and <option>--failover</option> options - can be specified with <option>--create-slot</option>. + The <option>--enable-two-phase</option> and <option>--enable-failover</option> + options can be specified with <option>--create-slot</option>. </para> </listitem> </varlistentry> @@ -166,7 +176,7 @@ PostgreSQL documentation </varlistentry> <varlistentry> - <term><option>--failover</option></term> + <term><option>--enable-failover</option></term> <listitem> <para> Enables the slot to be synchronized to the standbys. This option may @@ -300,7 +310,8 @@ PostgreSQL documentation <varlistentry> <term><option>-t</option></term> - <term><option>--two-phase</option></term> + <term><option>--enable-two-phase</option></term> + <term><option>--two-phase</option> (deprecated)</term> <listitem> <para> Enables decoding of prepared transactions. This option may only be specified with diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 8c88b07dcc8..261ead15039 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -18,9 +18,8 @@ PostgreSQL documentation <refname>pg_restore</refname> <refpurpose> - restore a <productname>PostgreSQL</productname> database or cluster - from an archive created by <application>pg_dump</application> or - <application>pg_dumpall</application> + restore a <productname>PostgreSQL</productname> database from an + archive file created by <application>pg_dump</application> </refpurpose> </refnamediv> @@ -39,14 +38,13 @@ PostgreSQL documentation <para> <application>pg_restore</application> is a utility for restoring a - <productname>PostgreSQL</productname> database or cluster from an archive - created by <xref linkend="app-pgdump"/> or - <xref linkend="app-pg-dumpall"/> in one of the non-plain-text + <productname>PostgreSQL</productname> database from an archive + created by <xref linkend="app-pgdump"/> in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the - database or cluster to the state it was in at the time it was saved. The - archives also allow <application>pg_restore</application> to + database to the state it was in at the time it was saved. The + archive files also allow <application>pg_restore</application> to be selective about what is restored, or even to reorder the items - prior to being restored. The archive formats are designed to be + prior to being restored. The archive files are designed to be portable across architectures. </para> @@ -54,17 +52,10 @@ PostgreSQL documentation <application>pg_restore</application> can operate in two modes. If a database name is specified, <application>pg_restore</application> connects to that database and restores archive contents directly into - the database. - When restoring from a dump made by <application>pg_dumpall</application>, - each database will be created and then the restoration will be run in that - database. - - Otherwise, when a database name is not specified, a script containing the SQL - commands necessary to rebuild the database or cluster is created and written + the database. Otherwise, a script containing the SQL + commands necessary to rebuild the database is created and written to a file or standard output. This script output is equivalent to - the plain text output format of <application>pg_dump</application> or - <application>pg_dumpall</application>. - + the plain text output format of <application>pg_dump</application>. Some of the options controlling the output are therefore analogous to <application>pg_dump</application> options. </para> @@ -149,8 +140,6 @@ PostgreSQL documentation commands that mention this database. Access privileges for the database itself are also restored, unless <option>--no-acl</option> is specified. - <option>--create</option> is required when restoring multiple databases - from an archive created by <application>pg_dumpall</application>. </para> <para> @@ -247,19 +236,6 @@ PostgreSQL documentation </varlistentry> <varlistentry> - <term><option>-g</option></term> - <term><option>--globals-only</option></term> - <listitem> - <para> - Restore only global objects (roles and tablespaces), no databases. - </para> - <para> - This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><option>-I <replaceable class="parameter">index</replaceable></option></term> <term><option>--index=<replaceable class="parameter">index</replaceable></option></term> <listitem> @@ -604,28 +580,6 @@ PostgreSQL documentation </varlistentry> <varlistentry> - <term><option>--exclude-database=<replaceable class="parameter">pattern</replaceable></option></term> - <listitem> - <para> - Do not restore databases whose name matches - <replaceable class="parameter">pattern</replaceable>. - Multiple patterns can be excluded by writing multiple - <option>--exclude-database</option> switches. The - <replaceable class="parameter">pattern</replaceable> parameter is - interpreted as a pattern according to the same rules used by - <application>psql</application>'s <literal>\d</literal> - commands (see <xref linkend="app-psql-patterns"/>), - so multiple databases can also be excluded by writing wildcard - characters in the pattern. When using wildcards, be careful to - quote the pattern if needed to prevent shell wildcard expansion. - </para> - <para> - This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term> <listitem> <para> @@ -862,6 +816,16 @@ PostgreSQL documentation </varlistentry> <varlistentry> + <term><option>--statistics</option></term> + <listitem> + <para> + Output commands to restore statistics, if the archive contains them. + This is the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><option>--statistics-only</option></term> <listitem> <para> @@ -920,36 +884,6 @@ PostgreSQL documentation </varlistentry> <varlistentry> - <term><option>--with-data</option></term> - <listitem> - <para> - Output commands to restore data, if the archive contains them. - This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><option>--with-schema</option></term> - <listitem> - <para> - Output commands to restore schema (data definitions), if the archive - contains them. This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> - <term><option>--with-statistics</option></term> - <listitem> - <para> - Output commands to restore statistics, if the archive contains them. - This is the default. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><option>-?</option></term> <term><option>--help</option></term> <listitem> diff --git a/doc/src/sgml/ref/pgtesttiming.sgml b/doc/src/sgml/ref/pgtesttiming.sgml index a5eb3aa25e0..afe6a12be4b 100644 --- a/doc/src/sgml/ref/pgtesttiming.sgml +++ b/doc/src/sgml/ref/pgtesttiming.sgml @@ -30,11 +30,23 @@ PostgreSQL documentation <title>Description</title> <para> - <application>pg_test_timing</application> is a tool to measure the timing overhead - on your system and confirm that the system time never moves backwards. + <application>pg_test_timing</application> is a tool to measure the + timing overhead on your system and confirm that the system time never + moves backwards. It simply reads the system clock over and over again + as fast as it can for a specified length of time, and then prints + statistics about the observed differences in successive clock readings. + </para> + <para> + Smaller (but not zero) differences are better, since they imply both + more-precise clock hardware and less overhead to collect a clock reading. Systems that are slow to collect timing data can give less accurate <command>EXPLAIN ANALYZE</command> results. </para> + <para> + This tool is also helpful to determine if + the <varname>track_io_timing</varname> configuration parameter is likely + to produce useful results. + </para> </refsect1> <refsect1> @@ -60,6 +72,21 @@ PostgreSQL documentation </varlistentry> <varlistentry> + <term><option>-c <replaceable class="parameter">cutoff</replaceable></option></term> + <term><option>--cutoff=<replaceable class="parameter">cutoff</replaceable></option></term> + <listitem> + <para> + Specifies the cutoff percentage for the list of exact observed + timing durations (that is, the changes in the system clock value + from one reading to the next). The list will end once the running + percentage total reaches or exceeds this value, except that the + largest observed duration will always be printed. The default + cutoff is 99.99. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><option>-V</option></term> <term><option>--version</option></term> <listitem> @@ -92,205 +119,83 @@ PostgreSQL documentation <title>Interpreting Results</title> <para> - Good results will show most (>90%) individual timing calls take less than - one microsecond. Average per loop overhead will be even lower, below 100 - nanoseconds. This example from an Intel i7-860 system using a TSC clock - source shows excellent performance: - -<screen><![CDATA[ -Testing timing overhead for 3 seconds. -Per loop time including overhead: 35.96 ns -Histogram of timing durations: - < us % of total count - 1 96.40465 80435604 - 2 3.59518 2999652 - 4 0.00015 126 - 8 0.00002 13 - 16 0.00000 2 -]]></screen> + The first block of output has four columns, with rows showing a + shifted-by-one log2(ns) histogram of timing durations (that is, the + differences between successive clock readings). This is not the + classic log2(n+1) histogram as it counts zeros separately and then + switches to log2(ns) starting from value 1. </para> - <para> - Note that different units are used for the per loop time than the - histogram. The loop can have resolution within a few nanoseconds (ns), - while the individual timing calls can only resolve down to one microsecond - (us). + The columns are: + <itemizedlist spacing="compact"> + <listitem> + <simpara>nanosecond value that is >= the durations in this + bucket</simpara> + </listitem> + <listitem> + <simpara>percentage of durations in this bucket</simpara> + </listitem> + <listitem> + <simpara>running-sum percentage of durations in this and previous + buckets</simpara> + </listitem> + <listitem> + <simpara>count of durations in this bucket</simpara> + </listitem> + </itemizedlist> </para> - - </refsect2> - <refsect2> - <title>Measuring Executor Timing Overhead</title> - <para> - When the query executor is running a statement using - <command>EXPLAIN ANALYZE</command>, individual operations are timed as well - as showing a summary. The overhead of your system can be checked by - counting rows with the <application>psql</application> program: - -<screen> -CREATE TABLE t AS SELECT * FROM generate_series(1,100000); -\timing -SELECT COUNT(*) FROM t; -EXPLAIN ANALYZE SELECT COUNT(*) FROM t; -</screen> + The second block of output goes into more detail, showing the exact + timing differences observed. For brevity this list is cut off when the + running-sum percentage exceeds the user-selectable cutoff value. + However, the largest observed difference is always shown. </para> - <para> - The i7-860 system measured runs the count query in 9.8 ms while - the <command>EXPLAIN ANALYZE</command> version takes 16.6 ms, each - processing just over 100,000 rows. That 6.8 ms difference means the timing - overhead per row is 68 ns, about twice what pg_test_timing estimated it - would be. Even that relatively small amount of overhead is making the fully - timed count statement take almost 70% longer. On more substantial queries, - the timing overhead would be less problematic. + The example results below show that 99.99% of timing loops took between + 8 and 31 nanoseconds, with the worst case somewhere between 32768 and + 65535 nanoseconds. In the second block, we can see that typical loop + time is 16 nanoseconds, and the readings appear to have full nanosecond + precision. </para> - </refsect2> - - <refsect2> - <title>Changing Time Sources</title> <para> - On some newer Linux systems, it's possible to change the clock source used - to collect timing data at any time. A second example shows the slowdown - possible from switching to the slower acpi_pm time source, on the same - system used for the fast results above: - <screen><![CDATA[ -# cat /sys/devices/system/clocksource/clocksource0/available_clocksource -tsc hpet acpi_pm -# echo acpi_pm > /sys/devices/system/clocksource/clocksource0/current_clocksource -# pg_test_timing -Per loop time including overhead: 722.92 ns +Testing timing overhead for 3 seconds. +Average loop time including overhead: 16.40 ns Histogram of timing durations: - < us % of total count - 1 27.84870 1155682 - 2 72.05956 2990371 - 4 0.07810 3241 - 8 0.01357 563 - 16 0.00007 3 + <= ns % of total running % count + 0 0.0000 0.0000 0 + 1 0.0000 0.0000 0 + 3 0.0000 0.0000 0 + 7 0.0000 0.0000 0 + 15 4.5452 4.5452 8313178 + 31 95.4527 99.9979 174581501 + 63 0.0001 99.9981 253 + 127 0.0001 99.9982 165 + 255 0.0000 99.9982 35 + 511 0.0000 99.9982 1 + 1023 0.0013 99.9994 2300 + 2047 0.0004 99.9998 690 + 4095 0.0000 99.9998 9 + 8191 0.0000 99.9998 8 + 16383 0.0002 100.0000 337 + 32767 0.0000 100.0000 2 + 65535 0.0000 100.0000 1 + +Observed timing durations up to 99.9900%: + ns % of total running % count + 15 4.5452 4.5452 8313178 + 16 58.3785 62.9237 106773354 + 17 33.6840 96.6078 61607584 + 18 3.1151 99.7229 5697480 + 19 0.2638 99.9867 482570 + 20 0.0093 99.9960 17054 +... + 38051 0.0000 100.0000 1 ]]></screen> </para> - <para> - In this configuration, the sample <command>EXPLAIN ANALYZE</command> above - takes 115.9 ms. That's 1061 ns of timing overhead, again a small multiple - of what's measured directly by this utility. That much timing overhead - means the actual query itself is only taking a tiny fraction of the - accounted for time, most of it is being consumed in overhead instead. In - this configuration, any <command>EXPLAIN ANALYZE</command> totals involving - many timed operations would be inflated significantly by timing overhead. - </para> - - <para> - FreeBSD also allows changing the time source on the fly, and it logs - information about the timer selected during boot: - -<screen> -# dmesg | grep "Timecounter" -Timecounter "ACPI-fast" frequency 3579545 Hz quality 900 -Timecounter "i8254" frequency 1193182 Hz quality 0 -Timecounters tick every 10.000 msec -Timecounter "TSC" frequency 2531787134 Hz quality 800 -# sysctl kern.timecounter.hardware=TSC -kern.timecounter.hardware: ACPI-fast -> TSC -</screen> - </para> - - <para> - Other systems may only allow setting the time source on boot. On older - Linux systems the "clock" kernel setting is the only way to make this sort - of change. And even on some more recent ones, the only option you'll see - for a clock source is "jiffies". Jiffies are the older Linux software clock - implementation, which can have good resolution when it's backed by fast - enough timing hardware, as in this example: - -<screen><![CDATA[ -$ cat /sys/devices/system/clocksource/clocksource0/available_clocksource -jiffies -$ dmesg | grep time.c -time.c: Using 3.579545 MHz WALL PM GTOD PIT/TSC timer. -time.c: Detected 2400.153 MHz processor. -$ pg_test_timing -Testing timing overhead for 3 seconds. -Per timing duration including loop overhead: 97.75 ns -Histogram of timing durations: - < us % of total count - 1 90.23734 27694571 - 2 9.75277 2993204 - 4 0.00981 3010 - 8 0.00007 22 - 16 0.00000 1 - 32 0.00000 1 -]]></screen></para> - </refsect2> - - <refsect2> - <title>Clock Hardware and Timing Accuracy</title> - - <para> - Collecting accurate timing information is normally done on computers using - hardware clocks with various levels of accuracy. With some hardware the - operating systems can pass the system clock time almost directly to - programs. A system clock can also be derived from a chip that simply - provides timing interrupts, periodic ticks at some known time interval. In - either case, operating system kernels provide a clock source that hides - these details. But the accuracy of that clock source and how quickly it can - return results varies based on the underlying hardware. - </para> - - <para> - Inaccurate time keeping can result in system instability. Test any change - to the clock source very carefully. Operating system defaults are sometimes - made to favor reliability over best accuracy. And if you are using a virtual - machine, look into the recommended time sources compatible with it. Virtual - hardware faces additional difficulties when emulating timers, and there are - often per operating system settings suggested by vendors. - </para> - - <para> - The Time Stamp Counter (TSC) clock source is the most accurate one available - on current generation CPUs. It's the preferred way to track the system time - when it's supported by the operating system and the TSC clock is - reliable. There are several ways that TSC can fail to provide an accurate - timing source, making it unreliable. Older systems can have a TSC clock that - varies based on the CPU temperature, making it unusable for timing. Trying - to use TSC on some older multicore CPUs can give a reported time that's - inconsistent among multiple cores. This can result in the time going - backwards, a problem this program checks for. And even the newest systems - can fail to provide accurate TSC timing with very aggressive power saving - configurations. - </para> - - <para> - Newer operating systems may check for the known TSC problems and switch to a - slower, more stable clock source when they are seen. If your system - supports TSC time but doesn't default to that, it may be disabled for a good - reason. And some operating systems may not detect all the possible problems - correctly, or will allow using TSC even in situations where it's known to be - inaccurate. - </para> - - <para> - The High Precision Event Timer (HPET) is the preferred timer on systems - where it's available and TSC is not accurate. The timer chip itself is - programmable to allow up to 100 nanosecond resolution, but you may not see - that much accuracy in your system clock. - </para> - - <para> - Advanced Configuration and Power Interface (ACPI) provides a Power - Management (PM) Timer, which Linux refers to as the acpi_pm. The clock - derived from acpi_pm will at best provide 300 nanosecond resolution. - </para> - - <para> - Timers used on older PC hardware include the 8254 Programmable Interval - Timer (PIT), the real-time clock (RTC), the Advanced Programmable Interrupt - Controller (APIC) timer, and the Cyclone timer. These timers aim for - millisecond resolution. - </para> - </refsect2> </refsect1> <refsect1> @@ -298,6 +203,8 @@ Histogram of timing durations: <simplelist type="inline"> <member><xref linkend="sql-explain"/></member> + <member><ulink url="https://wiki.postgresql.org/wiki/Pg_test_timing">Wiki + discussion about timing</ulink></member> </simplelist> </refsect1> </refentry> diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index aeeed297437..5ddf3a8ae92 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -1110,7 +1110,8 @@ psql --username=postgres --file=script.sql postgres <member><type>regproc</type></member> <member><type>regprocedure</type></member> </simplelist> - (<type>regclass</type>, <type>regrole</type>, and <type>regtype</type> can be upgraded.) + (<type>regclass</type>, <type>regdatabase</type>, <type>regrole</type>, and + <type>regtype</type> can be upgraded.) </para> <para> diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 570ef21d1fc..4f7b11175c6 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1067,8 +1067,8 @@ INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1 </listitem> </varlistentry> - <varlistentry id="app-psql-meta-command-close"> - <term><literal>\close</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term> + <varlistentry id="app-psql-meta-command-close-prepared"> + <term><literal>\close_prepared</literal> <replaceable class="parameter">prepared_statement_name</replaceable></term> <listitem> <para> @@ -1081,7 +1081,7 @@ INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1 Example: <programlisting> SELECT $1 \parse stmt1 -\close stmt1 +\close_prepared stmt1 </programlisting> </para> @@ -3710,7 +3710,7 @@ testdb=> <userinput>\setenv LESS -imx4F</userinput> All queries executed while a pipeline is ongoing use the extended query protocol. Queries are appended to the pipeline when ending with a semicolon. The meta-commands <literal>\bind</literal>, - <literal>\bind_named</literal>, <literal>\close</literal> or + <literal>\bind_named</literal>, <literal>\close_prepared</literal> or <literal>\parse</literal> can be used in an ongoing pipeline. While a pipeline is ongoing, <literal>\sendpipeline</literal> will append the current query buffer to the pipeline. Other meta-commands like @@ -4623,6 +4623,15 @@ bar </listitem> </varlistentry> + <varlistentry id="app-psql-variables-servicefile"> + <term><varname>SERVICEFILE</varname></term> + <listitem> + <para> + The service file name, if applicable. + </para> + </listitem> + </varlistentry> + <varlistentry id="app-psql-variables-shell-error"> <term><varname>SHELL_ERROR</varname></term> <listitem> diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 12ec5ba0709..40cca063946 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -57,7 +57,8 @@ UPDATE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ to compute and return value(s) based on each row actually updated. Any expression using the table's columns, and/or columns of other tables mentioned in <literal>FROM</literal>, can be computed. - The new (post-update) values of the table's columns are used. + By default, the new (post-update) values of the table's columns are used, + but it is also possible to request the old (pre-update) values. The syntax of the <literal>RETURNING</literal> list is identical to that of the output list of <command>SELECT</command>. </para> diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index b0680a61814..c7d9dca17b8 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -282,9 +282,11 @@ PostgreSQL documentation <listitem> <para> Only analyze relations that are missing statistics for a column, index - expression, or extended statistics object. This option prevents - <application>vacuumdb</application> from deleting existing statistics - so that the query optimizer's choices do not become transiently worse. + expression, or extended statistics object. When used with + <option>--analyze-in-stages</option>, this option prevents + <application>vacuumdb</application> from temporarily replacing existing + statistics with ones generated with lower statistics targets, thus + avoiding transiently worse query optimizer choices. </para> <para> This option can only be used in conjunction with |