v1-0008-Transaction-chaining-support-in-PL-pgSQL.patch

From 06affcd2fcdc9d6feb86b1754fb86728b073eede Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Wed, 28 Feb 2018 10:04:06 -0500
Subject: [PATCH v1 8/8] Transaction chaining support in PL/pgSQL

---
 doc/src/sgml/plpgsql.sgml                          |  9 +++++++
 doc/src/sgml/spi.sgml                              | 14 +++++++---
 src/backend/access/transam/xact.c                  |  4 +--
 src/backend/executor/spi.c                         | 25 +++++++++++++++--
 src/include/access/xact.h                          |  2 ++
 src/include/executor/spi.h                         |  4 +--
 src/pl/plperl/plperl.c                             |  4 +--
 .../plpgsql/src/expected/plpgsql_transaction.out   | 31 ++++++++++++++++++++++
 src/pl/plpgsql/src/pl_exec.c                       | 10 ++++---
 src/pl/plpgsql/src/pl_funcs.c                      | 10 +++++--
 src/pl/plpgsql/src/pl_gram.y                       | 18 +++++++++++--
 src/pl/plpgsql/src/pl_scanner.c                    |  2 ++
 src/pl/plpgsql/src/plpgsql.h                       |  2 ++
 src/pl/plpgsql/src/sql/plpgsql_transaction.sql     | 23 ++++++++++++++++
 src/pl/plpython/plpy_plpymodule.c                  |  4 +--
 src/pl/tcl/pltcl.c                                 |  4 +--
 16 files changed, 142 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index c1e3c6a19d..539f5bc4b2 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3479,6 +3479,15 @@ <title>Transaction Management</title>
 </programlisting>
    </para>
 
+   <para>
+    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>
     A transaction cannot be ended inside a loop over a query result, nor
     inside a block with exception handlers.
diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml
index 0bac342322..f99a34181c 100644
--- a/doc/src/sgml/spi.sgml
+++ b/doc/src/sgml/spi.sgml
@@ -4396,7 +4396,7 @@ <title>Transaction Management</title>
 
  <refsynopsisdiv>
 <synopsis>
-void SPI_commit(void)
+void SPI_commit(bool <parameter>chain</parameter>)
 </synopsis>
  </refsynopsisdiv>
 
@@ -4409,7 +4409,10 @@ <title>Description</title>
    command <command>COMMIT</command>.  After a transaction is committed, a new
    transaction has to be started
    using <function>SPI_start_transaction</function> before further database
-   actions can be executed.
+   actions can be executed.  If <parameter>chain</parameter> is true, then 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>
@@ -4436,7 +4439,7 @@ <title>Description</title>
 
  <refsynopsisdiv>
 <synopsis>
-void SPI_rollback(void)
+void SPI_rollback(bool <parameter>chain</parameter>)
 </synopsis>
  </refsynopsisdiv>
 
@@ -4449,7 +4452,10 @@ <title>Description</title>
    command <command>ROLLBACK</command>.  After a transaction is rolled back, a
    new transaction has to be started
    using <function>SPI_start_transaction</function> before further database
-   actions can be executed.
+   actions can be executed.  If <parameter>chain</parameter> is true, then 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>
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 3e468f4d38..66db22c8f9 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2753,7 +2753,7 @@ static int	save_XactIsoLevel;
 static bool	save_XactReadOnly;
 static bool	save_XactDeferrable;
 
-static void
+void
 SaveTransactionCharacteristics(void)
 {
 	save_XactIsoLevel = XactIsoLevel;
@@ -2761,7 +2761,7 @@ SaveTransactionCharacteristics(void)
 	save_XactDeferrable = XactDeferrable;
 }
 
-static void
+void
 RestoreTransactionCharacteristics(void)
 {
 	XactIsoLevel = save_XactIsoLevel;
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 9fc4431b80..8bedd98b68 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -203,7 +203,7 @@ SPI_start_transaction(void)
 }
 
 void
-SPI_commit(void)
+SPI_commit(bool chain)
 {
 	MemoryContext oldcontext = CurrentMemoryContext;
 
@@ -230,14 +230,25 @@ SPI_commit(void)
 
 	if (ActiveSnapshotSet())
 		PopActiveSnapshot();
+
+	if (chain)
+		SaveTransactionCharacteristics();
+
 	CommitTransactionCommand();
+
+	if (chain)
+	{
+		StartTransactionCommand();
+		RestoreTransactionCharacteristics();
+	}
+
 	MemoryContextSwitchTo(oldcontext);
 
 	_SPI_current->internal_xact = false;
 }
 
 void
-SPI_rollback(void)
+SPI_rollback(bool chain)
 {
 	MemoryContext oldcontext = CurrentMemoryContext;
 
@@ -254,7 +265,17 @@ SPI_rollback(void)
 
 	_SPI_current->internal_xact = true;
 
+	if (chain)
+		SaveTransactionCharacteristics();
+
 	AbortCurrentTransaction();
+
+	if (chain)
+	{
+		StartTransactionCommand();
+		RestoreTransactionCharacteristics();
+	}
+
 	MemoryContextSwitchTo(oldcontext);
 
 	_SPI_current->internal_xact = false;
diff --git a/src/include/access/xact.h b/src/include/access/xact.h
index 12db48c6b8..176314cae6 100644
--- a/src/include/access/xact.h
+++ b/src/include/access/xact.h
@@ -346,6 +346,8 @@ extern bool TransactionIdIsCurrentTransactionId(TransactionId xid);
 extern void CommandCounterIncrement(void);
 extern void ForceSyncCommit(void);
 extern void StartTransactionCommand(void);
+extern void SaveTransactionCharacteristics(void);
+extern void RestoreTransactionCharacteristics(void);
 extern void CommitTransactionCommand(void);
 extern void AbortCurrentTransaction(void);
 extern void BeginTransactionBlock(void);
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
index e5bdaecc4e..e68310e0cc 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -160,8 +160,8 @@ extern int	SPI_unregister_relation(const char *name);
 extern int	SPI_register_trigger_data(TriggerData *tdata);
 
 extern void SPI_start_transaction(void);
-extern void SPI_commit(void);
-extern void SPI_rollback(void);
+extern void SPI_commit(bool chain);
+extern void SPI_rollback(bool chain);
 
 extern void AtEOXact_SPI(bool isCommit);
 extern void AtEOSubXact_SPI(bool isCommit, SubTransactionId mySubid);
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 77c41b2821..3471800090 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -3970,7 +3970,7 @@ plperl_spi_commit(void)
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("cannot commit transaction while a cursor is open")));
 
-		SPI_commit();
+		SPI_commit(false);
 		SPI_start_transaction();
 	}
 	PG_CATCH();
@@ -4000,7 +4000,7 @@ plperl_spi_rollback(void)
 					(errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
 					 errmsg("cannot abort transaction while a cursor is open")));
 
-		SPI_rollback();
+		SPI_rollback(false);
 		SPI_start_transaction();
 	}
 	PG_CATCH();
diff --git a/src/pl/plpgsql/src/expected/plpgsql_transaction.out b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
index 5f569dc64a..b006b58cf3 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
@@ -256,6 +256,37 @@ $$;
 INFO:  read committed
 INFO:  repeatable read
 INFO:  read committed
+-- transaction chain
+TRUNCATE test1;
+DO LANGUAGE plpgsql $$
+BEGIN
+    ROLLBACK;
+    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+    FOR i IN 0..5 LOOP
+        RAISE INFO 'transaction_isolation = %', current_setting('transaction_isolation');
+        INSERT INTO test1 (a) VALUES (i);
+        IF i % 2 = 0 THEN
+            COMMIT AND CHAIN;
+        ELSE
+            ROLLBACK AND CHAIN;
+        END IF;
+    END LOOP;
+END
+$$;
+INFO:  transaction_isolation = repeatable read
+INFO:  transaction_isolation = repeatable read
+INFO:  transaction_isolation = repeatable read
+INFO:  transaction_isolation = repeatable read
+INFO:  transaction_isolation = repeatable read
+INFO:  transaction_isolation = repeatable read
+SELECT * FROM test1;
+ a | b 
+---+---
+ 0 | 
+ 2 | 
+ 4 | 
+(3 rows)
+
 DROP TABLE test1;
 DROP TABLE test2;
 DROP TABLE test3;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 9a25ee9ad9..73540c9f7f 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4538,8 +4538,9 @@ exec_stmt_commit(PLpgSQL_execstate *estate, PLpgSQL_stmt_commit *stmt)
 				(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				 errmsg("committing inside a cursor loop is not supported")));
 
-	SPI_commit();
-	SPI_start_transaction();
+	SPI_commit(stmt->chain);
+	if (!stmt->chain)
+		SPI_start_transaction();
 
 	estate->simple_eval_estate = NULL;
 	plpgsql_create_econtext(estate);
@@ -4564,8 +4565,9 @@ exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt)
 				(errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
 				 errmsg("cannot abort transaction inside a cursor loop")));
 
-	SPI_rollback();
-	SPI_start_transaction();
+	SPI_rollback(stmt->chain);
+	if (!stmt->chain)
+		SPI_start_transaction();
 
 	estate->simple_eval_estate = NULL;
 	plpgsql_create_econtext(estate);
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 9acee818cc..37859c08c0 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -1295,14 +1295,20 @@ static void
 dump_commit(PLpgSQL_stmt_commit *stmt)
 {
 	dump_ind();
-	printf("COMMIT\n");
+	if (stmt->chain)
+		printf("COMMIT AND CHAIN\n");
+	else
+		printf("COMMIT\n");
 }
 
 static void
 dump_rollback(PLpgSQL_stmt_rollback *stmt)
 {
 	dump_ind();
-	printf("ROLLBACK\n");
+	if (stmt->chain)
+		printf("ROLLBACK AND CHAIN\n");
+	else
+		printf("ROLLBACK\n");
 }
 
 static void
diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y
index 4734c80766..ad69f2391e 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -218,6 +218,8 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %type <ival>	opt_scrollable
 %type <fetch>	opt_fetch_direction
 
+%type <ival>	opt_transaction_chain
+
 %type <keyword>	unreserved_keyword
 
 
@@ -251,12 +253,14 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token <keyword>	K_ABSOLUTE
 %token <keyword>	K_ALIAS
 %token <keyword>	K_ALL
+%token <keyword>	K_AND
 %token <keyword>	K_ARRAY
 %token <keyword>	K_ASSERT
 %token <keyword>	K_BACKWARD
 %token <keyword>	K_BEGIN
 %token <keyword>	K_BY
 %token <keyword>	K_CASE
+%token <keyword>	K_CHAIN
 %token <keyword>	K_CLOSE
 %token <keyword>	K_COLLATE
 %token <keyword>	K_COLUMN
@@ -2147,30 +2151,38 @@ stmt_null		: K_NULL ';'
 					}
 				;
 
-stmt_commit		: K_COMMIT ';'
+stmt_commit		: K_COMMIT opt_transaction_chain ';'
 					{
 						PLpgSQL_stmt_commit *new;
 
 						new = palloc(sizeof(PLpgSQL_stmt_commit));
 						new->cmd_type = PLPGSQL_STMT_COMMIT;
 						new->lineno = plpgsql_location_to_lineno(@1);
+						new->chain = $2;
 
 						$$ = (PLpgSQL_stmt *)new;
 					}
 				;
 
-stmt_rollback	: K_ROLLBACK ';'
+stmt_rollback	: K_ROLLBACK opt_transaction_chain ';'
 					{
 						PLpgSQL_stmt_rollback *new;
 
 						new = palloc(sizeof(PLpgSQL_stmt_rollback));
 						new->cmd_type = PLPGSQL_STMT_ROLLBACK;
 						new->lineno = plpgsql_location_to_lineno(@1);
+						new->chain = $2;
 
 						$$ = (PLpgSQL_stmt *)new;
 					}
 				;
 
+opt_transaction_chain:
+			K_AND K_CHAIN			{ $$ = true; }
+			| K_AND K_NO K_CHAIN	{ $$ = false; }
+			| /* EMPTY */			{ $$ = false; }
+				;
+
 stmt_set	: K_SET
 					{
 						PLpgSQL_stmt_set *new;
@@ -2425,9 +2437,11 @@ any_identifier	: T_WORD
 unreserved_keyword	:
 				K_ABSOLUTE
 				| K_ALIAS
+				| K_AND
 				| K_ARRAY
 				| K_ASSERT
 				| K_BACKWARD
+				| K_CHAIN
 				| K_CLOSE
 				| K_COLLATE
 				| K_COLUMN
diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c
index ed8933f69a..a2cf56bed0 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -99,9 +99,11 @@ static const int num_reserved_keywords = lengthof(reserved_keywords);
 static const ScanKeyword unreserved_keywords[] = {
 	PG_KEYWORD("absolute", K_ABSOLUTE, UNRESERVED_KEYWORD)
 	PG_KEYWORD("alias", K_ALIAS, UNRESERVED_KEYWORD)
+	PG_KEYWORD("and", K_AND, UNRESERVED_KEYWORD)
 	PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD)
 	PG_KEYWORD("assert", K_ASSERT, UNRESERVED_KEYWORD)
 	PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD)
+	PG_KEYWORD("chain", K_CHAIN, UNRESERVED_KEYWORD)
 	PG_KEYWORD("close", K_CLOSE, UNRESERVED_KEYWORD)
 	PG_KEYWORD("collate", K_COLLATE, UNRESERVED_KEYWORD)
 	PG_KEYWORD("column", K_COLUMN, UNRESERVED_KEYWORD)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index bece089529..6f43e1d4f0 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -516,6 +516,7 @@ typedef struct PLpgSQL_stmt_commit
 {
 	PLpgSQL_stmt_type cmd_type;
 	int			lineno;
+	bool		chain;
 } PLpgSQL_stmt_commit;
 
 /*
@@ -525,6 +526,7 @@ typedef struct PLpgSQL_stmt_rollback
 {
 	PLpgSQL_stmt_type cmd_type;
 	int			lineno;
+	bool		chain;
 } PLpgSQL_stmt_rollback;
 
 /*
diff --git a/src/pl/plpgsql/src/sql/plpgsql_transaction.sql b/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
index d2a2efd661..3a8f4629da 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
@@ -229,6 +229,29 @@ CREATE TABLE test2 (x int);
 $$;
 
 
+-- transaction chain
+
+TRUNCATE test1;
+
+DO LANGUAGE plpgsql $$
+BEGIN
+    ROLLBACK;
+    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+    FOR i IN 0..5 LOOP
+        RAISE INFO 'transaction_isolation = %', current_setting('transaction_isolation');
+        INSERT INTO test1 (a) VALUES (i);
+        IF i % 2 = 0 THEN
+            COMMIT AND CHAIN;
+        ELSE
+            ROLLBACK AND CHAIN;
+        END IF;
+    END LOOP;
+END
+$$;
+
+SELECT * FROM test1;
+
+
 DROP TABLE test1;
 DROP TABLE test2;
 DROP TABLE test3;
diff --git a/src/pl/plpython/plpy_plpymodule.c b/src/pl/plpython/plpy_plpymodule.c
index 3d7dd13f0c..88f103275c 100644
--- a/src/pl/plpython/plpy_plpymodule.c
+++ b/src/pl/plpython/plpy_plpymodule.c
@@ -599,7 +599,7 @@ PLy_commit(PyObject *self, PyObject *args)
 			   (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 				errmsg("cannot commit transaction while a cursor is open")));
 
-	SPI_commit();
+	SPI_commit(false);
 	SPI_start_transaction();
 
 	/* was cleared at transaction end, reset pointer */
@@ -618,7 +618,7 @@ PLy_rollback(PyObject *self, PyObject *args)
 			   (errcode(ERRCODE_INVALID_TRANSACTION_TERMINATION),
 				errmsg("cannot abort transaction while a cursor is open")));
 
-	SPI_rollback();
+	SPI_rollback(false);
 	SPI_start_transaction();
 
 	/* was cleared at transaction end, reset pointer */
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index 5df4dfdf55..e548450808 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -2962,7 +2962,7 @@ pltcl_commit(ClientData cdata, Tcl_Interp *interp,
 
 	PG_TRY();
 	{
-		SPI_commit();
+		SPI_commit(false);
 		SPI_start_transaction();
 	}
 	PG_CATCH();
@@ -3002,7 +3002,7 @@ pltcl_rollback(ClientData cdata, Tcl_Interp *interp,
 
 	PG_TRY();
 	{
-		SPI_rollback();
+		SPI_rollback(false);
 		SPI_start_transaction();
 	}
 	PG_CATCH();
-- 
2.16.2