Hackers,
Here is a doc patch that includes pages for savepoint commands. It
would be cool if they can be applied as starting points for savepoint
documentation. Poeple who can do better, please feel free to improve in
any way.
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Cómo ponemos nuestros dedos en la arcilla del otro. Eso es la amistad; jugar
al alfarero y ver qué formas se pueden sacar del otro" (C. Halloway en
La Feria de las Tinieblas, R. Bradbury)
diff -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/advanced.sgml
03pgproc/doc/src/sgml/advanced.sgml
*** 00orig/doc/src/sgml/advanced.sgml 2004-04-14 16:45:53.000000000 -0400
--- 03pgproc/doc/src/sgml/advanced.sgml 2004-07-26 22:39:01.689881464 -0400
***************
*** 257,262 ****
--- 257,310 ----
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. This is done be defining a savepoint with
+ <command>SAVEPOINT</>, to which you can later roll back using
+ <command>ROLLBACK TO</>. All statements between defining the savepoint
+ and rolling back to it will have no effect on the final transaction.
+ </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 releasing a savepoint
+ will automatically release all savepoints that were defined after it.
+ </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 wanted to
+ credit 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 automatically put on aborted state by the
+ system for some reason, short of rolling it back completely and starting
+ again.
+ </para>
+
</sect1>
diff -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/ref/allfiles.sgml
03pgproc/doc/src/sgml/ref/allfiles.sgml
*** 00orig/doc/src/sgml/ref/allfiles.sgml 2004-06-26 00:28:45.000000000 -0400
--- 03pgproc/doc/src/sgml/ref/allfiles.sgml 2004-07-26 18:27:47.000000000 -0400
***************
*** 88,96 ****
--- 88,99 ----
<!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 -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/ref/begin.sgml
03pgproc/doc/src/sgml/ref/begin.sgml
*** 00orig/doc/src/sgml/ref/begin.sgml 2004-01-11 06:24:17.000000000 -0300
--- 03pgproc/doc/src/sgml/ref/begin.sgml 2004-07-26 18:49:53.000000000 -0400
***************
*** 145,150 ****
--- 145,151 ----
<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 -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/ref/release.sgml
03pgproc/doc/src/sgml/ref/release.sgml
*** 00orig/doc/src/sgml/ref/release.sgml 1969-12-31 21:00:00.000000000 -0300
--- 03pgproc/doc/src/sgml/ref/release.sgml 2004-07-26 19:07:17.000000000 -0400
***************
*** 0 ****
--- 1,138 ----
+ <!--
+ $PostgreSQL$
+ 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 previously defined savepoint
+ in the current transaction.
+ </para>
+
+ <para>
+ Destroying a savepoint makes it—and all savepoints established after
+ it was established—unavailable as rollback points,
+ but it has no other user visible behavior. It does not undo the
+ effects of command executed after the savepoint was established.
+ To do that, see <xref linkend="sql-rollback-to"
+ endterm="sql-rollback-to-title">.
+ </para>
+
+ <para>
+ <command>RELEASE</command> also destroys all savepoints that were established
+ after the named savepoint was established.
+ </para>
+
+ <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 raises
+ an exception.
+ </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 last
+ 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;
+ </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-rollback" endterm="sql-rollback-title"></member>
+ <member><xref linkend="sql-savepoint" endterm="sql-savepoint-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 -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/ref/rollback.sgml
03pgproc/doc/src/sgml/ref/rollback.sgml
*** 00orig/doc/src/sgml/ref/rollback.sgml 2003-11-29 16:51:39.000000000 -0300
--- 03pgproc/doc/src/sgml/ref/rollback.sgml 2004-07-26 18:49:30.000000000 -0400
***************
*** 90,95 ****
--- 90,96 ----
<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 -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/ref/rollback_to.sgml
03pgproc/doc/src/sgml/ref/rollback_to.sgml
*** 00orig/doc/src/sgml/ref/rollback_to.sgml 1969-12-31 21:00:00.000000000 -0300
--- 03pgproc/doc/src/sgml/ref/rollback_to.sgml 2004-07-26 22:31:24.566374800 -0400
***************
*** 0 ****
--- 1,158 ----
+ <!--
+ $PostgreSQL$
+ 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 and destroy all savepoints that
+ were created after the savepoint was established. The savepoint is
+ automatically established again.
+ </para>
+
+ <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 causes an
+ exception to be raised.
+ </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 no longer can be used.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To undo the effects of the commands executed after <literal>my_savepoint</literal>
+ was established, and establish <literal>my_savepoint</> again:
+ <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 -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/ref/savepoint.sgml
03pgproc/doc/src/sgml/ref/savepoint.sgml
*** 00orig/doc/src/sgml/ref/savepoint.sgml 1969-12-31 21:00:00.000000000 -0300
--- 03pgproc/doc/src/sgml/ref/savepoint.sgml 2004-07-26 19:07:40.000000000 -0400
***************
*** 0 ****
--- 1,153 ----
+ <!--
+ $PostgreSQL$
+ 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>
+
+ </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>
+ A savepoint is a special mark inside a transaction that allows all commands
+ that are executed after it was established to be rolled back.
+ Alternatively, a savepoint can be destroyed so that it isn't a possible
+ rollback destination anymore. In this case, all commands that were executed after
+ the savepoint was established are preserved.
+ </para>
+
+ <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.
+ Issuing <command>SAVEPOINT</> when not inside a transaction block
+ will cause an exception to be raised.
+ </para>
+
+ <para>
+ There can be multiple savepoints defined within a transaction.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To establish a savepoint and undo the effects of all commands executed
+ after it was established, keeping only the first inserted value
+ in the table:
+ <programlisting>
+ BEGIN;
+ INSERT INTO table VALUES (1);
+ SAVEPOINT my_savepoint;
+ INSERT INTO table VALUES (2);
+ ROLLBACK TO my_savepoint;
+ COMMIT;
+ </programlisting>
+ </para>
+
+ <para>
+ To establish and later destroy a savepoint, keeping both values in the table:
+ <programlisting>
+ BEGIN;
+ INSERT INTO table VALUES (3);
+ SAVEPOINT my_savepoint;
+ INSERT INTO table VALUES (4);
+ RELEASE my_savepoint;
+ COMMIT;
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ SQL requires a savepoint to be automatically destroyed when another savepoint
+ with the same name is established. In <productname>PostgreSQL</>, the old
+ savepoint is kept, though only the last one will be used when rolling back or
+ releasing. 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 -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/ref/start_transaction.sgml
03pgproc/doc/src/sgml/ref/start_transaction.sgml
*** 00orig/doc/src/sgml/ref/start_transaction.sgml 2004-01-11 02:46:58.000000000
-0300
--- 03pgproc/doc/src/sgml/ref/start_transaction.sgml 2004-07-26 18:50:12.000000000
-0400
***************
*** 66,71 ****
--- 66,72 ----
<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 -Ncr --exclude-from=diff-ignore 00orig/doc/src/sgml/reference.sgml
03pgproc/doc/src/sgml/reference.sgml
*** 00orig/doc/src/sgml/reference.sgml 2004-06-26 00:28:44.000000000 -0400
--- 03pgproc/doc/src/sgml/reference.sgml 2004-07-26 19:13:02.000000000 -0400
***************
*** 120,128 ****
--- 120,131 ----
¬ify;
&prepare;
&reindex;
+ &releaseSavepoint;
&reset;
&revoke;
&rollback;
+ &rollbackTo;
+ &savepoint;
&select;
&selectInto;
&set;
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
