[PATCH v2 2/2] Transaction chaining support in PL/pgSQL
Peter Eisentraut <peter_e@gmx.net>
From: Peter Eisentraut <peter_e@gmx.net>
To:
Date: 2018-02-28T15:04:06Z
Lists: pgsql-hackers
---
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 4344ceadbe..321953fd46 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3478,6 +3478,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 58565f1f72..b4aa8ab6e0 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -2760,7 +2760,7 @@ static int save_XactIsoLevel;
static bool save_XactReadOnly;
static bool save_XactDeferrable;
-static void
+void
SaveTransactionCharacteristics(void)
{
save_XactIsoLevel = XactIsoLevel;
@@ -2768,7 +2768,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 11ca800e4c..444a8765f7 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -223,7 +223,7 @@ SPI_start_transaction(void)
}
void
-SPI_commit(void)
+SPI_commit(bool chain)
{
MemoryContext oldcontext = CurrentMemoryContext;
@@ -255,14 +255,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;
@@ -279,7 +289,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 143a89a16c..a391e92405 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 SPICleanup(void);
extern void AtEOXact_SPI(bool isCommit);
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 4cfc506253..003fd594c6 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 77a83adab5..5441e997a6 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out
@@ -493,6 +493,37 @@ CALL transaction_test10b(10);
9
(1 row)
+-- 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 45526383f2..68567afb03 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4753,8 +4753,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);
@@ -4772,8 +4773,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 b93f866223..a2e9a8dcb8 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -1320,14 +1320,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 68e399f9cf..498c17c0fb 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
@@ -2182,30 +2186,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;
@@ -2460,10 +2472,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 fc4ba3054a..0be191f270 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 f6c35a5049..fad2ad0d87 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -534,6 +534,7 @@ typedef struct PLpgSQL_stmt_commit
{
PLpgSQL_stmt_type cmd_type;
int lineno;
+ bool chain;
} PLpgSQL_stmt_commit;
/*
@@ -543,6 +544,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 0ed9ab873a..e6a2d89ec7 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_transaction.sql
@@ -412,6 +412,29 @@ CREATE PROCEDURE transaction_test10b(INOUT x int)
CALL transaction_test10b(10);
+-- 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 e2fa43b890..7a317e1f89 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -2957,7 +2957,7 @@ pltcl_commit(ClientData cdata, Tcl_Interp *interp,
PG_TRY();
{
- SPI_commit();
+ SPI_commit(false);
SPI_start_transaction();
}
PG_CATCH();
@@ -2997,7 +2997,7 @@ pltcl_rollback(ClientData cdata, Tcl_Interp *interp,
PG_TRY();
{
- SPI_rollback();
+ SPI_rollback(false);
SPI_start_transaction();
}
PG_CATCH();
--
2.19.0
--------------10239C0B4F8C169BEEE37D84--