aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/func.sgml130
-rw-r--r--doc/src/sgml/ref/set_transaction.sgml79
-rw-r--r--doc/src/sgml/storage.sgml5
3 files changed, 198 insertions, 16 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 45b99566973..8dd69337f11 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13803,6 +13803,14 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33);
<title>System Administration Functions</title>
<para>
+ The functions described in this section are used to control and
+ monitor a <productname>PostgreSQL</> installation.
+ </para>
+
+ <sect2 id="functions-admin-set">
+ <title>Configuration Settings Functions</title>
+
+ <para>
<xref linkend="functions-admin-set-table"> shows the functions
available to query and alter run-time configuration parameters.
</para>
@@ -13889,6 +13897,11 @@ SELECT set_config('log_statement_stats', 'off', false);
</programlisting>
</para>
+ </sect2>
+
+ <sect2 id="functions-admin-signal">
+ <title>Server Signalling Functions</title>
+
<indexterm>
<primary>pg_cancel_backend</primary>
</indexterm>
@@ -13985,6 +13998,11 @@ SELECT set_config('log_statement_stats', 'off', false);
subprocess.
</para>
+ </sect2>
+
+ <sect2 id="functions-admin-backup">
+ <title>Backup Control Functions</title>
+
<indexterm>
<primary>backup</primary>
</indexterm>
@@ -14181,6 +14199,11 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
<xref linkend="continuous-archiving">.
</para>
+ </sect2>
+
+ <sect2 id="functions-recovery-control">
+ <title>Recovery Control Functions</title>
+
<indexterm>
<primary>pg_is_in_recovery</primary>
</indexterm>
@@ -14198,7 +14221,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
The functions shown in <xref
linkend="functions-recovery-info-table"> provide information
about the current status of the standby.
- These functions may be executed during both recovery and in normal running.
+ These functions may be executed both during recovery and in normal running.
</para>
<table id="functions-recovery-info-table">
@@ -14333,6 +14356,87 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
the pause, the rate of WAL generation and available disk space.
</para>
+ </sect2>
+
+ <sect2 id="functions-snapshot-synchronization">
+ <title>Snapshot Synchronization Functions</title>
+
+ <indexterm>
+ <primary>pg_export_snapshot</primary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</> allows database sessions to synchronize their
+ snapshots. A <firstterm>snapshot</> determines which data is visible to the
+ transaction that is using the snapshot. Synchronized snapshots are
+ necessary when two or more sessions need to see identical content in the
+ database. If two sessions just start their transactions independently,
+ there is always a possibility that some third transaction commits
+ between the executions of the two <command>START TRANSACTION</> commands,
+ so that one session sees the effects of that transaction and the other
+ does not.
+ </para>
+
+ <para>
+ To solve this problem, <productname>PostgreSQL</> allows a transaction to
+ <firstterm>export</> the snapshot it is using. As long as the exporting
+ transaction remains open, other transactions can <firstterm>import</> its
+ snapshot, and thereby be guaranteed that they see exactly the same view
+ of the database that the first transaction sees. But note that any
+ database changes made by any one of these transactions remain invisible
+ to the other transactions, as is usual for changes made by uncommitted
+ transactions. So the transactions are synchronized with respect to
+ pre-existing data, but act normally for changes they make themselves.
+ </para>
+
+ <para>
+ Snapshots are exported with the <function>pg_export_snapshot</> function,
+ shown in <xref linkend="functions-snapshot-synchronization-table">, and
+ imported with the <xref linkend="sql-set-transaction"> command.
+ </para>
+
+ <table id="functions-snapshot-synchronization-table">
+ <title>Snapshot Synchronization Functions</title>
+ <tgroup cols="3">
+ <thead>
+ <row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <literal><function>pg_export_snapshot()</function></literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>Save the current snapshot and return its identifier</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ The function <function>pg_export_snapshot</> saves the current snapshot
+ and returns a <type>text</> string identifying the snapshot. This string
+ must be passed (outside the database) to clients that want to import the
+ snapshot. The snapshot is available for import only until the end of the
+ transaction that exported it. A transaction can export more than one
+ snapshot, if needed. Note that doing so is only useful in <literal>READ
+ COMMITTED</> transactions, since in <literal>REPEATABLE READ</> and
+ higher isolation levels, transactions use the same snapshot throughout
+ their lifetime. Once a transaction has exported any snapshots, it cannot
+ be prepared with <xref linkend="sql-prepare-transaction">.
+ </para>
+
+ <para>
+ See <xref linkend="sql-set-transaction"> for details of how to use an
+ exported snapshot.
+ </para>
+ </sect2>
+
+ <sect2 id="functions-admin-dbobject">
+ <title>Database Object Management Functions</title>
+
<para>
The functions shown in <xref linkend="functions-admin-dbsize"> calculate
the disk space usage of database objects.
@@ -14591,9 +14695,14 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
the relation.
</para>
+ </sect2>
+
+ <sect2 id="functions-admin-genfile">
+ <title>Generic File Access Functions</title>
+
<para>
The functions shown in <xref
- linkend="functions-admin-genfile"> provide native access to
+ linkend="functions-admin-genfile-table"> provide native access to
files on the machine hosting the server. Only files within the
database cluster directory and the <varname>log_directory</> can be
accessed. Use a relative path for files in the cluster directory,
@@ -14601,7 +14710,7 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
for log files. Use of these functions is restricted to superusers.
</para>
- <table id="functions-admin-genfile">
+ <table id="functions-admin-genfile-table">
<title>Generic File Access Functions</title>
<tgroup cols="3">
<thead>
@@ -14694,13 +14803,18 @@ SELECT (pg_stat_file('filename')).modification;
</programlisting>
</para>
+ </sect2>
+
+ <sect2 id="functions-advisory-locks">
+ <title>Advisory Lock Functions</title>
+
<para>
- The functions shown in <xref linkend="functions-advisory-locks"> manage
- advisory locks. For details about proper use of these functions, see
- <xref linkend="advisory-locks">.
+ The functions shown in <xref linkend="functions-advisory-locks-table">
+ manage advisory locks. For details about proper use of these functions,
+ see <xref linkend="advisory-locks">.
</para>
- <table id="functions-advisory-locks">
+ <table id="functions-advisory-locks-table">
<title>Advisory Lock Functions</title>
<tgroup cols="3">
<thead>
@@ -14972,6 +15086,8 @@ SELECT (pg_stat_file('filename')).modification;
at session end, even if the client disconnects ungracefully.)
</para>
+ </sect2>
+
</sect1>
<sect1 id="functions-trigger">
diff --git a/doc/src/sgml/ref/set_transaction.sgml b/doc/src/sgml/ref/set_transaction.sgml
index e28a7e1cde2..4327ca51a69 100644
--- a/doc/src/sgml/ref/set_transaction.sgml
+++ b/doc/src/sgml/ref/set_transaction.sgml
@@ -33,6 +33,7 @@
<refsynopsisdiv>
<synopsis>
SET TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
+SET TRANSACTION SNAPSHOT <replaceable class="parameter">snapshot_id</replaceable>
SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transaction_mode</replaceable> [, ...]
<phrase>where <replaceable class="parameter">transaction_mode</replaceable> is one of:</phrase>
@@ -60,6 +61,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
The available transaction characteristics are the transaction
isolation level, the transaction access mode (read/write or
read-only), and the deferrable mode.
+ In addition, a snapshot can be selected, though only for the current
+ transaction, not as a session default.
</para>
<para>
@@ -98,7 +101,7 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
serializable transactions would create a situation which could not
have occurred for any serial (one-at-a-time) execution of those
transactions, one of them will be rolled back with a
- <literal>serialization_failure</literal> <literal>SQLSTATE</literal>.
+ <literal>serialization_failure</literal> error.
</para>
</listitem>
</varlistentry>
@@ -139,13 +142,41 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
<para>
The <literal>DEFERRABLE</literal> transaction property has no effect
unless the transaction is also <literal>SERIALIZABLE</literal> and
- <literal>READ ONLY</literal>. When all of these properties are set on a
+ <literal>READ ONLY</literal>. When all three of these properties are
+ selected for a
transaction, the transaction may block when first acquiring its snapshot,
after which it is able to run without the normal overhead of a
<literal>SERIALIZABLE</literal> transaction and without any risk of
contributing to or being canceled by a serialization failure. This mode
is well suited for long-running reports or backups.
</para>
+
+ <para>
+ The <literal>SET TRANSACTION SNAPSHOT</literal> command allows a new
+ transaction to run with the same <firstterm>snapshot</> as an existing
+ transaction. The pre-existing transaction must have exported its snapshot
+ with the <literal>pg_export_snapshot</literal> function (see <xref
+ linkend="functions-snapshot-synchronization">). That function returns a
+ snapshot identifier, which must be given to <literal>SET TRANSACTION
+ SNAPSHOT</literal> to specify which snapshot is to be imported. The
+ identifier must be written as a string literal in this command, for example
+ <literal>'000003A1-1'</>.
+ <literal>SET TRANSACTION SNAPSHOT</literal> can only be executed at the
+ start of a transaction, before the first query or
+ data-modification statement (<command>SELECT</command>,
+ <command>INSERT</command>, <command>DELETE</command>,
+ <command>UPDATE</command>, <command>FETCH</command>, or
+ <command>COPY</command>) of the transaction. Furthermore, the transaction
+ must already be set to <literal>SERIALIZABLE</literal> or
+ <literal>REPEATABLE READ</literal> isolation level (otherwise, the snapshot
+ would be discarded immediately, since <literal>READ COMMITTED</> mode takes
+ a new snapshot for each command). If the importing transaction uses
+ <literal>SERIALIZABLE</literal> isolation level, then the transaction that
+ exported the snapshot must also use that isolation level. Also, a
+ non-read-only serializable transaction cannot import a snapshot from a
+ read-only transaction.
+ </para>
+
</refsect1>
<refsect1>
@@ -163,6 +194,8 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
by instead specifying the desired <replaceable
class="parameter">transaction_modes</replaceable> in
<command>BEGIN</command> or <command>START TRANSACTION</command>.
+ But that option is not available for <command>SET TRANSACTION
+ SNAPSHOT</command>.
</para>
<para>
@@ -178,11 +211,45 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
</para>
</refsect1>
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To begin a new transaction with the same snapshot as an already
+ existing transaction, first export the snapshot from the existing
+ transaction. That will return the snapshot identifier, for example:
+
+<programlisting>
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SELECT pg_export_snapshot();
+ pg_export_snapshot
+--------------------
+ 000003A1-1
+(1 row)
+</programlisting>
+
+ Then give the snapshot identifier in a <command>SET TRANSACTION
+ SNAPSHOT</command> command at the beginning of the newly opened
+ transaction:
+
+<programlisting>
+BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+SET TRANSACTION SNAPSHOT '000003A1-1';
+</programlisting>
+ </para>
+ </refsect1>
+
<refsect1 id="R1-SQL-SET-TRANSACTION-3">
<title>Compatibility</title>
<para>
- Both commands are defined in the <acronym>SQL</acronym> standard.
+ These commands are defined in the <acronym>SQL</acronym> standard,
+ except for the <literal>DEFERRABLE</literal> transaction mode
+ and the <command>SET TRANSACTION SNAPSHOT</> form, which are
+ <productname>PostgreSQL</productname> extensions.
+ </para>
+
+ <para>
<literal>SERIALIZABLE</literal> is the default transaction
isolation level in the standard. In
<productname>PostgreSQL</productname> the default is ordinarily
@@ -198,12 +265,6 @@ SET SESSION CHARACTERISTICS AS TRANSACTION <replaceable class="parameter">transa
</para>
<para>
- The <literal>DEFERRABLE</literal>
- <replaceable class="parameter">transaction_mode</replaceable>
- is a <productname>PostgreSQL</productname> language extension.
- </para>
-
- <para>
The SQL standard requires commas between successive <replaceable
class="parameter">transaction_modes</replaceable>, but for historical
reasons <productname>PostgreSQL</productname> allows the commas to be
diff --git a/doc/src/sgml/storage.sgml b/doc/src/sgml/storage.sgml
index 0a133bb7c7e..cb2f60e1eee 100644
--- a/doc/src/sgml/storage.sgml
+++ b/doc/src/sgml/storage.sgml
@@ -88,6 +88,11 @@ Item
</row>
<row>
+ <entry><filename>pg_snapshots</></entry>
+ <entry>Subdirectory containing exported snapshots</entry>
+</row>
+
+<row>
<entry><filename>pg_stat_tmp</></entry>
<entry>Subdirectory containing temporary files for the statistics
subsystem</entry>
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-01 20:47:26 +0000