Transaction chaining

Add command variants COMMIT AND CHAIN and ROLLBACK AND CHAIN, which
start new transactions with the same transaction characteristics as the
just finished one, per SQL standard.

Support for transaction chaining in PL/pgSQL is also added.  This
functionality is especially useful when running COMMIT in a loop in
PL/pgSQL.

Reviewed-by: Fabien COELHO <coelho@cri.ensmp.fr>
Discussion: https://www.postgresql.org/message-id/flat/28536681-324b-10dc-ade8-ab46f7645a5a@2ndquadrant.com

6 files changed, 105 insertions, 12 deletions

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index f8c6435c50e..50f1884afec 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3490,6 +3490,20 @@ CALL transaction_test1();
 </programlisting>
    </para>
 
+   <indexterm zone="plpgsql-transaction-chain">
+    <primary>chained transactions</primary>
+    <secondary>in PL/pgSQL</secondary>
+   </indexterm>
+
+   <para id="plpgsql-transaction-chain">
+    A new transaction starts out with default transaction characteristics such
+    as transaction isolation level.  In cases where transactions are committed
+    in a loop, it might be desirable to start new transactions automatically
+    with the same characteristics as the previous one.  The commands
+    <command>COMMIT AND CHAIN</command> and <command>ROLLBACK AND
+    CHAIN</command> accomplish this.
+   </para>
+
    <para>
     Transaction control is only possible in <command>CALL</command> or
     <command>DO</command> invocations from the top level or nested
diff --git a/doc/src/sgml/ref/abort.sgml b/doc/src/sgml/ref/abort.sgml
index 21799d2a83f..03729133651 100644
--- a/doc/src/sgml/ref/abort.sgml
+++ b/doc/src/sgml/ref/abort.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-ABORT [ WORK | TRANSACTION ]
+ABORT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -51,6 +51,18 @@ ABORT [ WORK | TRANSACTION ]
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><literal>AND CHAIN</literal></term>
+    <listitem>
+     <para>
+      If <literal>AND CHAIN</literal> is specified, a new transaction is
+      immediately started with the same transaction characteristics (see <xref
+      linkend="sql-set-transaction"/>) as the just finished one.  Otherwise,
+      no new transaction is started.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/commit.sgml b/doc/src/sgml/ref/commit.sgml
index b2e8d5d1807..e4169cd2c62 100644
--- a/doc/src/sgml/ref/commit.sgml
+++ b/doc/src/sgml/ref/commit.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-COMMIT [ WORK | TRANSACTION ]
+COMMIT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -38,6 +38,10 @@ COMMIT [ WORK | TRANSACTION ]
  <refsect1>
   <title>Parameters</title>
 
+  <indexterm zone="sql-commit-chain">
+   <primary>chained transactions</primary>
+  </indexterm>
+
   <variablelist>
    <varlistentry>
     <term><literal>WORK</literal></term>
@@ -48,6 +52,18 @@ COMMIT [ WORK | TRANSACTION ]
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry id="sql-commit-chain">
+    <term><literal>AND CHAIN</literal></term>
+    <listitem>
+     <para>
+      If <literal>AND CHAIN</literal> is specified, a new transaction is
+      immediately started with the same transaction characteristics (see <xref
+      linkend="sql-set-transaction"/>) as the just finished one.  Otherwise,
+      no new transaction is started.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
@@ -79,9 +95,8 @@ COMMIT;
   <title>Compatibility</title>
 
   <para>
-   The SQL standard only specifies the two forms
-   <literal>COMMIT</literal> and <literal>COMMIT
-   WORK</literal>. Otherwise, this command is fully conforming.
+   The command <command>COMMIT</command> conforms to the SQL standard.  The
+   form <literal>COMMIT TRANSACTION</literal> is a PostgreSQL extension.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/end.sgml b/doc/src/sgml/ref/end.sgml
index 7523315f344..8b8f4f0dbb9 100644
--- a/doc/src/sgml/ref/end.sgml
+++ b/doc/src/sgml/ref/end.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-END [ WORK | TRANSACTION ]
+END [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -50,6 +50,18 @@ END [ WORK | TRANSACTION ]
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><literal>AND CHAIN</literal></term>
+    <listitem>
+     <para>
+      If <literal>AND CHAIN</literal> is specified, a new transaction is
+      immediately started with the same transaction characteristics (see <xref
+      linkend="sql-set-transaction"/>) as the just finished one.  Otherwise,
+      no new transaction is started.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
diff --git a/doc/src/sgml/ref/rollback.sgml b/doc/src/sgml/ref/rollback.sgml
index 3cafb848a9a..a5bbf25221c 100644
--- a/doc/src/sgml/ref/rollback.sgml
+++ b/doc/src/sgml/ref/rollback.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
 
  <refsynopsisdiv>
 <synopsis>
-ROLLBACK [ WORK | TRANSACTION ]
+ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -37,6 +37,10 @@ ROLLBACK [ WORK | TRANSACTION ]
  <refsect1>
   <title>Parameters</title>
 
+  <indexterm zone="sql-rollback-chain">
+   <primary>chained transactions</primary>
+  </indexterm>
+
   <variablelist>
    <varlistentry>
     <term><literal>WORK</literal></term>
@@ -47,6 +51,18 @@ ROLLBACK [ WORK | TRANSACTION ]
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry id="sql-rollback-chain">
+    <term><literal>AND CHAIN</literal></term>
+    <listitem>
+     <para>
+      If <literal>AND CHAIN</literal> is specified, a new transaction is
+      immediately started with the same transaction characteristics (see <xref
+      linkend="sql-set-transaction"/>) as the just finished one.  Otherwise,
+      no new transaction is started.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
  </refsect1>
 
@@ -78,9 +94,8 @@ ROLLBACK;
   <title>Compatibility</title>
 
   <para>
-   The SQL standard only specifies the two forms
-   <literal>ROLLBACK</literal> and <literal>ROLLBACK
-   WORK</literal>. Otherwise, this command is fully conforming.
+   The command <command>ROLLBACK</command> conforms to the SQL standard.  The
+   form <literal>ROLLBACK TRANSACTION</literal> is a PostgreSQL extension.
   </para>
  </refsect1>
 
diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml
index 6f4f3bae6ff..9b2f5169421 100644
--- a/doc/src/sgml/spi.sgml
+++ b/doc/src/sgml/spi.sgml
@@ -4376,6 +4376,7 @@ int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
 
 <refentry id="spi-spi-commit">
  <indexterm><primary>SPI_commit</primary></indexterm>
+ <indexterm><primary>SPI_commit_and_chain</primary></indexterm>
 
  <refmeta>
   <refentrytitle>SPI_commit</refentrytitle>
@@ -4384,6 +4385,7 @@ int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
 
  <refnamediv>
   <refname>SPI_commit</refname>
+  <refname>SPI_commit_and_chain</refname>
   <refpurpose>commit the current transaction</refpurpose>
  </refnamediv>
 
@@ -4391,6 +4393,10 @@ int SPI_freeplan(SPIPlanPtr <parameter>plan</parameter>)
 <synopsis>
 void SPI_commit(void)
 </synopsis>
+
+<synopsis>
+void SPI_commit_and_chain(void)
+</synopsis>
  </refsynopsisdiv>
 
  <refsect1>
@@ -4406,7 +4412,14 @@ void SPI_commit(void)
   </para>
 
   <para>
-   This function can only be executed if the SPI connection has been set as
+   <function>SPI_commit_and_chain</function> is the same, but a new
+   transaction is immediately started with the same transaction
+   characteristics as the just finished one, like with the SQL command
+   <command>COMMIT AND CHAIN</command>.
+  </para>
+
+  <para>
+   These functions can only be executed if the SPI connection has been set as
    nonatomic in the call to <function>SPI_connect_ext</function>.
   </para>
  </refsect1>
@@ -4416,6 +4429,7 @@ void SPI_commit(void)
 
 <refentry id="spi-spi-rollback">
  <indexterm><primary>SPI_rollback</primary></indexterm>
+ <indexterm><primary>SPI_rollback_and_chain</primary></indexterm>
 
  <refmeta>
   <refentrytitle>SPI_rollback</refentrytitle>
@@ -4424,6 +4438,7 @@ void SPI_commit(void)
 
  <refnamediv>
   <refname>SPI_rollback</refname>
+  <refname>SPI_rollback_and_chain</refname>
   <refpurpose>abort the current transaction</refpurpose>
  </refnamediv>
 
@@ -4431,6 +4446,10 @@ void SPI_commit(void)
 <synopsis>
 void SPI_rollback(void)
 </synopsis>
+
+<synopsis>
+void SPI_rollback_and_chain(void)
+</synopsis>
  </refsynopsisdiv>
 
  <refsect1>
@@ -4444,9 +4463,15 @@ void SPI_rollback(void)
    using <function>SPI_start_transaction</function> before further database
    actions can be executed.
   </para>
+  <para>
+   <function>SPI_rollback_and_chain</function> is the same, but a new
+   transaction is immediately started with the same transaction
+   characteristics as the just finished one, like with the SQL command
+   <command>ROLLBACK AND CHAIN</command>.
+  </para>
 
   <para>
-   This function can only be executed if the SPI connection has been set as
+   These functions can only be executed if the SPI connection has been set as
    nonatomic in the call to <function>SPI_connect_ext</function>.
   </para>
  </refsect1>