Support synchronization of snapshots through an export/import procedure.

A transaction can export a snapshot with pg_export_snapshot(), and then
others can import it with SET TRANSACTION SNAPSHOT.  The data does not
leave the server so there are not security issues.  A snapshot can only
be imported while the exporting transaction is still running, and there
are some other restrictions.

I'm not totally convinced that we've covered all the bases for SSI (true
serializable) mode, but it works fine for lesser isolation modes.

Joachim Wieland, reviewed by Marko Tiikkaja, and rather heavily modified
by Tom Lane

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>