On 02/10/2018 07:38, Michael Paquier wrote:
> The patch set does not apply anymore, so this patch is moved to next CF,
> waiting on author.
Attached is a rebased patch set. No functionality changes.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From e964e279827635feb95df7d6518a6ba9fa55a4df Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Fri, 16 Feb 2018 21:37:55 -0500
Subject: [PATCH v2 1/2] 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.
---
doc/src/sgml/ref/abort.sgml | 14 ++-
doc/src/sgml/ref/commit.sgml | 14 ++-
doc/src/sgml/ref/end.sgml | 14 ++-
doc/src/sgml/ref/rollback.sgml | 14 ++-
src/backend/access/transam/xact.c | 73 ++++++++++-
src/backend/catalog/sql_features.txt | 2 +-
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/parser/gram.y | 19 ++-
src/backend/tcop/utility.c | 4 +-
src/include/access/xact.h | 4 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/transactions.out | 139 +++++++++++++++++++++
src/test/regress/sql/transactions.sql | 49 ++++++++
14 files changed, 333 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/ref/abort.sgml b/doc/src/sgml/ref/abort.sgml
index 21799d2a83..0372913365 100644
--- a/doc/src/sgml/ref/abort.sgml
+++ b/doc/src/sgml/ref/abort.sgml
@@ -21,7 +21,7 @@
<refsynopsisdiv>
<synopsis>
-ABORT [ WORK | TRANSACTION ]
+ABORT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
</synopsis>
</refsynopsisdiv>
@@ -51,6 +51,18 @@ <title>Parameters</title>
</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 b2e8d5d180..37c706a66f 100644
--- a/doc/src/sgml/ref/commit.sgml
+++ b/doc/src/sgml/ref/commit.sgml
@@ -21,7 +21,7 @@
<refsynopsisdiv>
<synopsis>
-COMMIT [ WORK | TRANSACTION ]
+COMMIT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
</synopsis>
</refsynopsisdiv>
@@ -48,6 +48,18 @@ <title>Parameters</title>
</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/end.sgml b/doc/src/sgml/ref/end.sgml
index 7523315f34..8b8f4f0dbb 100644
--- a/doc/src/sgml/ref/end.sgml
+++ b/doc/src/sgml/ref/end.sgml
@@ -21,7 +21,7 @@
<refsynopsisdiv>
<synopsis>
-END [ WORK | TRANSACTION ]
+END [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
</synopsis>
</refsynopsisdiv>
@@ -50,6 +50,18 @@ <title>Parameters</title>
</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 3cafb848a9..3019273a47 100644
--- a/doc/src/sgml/ref/rollback.sgml
+++ b/doc/src/sgml/ref/rollback.sgml
@@ -21,7 +21,7 @@
<refsynopsisdiv>
<synopsis>
-ROLLBACK [ WORK | TRANSACTION ]
+ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
</synopsis>
</refsynopsisdiv>
@@ -47,6 +47,18 @@ <title>Parameters</title>
</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/src/backend/access/transam/xact.c
b/src/backend/access/transam/xact.c
index e3f668bf6a..58565f1f72 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -188,6 +188,7 @@ typedef struct TransactionStateData
bool startedInRecovery; /* did we start in recovery? */
bool didLogXid; /* has xid been included in WAL
record? */
int parallelModeLevel; /*
Enter/ExitParallelMode counter */
+ bool chain; /* start a new block after this
one */
struct TransactionStateData *parent; /* back link to parent */
} TransactionStateData;
@@ -2746,6 +2747,36 @@ StartTransactionCommand(void)
MemoryContextSwitchTo(CurTransactionContext);
}
+
+/*
+ * Simple system for saving and restoring transaction characteristics
+ * (isolation level, read only, deferrable). We need this for transaction
+ * chaining, so that we can set the characteristics of the new transaction to
+ * be the same as the previous one. (We need something like this because the
+ * GUC system resets the characteristics at transaction end, so for example
+ * just skipping the reset in StartTransaction() won't work.)
+ */
+static int save_XactIsoLevel;
+static bool save_XactReadOnly;
+static bool save_XactDeferrable;
+
+static void
+SaveTransactionCharacteristics(void)
+{
+ save_XactIsoLevel = XactIsoLevel;
+ save_XactReadOnly = XactReadOnly;
+ save_XactDeferrable = XactDeferrable;
+}
+
+static void
+RestoreTransactionCharacteristics(void)
+{
+ XactIsoLevel = save_XactIsoLevel;
+ XactReadOnly = save_XactReadOnly;
+ XactDeferrable = save_XactDeferrable;
+}
+
+
/*
* CommitTransactionCommand
*/
@@ -2754,6 +2785,9 @@ CommitTransactionCommand(void)
{
TransactionState s = CurrentTransactionState;
+ if (s->chain)
+ SaveTransactionCharacteristics();
+
switch (s->blockState)
{
/*
@@ -2805,6 +2839,13 @@ CommitTransactionCommand(void)
case TBLOCK_END:
CommitTransaction();
s->blockState = TBLOCK_DEFAULT;
+ if (s->chain)
+ {
+ StartTransaction();
+ s->blockState = TBLOCK_INPROGRESS;
+ s->chain = false;
+ RestoreTransactionCharacteristics();
+ }
break;
/*
@@ -2824,6 +2865,13 @@ CommitTransactionCommand(void)
case TBLOCK_ABORT_END:
CleanupTransaction();
s->blockState = TBLOCK_DEFAULT;
+ if (s->chain)
+ {
+ StartTransaction();
+ s->blockState = TBLOCK_INPROGRESS;
+ s->chain = false;
+ RestoreTransactionCharacteristics();
+ }
break;
/*
@@ -2835,6 +2883,13 @@ CommitTransactionCommand(void)
AbortTransaction();
CleanupTransaction();
s->blockState = TBLOCK_DEFAULT;
+ if (s->chain)
+ {
+ StartTransaction();
+ s->blockState = TBLOCK_INPROGRESS;
+ s->chain = false;
+ RestoreTransactionCharacteristics();
+ }
break;
/*
@@ -3492,7 +3547,7 @@ PrepareTransactionBlock(const char *gid)
bool result;
/* Set up to commit the current transaction */
- result = EndTransactionBlock();
+ result = EndTransactionBlock(false);
/* If successful, change outer tblock state to PREPARE */
if (result)
@@ -3538,7 +3593,7 @@ PrepareTransactionBlock(const char *gid)
* resource owner, etc while executing inside a Portal.
*/
bool
-EndTransactionBlock(void)
+EndTransactionBlock(bool chain)
{
TransactionState s = CurrentTransactionState;
bool result = false;
@@ -3664,6 +3719,13 @@ EndTransactionBlock(void)
break;
}
+ Assert(s->blockState == TBLOCK_STARTED ||
+ s->blockState == TBLOCK_END ||
+ s->blockState == TBLOCK_ABORT_END ||
+ s->blockState == TBLOCK_ABORT_PENDING);
+
+ s->chain = chain;
+
return result;
}
@@ -3674,7 +3736,7 @@ EndTransactionBlock(void)
* As above, we don't actually do anything here except change blockState.
*/
void
-UserAbortTransactionBlock(void)
+UserAbortTransactionBlock(bool chain)
{
TransactionState s = CurrentTransactionState;
@@ -3772,6 +3834,11 @@ UserAbortTransactionBlock(void)
BlockStateAsString(s->blockState));
break;
}
+
+ Assert(s->blockState == TBLOCK_ABORT_END ||
+ s->blockState == TBLOCK_ABORT_PENDING);
+
+ s->chain = chain;
}
/*
diff --git a/src/backend/catalog/sql_features.txt
b/src/backend/catalog/sql_features.txt
index aeb262a5b0..49df7e08c0 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -443,7 +443,7 @@ T213 INSTEAD OF triggers YES
T231 Sensitive cursors YES
T241 START TRANSACTION statement YES
T251 SET TRANSACTION statement: LOCAL option NO
-T261 Chained transactions NO
+T261 Chained transactions YES
T271 Savepoints YES
T272 Enhanced savepoint management NO
T281 SELECT privilege with column granularity YES
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index e47641d572..f78aa375fc 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3655,6 +3655,7 @@ _copyTransactionStmt(const TransactionStmt *from)
COPY_NODE_FIELD(options);
COPY_STRING_FIELD(savepoint_name);
COPY_STRING_FIELD(gid);
+ COPY_SCALAR_FIELD(chain);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 3bb91c9595..8c6162c67d 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1506,6 +1506,7 @@ _equalTransactionStmt(const TransactionStmt *a, const
TransactionStmt *b)
COMPARE_NODE_FIELD(options);
COMPARE_STRING_FIELD(savepoint_name);
COMPARE_STRING_FIELD(gid);
+ COMPARE_SCALAR_FIELD(chain);
return true;
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 6d23bfb0b3..bc42b3400f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -310,6 +310,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
%type <boolean> opt_or_replace
opt_grant_grant_option opt_grant_admin_option
opt_nowait opt_if_exists opt_with_data
+ opt_transaction_chain
%type <ival> opt_nowait_or_skip
%type <list> OptRoleList AlterOptRoleList
@@ -9832,11 +9833,12 @@ UnlistenStmt:
*****************************************************************************/
TransactionStmt:
- ABORT_P opt_transaction
+ ABORT_P opt_transaction opt_transaction_chain
{
TransactionStmt *n =
makeNode(TransactionStmt);
n->kind = TRANS_STMT_ROLLBACK;
n->options = NIL;
+ n->chain = $3;
$$ = (Node *)n;
}
| BEGIN_P opt_transaction transaction_mode_list_or_empty
@@ -9853,25 +9855,28 @@ TransactionStmt:
n->options = $3;
$$ = (Node *)n;
}
- | COMMIT opt_transaction
+ | COMMIT opt_transaction opt_transaction_chain
{
TransactionStmt *n =
makeNode(TransactionStmt);
n->kind = TRANS_STMT_COMMIT;
n->options = NIL;
+ n->chain = $3;
$$ = (Node *)n;
}
- | END_P opt_transaction
+ | END_P opt_transaction opt_transaction_chain
{
TransactionStmt *n =
makeNode(TransactionStmt);
n->kind = TRANS_STMT_COMMIT;
n->options = NIL;
+ n->chain = $3;
$$ = (Node *)n;
}
- | ROLLBACK opt_transaction
+ | ROLLBACK opt_transaction opt_transaction_chain
{
TransactionStmt *n =
makeNode(TransactionStmt);
n->kind = TRANS_STMT_ROLLBACK;
n->options = NIL;
+ n->chain = $3;
$$ = (Node *)n;
}
| SAVEPOINT ColId
@@ -9971,6 +9976,12 @@ transaction_mode_list_or_empty:
{ $$ = NIL; }
;
+opt_transaction_chain:
+ AND CHAIN { $$ = true; }
+ | AND NO CHAIN { $$ = false; }
+ | /* EMPTY */ { $$ = false; }
+ ;
+
/*****************************************************************************
*
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index b5804f64ad..4c3ac2689b 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -437,7 +437,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case TRANS_STMT_COMMIT:
- if (!EndTransactionBlock())
+ if
(!EndTransactionBlock(stmt->chain))
{
/* report unsuccessful
commit in completionTag */
if (completionTag)
@@ -468,7 +468,7 @@ standard_ProcessUtility(PlannedStmt *pstmt,
break;
case TRANS_STMT_ROLLBACK:
- UserAbortTransactionBlock();
+
UserAbortTransactionBlock(stmt->chain);
break;
case TRANS_STMT_SAVEPOINT:
diff --git a/src/include/access/xact.h b/src/include/access/xact.h
index 689c57c592..c757146e4d 100644
--- a/src/include/access/xact.h
+++ b/src/include/access/xact.h
@@ -372,9 +372,9 @@ extern void StartTransactionCommand(void);
extern void CommitTransactionCommand(void);
extern void AbortCurrentTransaction(void);
extern void BeginTransactionBlock(void);
-extern bool EndTransactionBlock(void);
+extern bool EndTransactionBlock(bool chain);
extern bool PrepareTransactionBlock(const char *gid);
-extern void UserAbortTransactionBlock(void);
+extern void UserAbortTransactionBlock(bool chain);
extern void BeginImplicitTransactionBlock(void);
extern void EndImplicitTransactionBlock(void);
extern void ReleaseSavepoint(const char *name);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index aa4a0dba2a..da34aeb27f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2989,6 +2989,7 @@ typedef struct TransactionStmt
List *options; /* for BEGIN/START commands */
char *savepoint_name; /* for savepoint commands */
char *gid; /* for two-phase-commit related
commands */
+ bool chain; /* AND CHAIN option */
} TransactionStmt;
/* ----------------------
diff --git a/src/test/regress/expected/transactions.out
b/src/test/regress/expected/transactions.out
index 69e176c525..0167520333 100644
--- a/src/test/regress/expected/transactions.out
+++ b/src/test/regress/expected/transactions.out
@@ -659,6 +659,145 @@ ERROR: portal "ctt" cannot be run
COMMIT;
DROP FUNCTION create_temp_tab();
DROP FUNCTION invert(x float8);
+-- Tests for AND CHAIN
+CREATE TABLE abc (a int);
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO abc VALUES (1);
+INSERT INTO abc VALUES (2);
+COMMIT AND CHAIN; -- TBLOCK_END
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO abc VALUES ('error');
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: INSERT INTO abc VALUES ('error');
+ ^
+INSERT INTO abc VALUES (3); -- check it's really aborted
+ERROR: current transaction is aborted, commands ignored until end of
transaction block
+COMMIT AND CHAIN; -- TBLOCK_ABORT_END
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO abc VALUES (3);
+COMMIT;
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+SAVEPOINT x;
+INSERT INTO abc VALUES ('error');
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: INSERT INTO abc VALUES ('error');
+ ^
+COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO abc VALUES (4);
+COMMIT;
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, DEFERRABLE;
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO abc VALUES (5);
+ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+INSERT INTO abc VALUES ('error');
+ERROR: invalid input syntax for type integer: "error"
+LINE 1: INSERT INTO abc VALUES ('error');
+ ^
+ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END
+SHOW transaction_isolation;
+ transaction_isolation
+-----------------------
+ repeatable read
+(1 row)
+
+SHOW transaction_deferrable;
+ transaction_deferrable
+------------------------
+ on
+(1 row)
+
+ROLLBACK;
+SELECT * FROM abc ORDER BY 1;
+ a
+---
+ 1
+ 2
+ 3
+ 4
+(4 rows)
+
+DROP TABLE abc;
-- Test assorted behaviors around the implicit transaction block created
-- when multiple SQL commands are sent in a single Query message. These
-- tests rely on the fact that psql will not break SQL commands apart at a
diff --git a/src/test/regress/sql/transactions.sql
b/src/test/regress/sql/transactions.sql
index 2e3739fd6c..7cd89f2477 100644
--- a/src/test/regress/sql/transactions.sql
+++ b/src/test/regress/sql/transactions.sql
@@ -419,6 +419,55 @@ CREATE FUNCTION create_temp_tab() RETURNS text
DROP FUNCTION invert(x float8);
+-- Tests for AND CHAIN
+
+CREATE TABLE abc (a int);
+
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, DEFERRABLE;
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+INSERT INTO abc VALUES (1);
+INSERT INTO abc VALUES (2);
+COMMIT AND CHAIN; -- TBLOCK_END
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+INSERT INTO abc VALUES ('error');
+INSERT INTO abc VALUES (3); -- check it's really aborted
+COMMIT AND CHAIN; -- TBLOCK_ABORT_END
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+INSERT INTO abc VALUES (3);
+COMMIT;
+
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, DEFERRABLE;
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+SAVEPOINT x;
+INSERT INTO abc VALUES ('error');
+COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+INSERT INTO abc VALUES (4);
+COMMIT;
+
+START TRANSACTION ISOLATION LEVEL REPEATABLE READ, DEFERRABLE;
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+INSERT INTO abc VALUES (5);
+ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+INSERT INTO abc VALUES ('error');
+ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END
+SHOW transaction_isolation;
+SHOW transaction_deferrable;
+ROLLBACK;
+
+SELECT * FROM abc ORDER BY 1;
+
+DROP TABLE abc;
+
+
-- Test assorted behaviors around the implicit transaction block created
-- when multiple SQL commands are sent in a single Query message. These
-- tests rely on the fact that psql will not break SQL commands apart at a
base-commit: eee01d606eb40f760799320e75d45c84022353d8
--
2.19.0
>From 647de712265239fa03cab409bb239a226204dc20 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Wed, 28 Feb 2018 10:04:06 -0500
Subject: [PATCH v2 2/2] 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 | 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
