Updated patch. I have squashed the two previously separate patches together in this one.
On 2019-01-06 15:14, Fabien COELHO wrote: > I do not understand the value of the SAVEPOINT in the tests. The purpose of the SAVEPOINT in the test is because it exercises different switch cases in CommitTransactionCommand() and AbortCurrentTransaction(). It's not entirely comprehensible from the outside, but code coverage analysis confirms it. > Otherwise I'm okay with this patch. > > About the second patch, I'm still unhappy with functions named commit & > rollback doing something else, which result in somehow strange code, where > you have to guess that the transaction is restarted in all cases, either > within the commit function or explicitely. I have updated the SPI interface with your suggestions. I agree it's better that way. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 87a1c30fedddeb83f7a84a0c8f7d012a0df43814 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <pe...@eisentraut.org> Date: Mon, 18 Mar 2019 10:52:12 +0100 Subject: [PATCH v5] 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. Discussion: https://www.postgresql.org/message-id/flat/28536681-324b-10dc-ade8-ab46f7645...@2ndquadrant.com --- doc/src/sgml/plpgsql.sgml | 9 + doc/src/sgml/ref/abort.sgml | 14 +- doc/src/sgml/ref/commit.sgml | 19 +- doc/src/sgml/ref/end.sgml | 14 +- doc/src/sgml/ref/rollback.sgml | 19 +- doc/src/sgml/spi.sgml | 25 ++- src/backend/access/transam/xact.c | 73 ++++++- src/backend/catalog/sql_features.txt | 2 +- src/backend/executor/spi.c | 50 ++++- 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/bin/psql/tab-complete.c | 8 +- src/include/access/xact.h | 6 +- src/include/executor/spi.h | 2 + src/include/nodes/parsenodes.h | 1 + .../src/expected/plpgsql_transaction.out | 28 +++ src/pl/plpgsql/src/pl_exec.c | 18 +- src/pl/plpgsql/src/pl_funcs.c | 10 +- src/pl/plpgsql/src/pl_gram.y | 18 +- src/pl/plpgsql/src/pl_unreserved_kwlist.h | 2 + src/pl/plpgsql/src/plpgsql.h | 2 + .../plpgsql/src/sql/plpgsql_transaction.sql | 23 +++ src/test/regress/expected/transactions.out | 191 ++++++++++++++++++ src/test/regress/sql/transactions.sql | 63 ++++++ 26 files changed, 584 insertions(+), 38 deletions(-) diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index f8c6435c50..eacd67d8b6 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/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..96a018e6aa 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> @@ -79,9 +91,8 @@ <title>Examples</title> <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 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..54fffefe18 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> @@ -78,9 +90,8 @@ <title>Examples</title> <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 6f4f3bae6f..8ed4814d9a 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -4390,6 +4390,10 @@ <title>Transaction Management</title> <refsynopsisdiv> <synopsis> void SPI_commit(void) +</synopsis> + +<synopsis> +void SPI_commit_and_chain(void) </synopsis> </refsynopsisdiv> @@ -4406,7 +4410,14 @@ <title>Description</title> </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> @@ -4430,6 +4441,10 @@ <title>Description</title> <refsynopsisdiv> <synopsis> void SPI_rollback(void) +</synopsis> + +<synopsis> +void SPI_rollback_and_chain(void) </synopsis> </refsynopsisdiv> @@ -4444,9 +4459,15 @@ <title>Description</title> 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> diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index 6e5891749b..c3214d4f4d 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -190,6 +190,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; @@ -2775,6 +2776,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; + +void +SaveTransactionCharacteristics(void) +{ + save_XactIsoLevel = XactIsoLevel; + save_XactReadOnly = XactReadOnly; + save_XactDeferrable = XactDeferrable; +} + +void +RestoreTransactionCharacteristics(void) +{ + XactIsoLevel = save_XactIsoLevel; + XactReadOnly = save_XactReadOnly; + XactDeferrable = save_XactDeferrable; +} + + /* * CommitTransactionCommand */ @@ -2783,6 +2814,9 @@ CommitTransactionCommand(void) { TransactionState s = CurrentTransactionState; + if (s->chain) + SaveTransactionCharacteristics(); + switch (s->blockState) { /* @@ -2834,6 +2868,13 @@ CommitTransactionCommand(void) case TBLOCK_END: CommitTransaction(); s->blockState = TBLOCK_DEFAULT; + if (s->chain) + { + StartTransaction(); + s->blockState = TBLOCK_INPROGRESS; + s->chain = false; + RestoreTransactionCharacteristics(); + } break; /* @@ -2853,6 +2894,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; /* @@ -2864,6 +2912,13 @@ CommitTransactionCommand(void) AbortTransaction(); CleanupTransaction(); s->blockState = TBLOCK_DEFAULT; + if (s->chain) + { + StartTransaction(); + s->blockState = TBLOCK_INPROGRESS; + s->chain = false; + RestoreTransactionCharacteristics(); + } break; /* @@ -3521,7 +3576,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) @@ -3567,7 +3622,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; @@ -3693,6 +3748,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; } @@ -3703,7 +3765,7 @@ EndTransactionBlock(void) * As above, we don't actually do anything here except change blockState. */ void -UserAbortTransactionBlock(void) +UserAbortTransactionBlock(bool chain) { TransactionState s = CurrentTransactionState; @@ -3801,6 +3863,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 bade0fe9ae..6b23163929 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/executor/spi.c b/src/backend/executor/spi.c index d898f4ca78..6e262d1a3a 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -217,8 +217,8 @@ SPI_start_transaction(void) MemoryContextSwitchTo(oldcontext); } -void -SPI_commit(void) +static void +_SPI_commit(bool chain) { MemoryContext oldcontext = CurrentMemoryContext; @@ -250,14 +250,36 @@ 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_commit(void) +{ + _SPI_commit(false); +} + +void +SPI_commit_and_chain(void) +{ + _SPI_commit(true); +} + +static void +_SPI_rollback(bool chain) { MemoryContext oldcontext = CurrentMemoryContext; @@ -274,12 +296,34 @@ SPI_rollback(void) _SPI_current->internal_xact = true; + if (chain) + SaveTransactionCharacteristics(); + AbortCurrentTransaction(); + + if (chain) + { + StartTransactionCommand(); + RestoreTransactionCharacteristics(); + } + MemoryContextSwitchTo(oldcontext); _SPI_current->internal_xact = false; } +void +SPI_rollback(void) +{ + _SPI_rollback(false); +} + +void +SPI_rollback_and_chain(void) +{ + _SPI_rollback(true); +} + /* * Clean up SPI state. Called on transaction end (of non-SPI-internal * transactions) and when returning to the main loop on error. diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index a8a735c247..8a3b316fed 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3658,6 +3658,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 3cab90e9f8..37f3359b94 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1509,6 +1509,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 e23e68fdb3..d6836de33f 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -311,6 +311,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 @@ -9789,11 +9790,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 @@ -9810,25 +9812,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 @@ -9928,6 +9933,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 6ec795f1b4..8eded2908c 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -440,7 +440,7 @@ standard_ProcessUtility(PlannedStmt *pstmt, break; case TRANS_STMT_COMMIT: - if (!EndTransactionBlock()) + if (!EndTransactionBlock(stmt->chain)) { /* report unsuccessful commit in completionTag */ if (completionTag) @@ -471,7 +471,7 @@ standard_ProcessUtility(PlannedStmt *pstmt, break; case TRANS_STMT_ROLLBACK: - UserAbortTransactionBlock(); + UserAbortTransactionBlock(stmt->chain); break; case TRANS_STMT_SAVEPOINT: diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 10ae21cc61..3ba3498496 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -2091,16 +2091,18 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH("WORK", "TRANSACTION", "ISOLATION LEVEL", "READ", "DEFERRABLE", "NOT DEFERRABLE"); /* END, ABORT */ else if (Matches("END|ABORT")) - COMPLETE_WITH("WORK", "TRANSACTION"); + COMPLETE_WITH("AND", "WORK", "TRANSACTION"); /* COMMIT */ else if (Matches("COMMIT")) - COMPLETE_WITH("WORK", "TRANSACTION", "PREPARED"); + COMPLETE_WITH("AND", "WORK", "TRANSACTION", "PREPARED"); /* RELEASE SAVEPOINT */ else if (Matches("RELEASE")) COMPLETE_WITH("SAVEPOINT"); /* ROLLBACK */ else if (Matches("ROLLBACK")) - COMPLETE_WITH("WORK", "TRANSACTION", "TO SAVEPOINT", "PREPARED"); + COMPLETE_WITH("AND", "WORK", "TRANSACTION", "TO SAVEPOINT", "PREPARED"); + else if (Matches("ABORT|END|COMMIT|ROLLBACK", "AND")) + COMPLETE_WITH("CHAIN"); /* CALL */ else if (Matches("CALL")) COMPLETE_WITH_VERSIONED_SCHEMA_QUERY(Query_for_list_of_procedures, NULL); diff --git a/src/include/access/xact.h b/src/include/access/xact.h index 426e77846f..e8579dcd47 100644 --- a/src/include/access/xact.h +++ b/src/include/access/xact.h @@ -368,12 +368,14 @@ 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); -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/executor/spi.h b/src/include/executor/spi.h index eafcc7a4e4..83c28b79bf 100644 --- a/src/include/executor/spi.h +++ b/src/include/executor/spi.h @@ -160,7 +160,9 @@ extern int SPI_register_trigger_data(TriggerData *tdata); extern void SPI_start_transaction(void); extern void SPI_commit(void); +extern void SPI_commit_and_chain(void); extern void SPI_rollback(void); +extern void SPI_rollback_and_chain(void); extern void SPICleanup(void); extern void AtEOXact_SPI(bool isCommit); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index fe35783359..a883ff2e35 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2997,6 +2997,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/pl/plpgsql/src/expected/plpgsql_transaction.out b/src/pl/plpgsql/src/expected/plpgsql_transaction.out index 6eedb215a4..ba0745326a 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out +++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out @@ -523,6 +523,34 @@ BEGIN END; $$; CALL transaction_test11(); +-- transaction chain +TRUNCATE test1; +DO LANGUAGE plpgsql $$ +BEGIN + ROLLBACK; + SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; + FOR i IN 0..3 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 +SELECT * FROM test1; + a | b +---+--- + 0 | + 2 | +(2 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 6dfcd1611a..527cada4fe 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -4773,8 +4773,13 @@ exec_stmt_commit(PLpgSQL_execstate *estate, PLpgSQL_stmt_commit *stmt) { HoldPinnedPortals(); - SPI_commit(); - SPI_start_transaction(); + if (stmt->chain) + SPI_commit_and_chain(); + else + { + SPI_commit(); + SPI_start_transaction(); + } estate->simple_eval_estate = NULL; plpgsql_create_econtext(estate); @@ -4792,8 +4797,13 @@ exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt) { HoldPinnedPortals(); - SPI_rollback(); - SPI_start_transaction(); + if (stmt->chain) + SPI_rollback_and_chain(); + else + { + SPI_rollback(); + 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 0b63da2b4a..053f83dc74 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 03f7cdce8c..dea95f4230 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 @@ -2199,7 +2203,7 @@ stmt_null : K_NULL ';' } ; -stmt_commit : K_COMMIT ';' +stmt_commit : K_COMMIT opt_transaction_chain ';' { PLpgSQL_stmt_commit *new; @@ -2207,12 +2211,13 @@ stmt_commit : K_COMMIT ';' new->cmd_type = PLPGSQL_STMT_COMMIT; new->lineno = plpgsql_location_to_lineno(@1); new->stmtid = ++plpgsql_curr_compile->nstatements; + new->chain = $2; $$ = (PLpgSQL_stmt *)new; } ; -stmt_rollback : K_ROLLBACK ';' +stmt_rollback : K_ROLLBACK opt_transaction_chain ';' { PLpgSQL_stmt_rollback *new; @@ -2220,11 +2225,18 @@ stmt_rollback : K_ROLLBACK ';' new->cmd_type = PLPGSQL_STMT_ROLLBACK; new->lineno = plpgsql_location_to_lineno(@1); new->stmtid = ++plpgsql_curr_compile->nstatements; + 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; @@ -2482,10 +2494,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_unreserved_kwlist.h b/src/pl/plpgsql/src/pl_unreserved_kwlist.h index ce4be81dd8..6d85f9396e 100644 --- a/src/pl/plpgsql/src/pl_unreserved_kwlist.h +++ b/src/pl/plpgsql/src/pl_unreserved_kwlist.h @@ -29,10 +29,12 @@ /* name, value */ PG_KEYWORD("absolute", K_ABSOLUTE) PG_KEYWORD("alias", K_ALIAS) +PG_KEYWORD("and", K_AND) PG_KEYWORD("array", K_ARRAY) PG_KEYWORD("assert", K_ASSERT) PG_KEYWORD("backward", K_BACKWARD) PG_KEYWORD("call", K_CALL) +PG_KEYWORD("chain", K_CHAIN) PG_KEYWORD("close", K_CLOSE) PG_KEYWORD("collate", K_COLLATE) PG_KEYWORD("column", K_COLUMN) diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 0a5fbfa9d6..4eff62e8e5 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -545,6 +545,7 @@ typedef struct PLpgSQL_stmt_commit PLpgSQL_stmt_type cmd_type; int lineno; unsigned int stmtid; + bool chain; } PLpgSQL_stmt_commit; /* @@ -555,6 +556,7 @@ typedef struct PLpgSQL_stmt_rollback PLpgSQL_stmt_type cmd_type; int lineno; unsigned int stmtid; + 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..0c137dd31d 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..3 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/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out index 69e176c525..1b316cc9b8 100644 --- a/src/test/regress/expected/transactions.out +++ b/src/test/regress/expected/transactions.out @@ -659,6 +659,197 @@ 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); +-- set nondefault value so we have something to override below +SET default_transaction_read_only = on; +START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE; +SHOW transaction_isolation; + transaction_isolation +----------------------- + repeatable read +(1 row) + +SHOW transaction_read_only; + transaction_read_only +----------------------- + off +(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_read_only; + transaction_read_only +----------------------- + off +(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_read_only; + transaction_read_only +----------------------- + off +(1 row) + +SHOW transaction_deferrable; + transaction_deferrable +------------------------ + on +(1 row) + +INSERT INTO abc VALUES (4); +COMMIT; +START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE; +SHOW transaction_isolation; + transaction_isolation +----------------------- + repeatable read +(1 row) + +SHOW transaction_read_only; + transaction_read_only +----------------------- + off +(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_read_only; + transaction_read_only +----------------------- + off +(1 row) + +SHOW transaction_deferrable; + transaction_deferrable +------------------------ + on +(1 row) + +INSERT INTO abc VALUES (5); +COMMIT; +-- different mix of options just for fun +START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE, NOT DEFERRABLE; +SHOW transaction_isolation; + transaction_isolation +----------------------- + serializable +(1 row) + +SHOW transaction_read_only; + transaction_read_only +----------------------- + off +(1 row) + +SHOW transaction_deferrable; + transaction_deferrable +------------------------ + off +(1 row) + +INSERT INTO abc VALUES (6); +ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING +SHOW transaction_isolation; + transaction_isolation +----------------------- + serializable +(1 row) + +SHOW transaction_read_only; + transaction_read_only +----------------------- + off +(1 row) + +SHOW transaction_deferrable; + transaction_deferrable +------------------------ + off +(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 +----------------------- + serializable +(1 row) + +SHOW transaction_read_only; + transaction_read_only +----------------------- + off +(1 row) + +SHOW transaction_deferrable; + transaction_deferrable +------------------------ + off +(1 row) + +ROLLBACK; +SELECT * FROM abc ORDER BY 1; + a +--- + 1 + 2 + 4 + 5 +(4 rows) + +RESET default_transaction_read_only; +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..812e40a1a3 100644 --- a/src/test/regress/sql/transactions.sql +++ b/src/test/regress/sql/transactions.sql @@ -419,6 +419,69 @@ CREATE FUNCTION create_temp_tab() RETURNS text DROP FUNCTION invert(x float8); +-- Tests for AND CHAIN + +CREATE TABLE abc (a int); + +-- set nondefault value so we have something to override below +SET default_transaction_read_only = on; + +START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE; +SHOW transaction_isolation; +SHOW transaction_read_only; +SHOW transaction_deferrable; +INSERT INTO abc VALUES (1); +INSERT INTO abc VALUES (2); +COMMIT AND CHAIN; -- TBLOCK_END +SHOW transaction_isolation; +SHOW transaction_read_only; +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_read_only; +SHOW transaction_deferrable; +INSERT INTO abc VALUES (4); +COMMIT; + +START TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE, DEFERRABLE; +SHOW transaction_isolation; +SHOW transaction_read_only; +SHOW transaction_deferrable; +SAVEPOINT x; +INSERT INTO abc VALUES ('error'); +COMMIT AND CHAIN; -- TBLOCK_ABORT_PENDING +SHOW transaction_isolation; +SHOW transaction_read_only; +SHOW transaction_deferrable; +INSERT INTO abc VALUES (5); +COMMIT; + +-- different mix of options just for fun +START TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE, NOT DEFERRABLE; +SHOW transaction_isolation; +SHOW transaction_read_only; +SHOW transaction_deferrable; +INSERT INTO abc VALUES (6); +ROLLBACK AND CHAIN; -- TBLOCK_ABORT_PENDING +SHOW transaction_isolation; +SHOW transaction_read_only; +SHOW transaction_deferrable; +INSERT INTO abc VALUES ('error'); +ROLLBACK AND CHAIN; -- TBLOCK_ABORT_END +SHOW transaction_isolation; +SHOW transaction_read_only; +SHOW transaction_deferrable; +ROLLBACK; + +SELECT * FROM abc ORDER BY 1; + +RESET default_transaction_read_only; + +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: a0478b69985056965a5737184279a99bde421f69 -- 2.21.0
