Thread

[PATCH v3 2/2] Transaction chaining support in PL/pgSQL

Peter Eisentraut <peter_e@gmx.net> — 2018-02-28T15:04:06Z

---
 doc/src/sgml/plpgsql.sgml                     |  9 ++++++
 doc/src/sgml/spi.sgml                         | 14 ++++++---
 src/backend/access/transam/xact.c             |  4 +--
 src/backend/executor/spi.c                    | 24 ++++++++++++--
 src/include/access/xact.h                     |  2 ++
 src/include/executor/spi.h                    |  4 +--
 src/pl/plperl/plperl.c                        |  4 +--
 .../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 ++
 .../plpgsql/src/sql/plpgsql_transaction.sql   | 23 ++++++++++++++
 src/pl/plpython/plpy_plpymodule.c             |  4 +--
 src/pl/tcl/pltcl.c                            |  4 +--
 16 files changed, 141 insertions(+), 24 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index b9eb98cfd7..128f13742a 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3490,6 +3490,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>
     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/spi.sgml b/doc/src/sgml/spi.sgml
index 9db11d22fb..9b4e63e352 100644
--- a/doc/src/sgml/spi.sgml
+++ b/doc/src/sgml/spi.sgml
@@ -4389,7 +4389,7 @@ <title>Transaction Management</title>
 
  <refsynopsisdiv>
 <synopsis>
-void SPI_commit(void)
+void SPI_commit(bool <parameter>chain</parameter>)
 </synopsis>
  </refsynopsisdiv>
 
@@ -4402,7 +4402,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>
@@ -4429,7 +4432,7 @@ <title>Description</title>
 
  <refsynopsisdiv>
 <synopsis>
-void SPI_rollback(void)
+void SPI_rollback(bool <parameter>chain</parameter>)
 </synopsis>
  </refsynopsisdiv>
 
@@ -4442,7 +4445,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 6af149d788..e8c2db3c2d 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2774,7 +2774,7 @@ static int	save_XactIsoLevel;
 static bool	save_XactReadOnly;
 static bool	save_XactDeferrable;
 
-static void
+void
 SaveTransactionCharacteristics(void)
 {
 	save_XactIsoLevel = XactIsoLevel;
@@ -2782,7 +2782,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 ad726676d8..d61a56e095 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -218,7 +218,7 @@ SPI_start_transaction(void)
 }
 
 void
-SPI_commit(void)
+SPI_commit(bool chain)
 {
 	MemoryContext oldcontext = CurrentMemoryContext;
 
@@ -250,14 +250,24 @@ SPI_commit(void)
 	while (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;
 
@@ -274,7 +284,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 c757146e4d..2abdbece6c 100644
--- a/src/include/access/xact.h
+++ b/src/include/access/xact.h
@@ -369,6 +369,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 d2616968ac..6bf2acb3b5 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -159,8 +159,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 SPICleanup(void);
 extern void AtEOXact_SPI(bool isCommit);
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index fe54b20903..93190d1099 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -3968,7 +3968,7 @@ plperl_spi_commit(void)
 	{
 		HoldPinnedPortals();
 
-		SPI_commit();
+		SPI_commit(false);
 		SPI_start_transaction();
 	}
 	PG_CATCH();
@@ -3995,7 +3995,7 @@ plperl_spi_rollback(void)
 	{
 		HoldPinnedPortals();
 
-		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 6eedb215a4..ab62437bde 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
@@ -523,6 +523,37 @@ BEGIN
 END;
 $$;
 CALL transaction_test11();
+-- 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 39ea925820..04f5ec603c 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4773,8 +4773,9 @@ exec_stmt_commit(PLpgSQL_execstate *estate, PLpgSQL_stmt_commit *stmt)
 {
 	HoldPinnedPortals();
 
-	SPI_commit();
-	SPI_start_transaction();
+	SPI_commit(stmt->chain);
+	if (!stmt->chain)
+		SPI_start_transaction();
 
 	estate->simple_eval_estate = NULL;
 	plpgsql_create_econtext(estate);
@@ -4792,8 +4793,9 @@ exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt)
 {
 	HoldPinnedPortals();
 
-	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 4266e6cee7..0b0f892e2d 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -1318,14 +1318,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 a979a5109d..924fbeb263 100644
--- a/src/pl/plpgsql/src/pl_gram.y
+++ b/src/pl/plpgsql/src/pl_gram.y
@@ -219,6 +219,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
 
 
@@ -252,6 +254,7 @@ 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
@@ -259,6 +262,7 @@ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
 %token <keyword>	K_BY
 %token <keyword>	K_CALL
 %token <keyword>	K_CASE
+%token <keyword>	K_CHAIN
 %token <keyword>	K_CLOSE
 %token <keyword>	K_COLLATE
 %token <keyword>	K_COLUMN
@@ -2177,30 +2181,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;
@@ -2455,10 +2467,12 @@ any_identifier	: T_WORD
 unreserved_keyword	:
 				K_ABSOLUTE
 				| K_ALIAS
+				| K_AND
 				| K_ARRAY
 				| K_ASSERT
 				| K_BACKWARD
 				| K_CALL
+				| 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 ab18946847..d4000bcaaa 100644
--- a/src/pl/plpgsql/src/pl_scanner.c
+++ b/src/pl/plpgsql/src/pl_scanner.c
@@ -99,10 +99,12 @@ 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("call", K_CALL, 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 42177ccaa6..cac14e87d8 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -533,6 +533,7 @@ typedef struct PLpgSQL_stmt_commit
 {
 	PLpgSQL_stmt_type cmd_type;
 	int			lineno;
+	bool		chain;
 } PLpgSQL_stmt_commit;
 
 /*
@@ -542,6 +543,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 ac1361a8ce..304bb3ffa8 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
@@ -445,6 +445,29 @@ CREATE PROCEDURE transaction_test11()
 CALL transaction_test11();
 
 
+-- 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 23e49e4b75..729f402ea1 100644
--- a/src/pl/plpython/plpy_plpymodule.c
+++ b/src/pl/plpython/plpy_plpymodule.c
@@ -590,7 +590,7 @@ PLy_commit(PyObject *self, PyObject *args)
 
 	HoldPinnedPortals();
 
-	SPI_commit();
+	SPI_commit(false);
 	SPI_start_transaction();
 
 	/* was cleared at transaction end, reset pointer */
@@ -606,7 +606,7 @@ PLy_rollback(PyObject *self, PyObject *args)
 
 	HoldPinnedPortals();
 
-	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 3b1454f833..27fa0fa53a 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -2931,7 +2931,7 @@ pltcl_commit(ClientData cdata, Tcl_Interp *interp,
 
 	PG_TRY();
 	{
-		SPI_commit();
+		SPI_commit(false);
 		SPI_start_transaction();
 	}
 	PG_CATCH();
@@ -2971,7 +2971,7 @@ pltcl_rollback(ClientData cdata, Tcl_Interp *interp,
 
 	PG_TRY();
 	{
-		SPI_rollback();
+		SPI_rollback(false);
 		SPI_start_transaction();
 	}
 	PG_CATCH();
-- 
2.19.1


--------------5FF59F88931798579DEB4489--