Some mop-up work for savepoints (nested transactions). Store a small

number of active subtransaction XIDs in each backend's PGPROC entry,
and use this to avoid expensive probes into pg_subtrans during
TransactionIdIsInProgress.  Extend EOXactCallback API to allow add-on
modules to get control at subxact start/end.  (This is deliberately
not compatible with the former API, since any uses of that API probably
need manual review anyway.)  Add basic reference documentation for
SAVEPOINT and related commands.  Minor other cleanups to check off some
of the open issues for subtransactions.
Alvaro Herrera and Tom Lane.

9 files changed, 532 insertions, 7 deletions

diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 6980dc47752..475ba910a1e 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.41 2004/03/31 16:20:53 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/advanced.sgml,v 1.42 2004/08/01 17:32:11 tgl Exp $
 -->
 
  <chapter id="tutorial-advanced">
@@ -257,6 +257,64 @@ COMMIT;
      you are using.
     </para>
    </note>
+
+   <para>
+    It's possible to control the statements in a transaction in a more
+    granular fashion through the use of <firstterm>savepoints</>.  Savepoints
+    allow you to selectively discard parts of the transaction, while
+    committing the rest.  After defining a savepoint with
+    <command>SAVEPOINT</>, you can if needed roll back to the savepoint
+    with <command>ROLLBACK TO</>.  All the transaction's database changes
+    between defining the savepoint and rolling back to it are discarded, but
+    changes earlier than the savepoint are kept.
+   </para> 
+
+   <para>
+    After rolling back to a savepoint, it continues to be defined, so you can
+    roll back to it several times.  Conversely, if you are sure you won't need
+    to roll back to a particular savepoint again, it can be released, so the
+    system can free some resources.  Keep in mind that either releasing or
+    rolling back to a savepoint
+    will automatically release all savepoints that were defined after it.
+   </para> 
+
+   <para>
+    All this is happening within the transaction block, so none of it
+    is visible to other database sessions.  When and if you commit the
+    transaction block, the committed actions become visible as a unit
+    to other sessions, while the rolled-back actions never become visible
+    at all.
+   </para> 
+
+   <para>
+    Remembering the bank database, suppose we debit $100.00 from Alice's
+    account, and credit Bob's account, only to find later that we should
+    have credited Wally's account.  We could do it using savepoints like
+
+<programlisting>
+BEGIN;
+UPDATE accounts SET balance = balance - 100.00
+    WHERE name = 'Alice';
+SAVEPOINT my_savepoint;
+UPDATE accounts SET balance = balance + 100.00
+    WHERE name = 'Bob';
+-- oops ... forget that and use Wally's account
+ROLLBACK TO my_savepoint;
+UPDATE accounts SET balance = balance + 100.00
+    WHERE name = 'Wally';
+COMMIT;
+</programlisting>
+   </para>
+
+   <para>
+    This example is, of course, oversimplified, but there's a lot of control
+    to be had over a transaction block through the use of savepoints.
+    Moreover, <command>ROLLBACK TO</> is the only way to regain control of a
+    transaction block that was put in aborted state by the
+    system due to an error, short of rolling it back completely and starting
+    again.
+   </para>
+
   </sect1>
 
 
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index f02edd4cff2..2ab20c9c690 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.59 2004/06/25 21:55:50 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.60 2004/08/01 17:32:13 tgl Exp $
 PostgreSQL documentation
 Complete list of usable sgml source files in this directory.
 -->
@@ -88,9 +88,12 @@ Complete list of usable sgml source files in this directory.
 <!entity notify             system "notify.sgml">
 <!entity prepare            system "prepare.sgml">
 <!entity reindex            system "reindex.sgml">
+<!entity releaseSavepoint   system "release.sgml">
 <!entity reset              system "reset.sgml">
 <!entity revoke             system "revoke.sgml">
 <!entity rollback           system "rollback.sgml">
+<!entity rollbackTo         system "rollback_to.sgml">
+<!entity savepoint          system "savepoint.sgml">
 <!entity select             system "select.sgml">
 <!entity selectInto         system "select_into.sgml">
 <!entity set                system "set.sgml">
diff --git a/doc/src/sgml/ref/begin.sgml b/doc/src/sgml/ref/begin.sgml
index d8ddf81ee06..d40cb416bc5 100644
--- a/doc/src/sgml/ref/begin.sgml
+++ b/doc/src/sgml/ref/begin.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/begin.sgml,v 1.30 2004/01/11 09:24:17 petere Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/begin.sgml,v 1.31 2004/08/01 17:32:13 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -31,7 +31,7 @@ BEGIN [ WORK | TRANSACTION ]
 
   <para>
    <command>BEGIN</command> initiates a transaction block, that is,
-   all statements after <command>BEGIN</command> command will be
+   all statements after a <command>BEGIN</command> command will be
    executed in a single transaction until an explicit <xref
    linkend="sql-commit" endterm="sql-commit-title"> or <xref
    linkend="sql-rollback" endterm="sql-rollback-title"> is given.
@@ -145,6 +145,7 @@ BEGIN;
    <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
    <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
    <member><xref linkend="sql-start-transaction" endterm="sql-start-transaction-title"></member>
+   <member><xref linkend="sql-savepoint" endterm="sql-savepoint-title"></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/ref/release.sgml b/doc/src/sgml/ref/release.sgml
new file mode 100644
index 00000000000..17ef14ee3c5
--- /dev/null
+++ b/doc/src/sgml/ref/release.sgml
@@ -0,0 +1,143 @@
+<!--
+$PostgreSQL: pgsql/doc/src/sgml/ref/release.sgml,v 1.1 2004/08/01 17:32:13 tgl Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-RELEASE">
+ <refmeta>
+  <refentrytitle id="SQL-RELEASE-TITLE">RELEASE</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>RELEASE</refname>
+  <refpurpose>destroy a previously defined savepoint</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-release">
+  <primary>RELEASE</primary>
+ </indexterm>
+
+ <indexterm zone="sql-release">
+  <primary>savepoints</primary>
+  <secondary>releasing</secondary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+RELEASE <replaceable>savepoint_name</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+  
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <command>RELEASE</command> destroys a savepoint previously defined
+   in the current transaction.
+  </para>
+
+  <para>
+   Destroying a savepoint makes it unavailable as a rollback point,
+   but it has no other user visible behavior.  It does not undo the
+   effects of commands executed after the savepoint was established.
+   (To do that, see <xref linkend="sql-rollback-to"
+   endterm="sql-rollback-to-title">.)  Destroying a savepoint when
+   it is no longer needed may allow the system to reclaim some resources
+   earlier than transaction end.
+  </para>
+
+  <para>
+   <command>RELEASE</command> also destroys all savepoints that were
+   established after the named savepoint was established.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Parameters</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><replaceable>savepoint_name</replaceable></term>
+    <listitem>
+     <para>
+      The name of the savepoint to destroy.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   Specifying a savepoint name that was not previously defined is an error.
+  </para>
+
+  <para>
+   It is not possible to release a savepoint when the transaction is in
+   aborted state.
+  </para>
+
+  <para>
+   If multiple savepoints have the same name, only the one that was most
+   recently defined is released.
+  </para>
+
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   To establish and later destroy a savepoint:
+<programlisting>
+BEGIN;
+    INSERT INTO table VALUES (3);
+    SAVEPOINT my_savepoint;
+    INSERT INTO table VALUES (4);
+    RELEASE my_savepoint;
+COMMIT;
+</programlisting>
+   The above transaction will insert both 3 and 4.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+  
+  <para>
+   RELEASE is fully conforming to the SQL standard.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-begin" endterm="sql-begin-title"></member>
+   <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
+   <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
+   <member><xref linkend="sql-rollback-to" endterm="sql-rollback-to-title"></member>
+   <member><xref linkend="sql-savepoint" endterm="sql-savepoint-title"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/rollback.sgml b/doc/src/sgml/ref/rollback.sgml
index f7e5e9fa281..53b7af3dd15 100644
--- a/doc/src/sgml/ref/rollback.sgml
+++ b/doc/src/sgml/ref/rollback.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/rollback.sgml,v 1.17 2003/11/29 19:51:39 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/rollback.sgml,v 1.18 2004/08/01 17:32:13 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -90,6 +90,7 @@ ROLLBACK;
   <simplelist type="inline">
    <member><xref linkend="sql-begin" endterm="sql-begin-title"></member>
    <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
+   <member><xref linkend="sql-rollback-to" endterm="sql-rollback-to-title"></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/ref/rollback_to.sgml b/doc/src/sgml/ref/rollback_to.sgml
new file mode 100644
index 00000000000..e38c4f4c4fd
--- /dev/null
+++ b/doc/src/sgml/ref/rollback_to.sgml
@@ -0,0 +1,163 @@
+<!--
+$PostgreSQL: pgsql/doc/src/sgml/ref/rollback_to.sgml,v 1.1 2004/08/01 17:32:13 tgl Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-ROLLBACK-TO">
+ <refmeta>
+  <refentrytitle id="SQL-ROLLBACK-TO-TITLE">ROLLBACK TO</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>ROLLBACK TO</refname>
+  <refpurpose>roll back to a savepoint</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-rollback-to">
+  <primary>ROLLBACK TO</primary>
+ </indexterm>
+
+ <indexterm zone="sql-rollback-to">
+  <primary>savepoints</primary>
+  <secondary>rolling back</secondary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+ROLLBACK TO <replaceable>savepoint_name</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   Roll back all commands that were executed after the savepoint was
+   established.  The savepoint remains valid and can be rolled back to
+   again later, if needed.
+  </para>
+
+  <para>
+   <command>ROLLBACK TO</> implicitly destroys all savepoints that
+   were established after the named savepoint.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Parameters</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><replaceable class="PARAMETER">savepoint_name</></term>
+    <listitem>
+     <para>
+      The savepoint to roll back to.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   Use <xref linkend="SQL-RELEASE" endterm="SQL-RELEASE-TITLE"> to
+   destroy a savepoint without discarding the effects of commands executed
+   after it was established.
+  </para>
+
+  <para>
+   Specifying a savepoint name that has not been established is an error.
+  </para>
+
+  <para>
+   Cursors have somewhat non-transactional behavior with respect to
+   savepoints.  Any cursor that is opened inside the savepoint is not closed
+   when the savepoint is rolled back.  If a cursor is affected by a
+   <command>FETCH</> command inside a savepoint that is later rolled
+   back, the cursor position remains at the position that <command>FETCH</>
+   left it pointing to (that is, <command>FETCH</> is not rolled back).
+   A cursor whose execution causes a transaction to abort is put in a
+   can't-execute state, so while the transaction can be restored using
+   <command>ROLLBACK TO</>, the cursor can no longer be used.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   To undo the effects of the commands executed after <literal>my_savepoint</literal>
+   was established:
+<programlisting>
+ROLLBACK TO my_savepoint;
+</programlisting>
+  </para>
+
+  <para>
+   Cursor positions are not affected by savepoint rollback:
+<programlisting>
+BEGIN;
+
+DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
+
+SAVEPOINT foo;
+
+FETCH 1 FROM foo;
+ ?column? 
+----------
+        1
+
+ROLLBACK TO foo;
+
+FETCH 1 FROM foo;
+ ?column? 
+----------
+        2
+
+COMMIT;
+</programlisting>
+   </para>
+
+
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+
+  <para>
+   This command is fully SQL standard conforming.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-begin" endterm="sql-begin-title"></member>
+   <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
+   <member><xref linkend="sql-savepoint" endterm="sql-savepoint-title"></member>
+   <member><xref linkend="sql-release" endterm="sql-release-title"></member>
+   <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/savepoint.sgml b/doc/src/sgml/ref/savepoint.sgml
new file mode 100644
index 00000000000..b881191c436
--- /dev/null
+++ b/doc/src/sgml/ref/savepoint.sgml
@@ -0,0 +1,152 @@
+<!--
+$PostgreSQL: pgsql/doc/src/sgml/ref/savepoint.sgml,v 1.1 2004/08/01 17:32:13 tgl Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-SAVEPOINT">
+ <refmeta>
+  <refentrytitle id="SQL-SAVEPOINT-TITLE">SAVEPOINT</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>SAVEPOINT</refname>
+  <refpurpose>define a new savepoint within the current transaction</refpurpose>
+ </refnamediv>
+
+ <indexterm zone="sql-savepoint">
+  <primary>SAVEPOINT</primary>
+ </indexterm>
+
+ <indexterm zone="sql-savepoint">
+  <primary>savepoints</primary>
+  <secondary>defining</secondary>
+ </indexterm>
+
+ <refsynopsisdiv>
+<synopsis>
+SAVEPOINT <replaceable>savepoint_name</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+  
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <command>SAVEPOINT</command> establishes a new savepoint within
+   the current transaction.
+  </para>
+
+  <para>
+   A savepoint is a special mark inside a transaction that allows all commands
+   that are executed after it was established to be rolled back, restoring
+   the transaction state to what it was at the time of the savepoint.
+  </para>
+ </refsect1>
+  
+ <refsect1>
+  <title>Parameters</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><replaceable>savepoint_name</replaceable></term>
+    <listitem>
+     <para>
+      The name to give to the new savepoint.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   Use <xref linkend="SQL-ROLLBACK-TO" endterm="SQL-ROLLBACK-TO-TITLE"> to
+   rollback to a savepoint.  Use <xref linkend="SQL-RELEASE"
+   endterm="SQL-RELEASE-TITLE"> to destroy a savepoint, keeping
+   the effects of commands executed after it was established.
+  </para>
+
+  <para>
+   Savepoints can only be established when inside a transaction block.
+   There can be multiple savepoints defined within a transaction.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   To establish a savepoint and later undo the effects of all commands executed
+   after it was established:
+<programlisting>
+BEGIN;
+    INSERT INTO table VALUES (1);
+    SAVEPOINT my_savepoint;
+    INSERT INTO table VALUES (2);
+    ROLLBACK TO my_savepoint;
+    INSERT INTO table VALUES (3);
+COMMIT;
+</programlisting>
+   The above transaction will insert the values 1 and 3, but not 2.
+  </para>
+
+  <para>
+   To establish and later destroy a savepoint:
+<programlisting>
+BEGIN;
+    INSERT INTO table VALUES (3);
+    SAVEPOINT my_savepoint;
+    INSERT INTO table VALUES (4);
+    RELEASE my_savepoint;
+COMMIT;
+</programlisting>
+   The above transaction will insert both 3 and 4.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+  
+  <para>
+   SQL requires a savepoint to be destroyed automatically when another
+   savepoint with the same name is established.  In
+   <productname>PostgreSQL</>, the old savepoint is kept, though only the more
+   recent one will be used when rolling back or releasing.  (Releasing the
+   newer savepoint will cause the older one to again become accessible to
+   <command>ROLLBACK TO</> and <command>RELEASE</>.)
+   Other than that, <command>SAVEPOINT</command> is fully SQL conforming.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-begin" endterm="sql-begin-title"></member>
+   <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
+   <member><xref linkend="sql-rollback-to" endterm="sql-rollback-to-title"></member>
+   <member><xref linkend="sql-release" endterm="sql-release-title"></member>
+   <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/start_transaction.sgml b/doc/src/sgml/ref/start_transaction.sgml
index 5cecbf75565..1a7bc363ce7 100644
--- a/doc/src/sgml/ref/start_transaction.sgml
+++ b/doc/src/sgml/ref/start_transaction.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/start_transaction.sgml,v 1.11 2004/01/11 05:46:58 neilc Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/start_transaction.sgml,v 1.12 2004/08/01 17:32:13 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -66,6 +66,7 @@ START TRANSACTION
    <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
    <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
    <member><xref linkend="sql-set-transaction" endterm="sql-set-transaction-title"></member>
+   <member><xref linkend="sql-savepoint" endterm="sql-savepoint-title"></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index 5230e07119e..23164a57c70 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,5 +1,5 @@
 <!-- reference.sgml
-$PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.50 2004/06/25 21:55:51 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/reference.sgml,v 1.51 2004/08/01 17:32:11 tgl Exp $
 
 PostgreSQL Reference Manual
 -->
@@ -120,9 +120,12 @@ PostgreSQL Reference Manual
    &notify;
    &prepare;
    &reindex;
+   &releaseSavepoint;
    &reset;
    &revoke;
    &rollback;
+   &rollbackTo;
+   &savepoint;
    &select;
    &selectInto;
    &set;