Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb6d72dd

Browse files
committed
Fix behavior of AND CHAIN outside of explicit transaction blocks
When using COMMIT AND CHAIN or ROLLBACK AND CHAIN not in an explicittransaction block, the previous implementation would leave atransaction block active in the ROLLBACK case but not the COMMIT case.To fix for now, error out when using these commands not in an explicittransaction block. This restriction could be lifted if a sensibledefinition and implementation is found.Bug: #15977Author: fn ln <emuser20140816@gmail.com>Reviewed-by: Fabien COELHO <coelho@cri.ensmp.fr>
1 parent4229104 commitb6d72dd

File tree

5 files changed

+166
-19
lines changed

5 files changed

+166
-19
lines changed

‎doc/src/sgml/ref/commit.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -77,7 +77,8 @@ COMMIT [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
7777

7878
<para>
7979
Issuing <command>COMMIT</command> when not inside a transaction does
80-
no harm, but it will provoke a warning message.
80+
no harm, but it will provoke a warning message. <command>COMMIT AND
81+
CHAIN</command> when not inside a transaction is an error.
8182
</para>
8283
</refsect1>
8384

‎doc/src/sgml/ref/rollback.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -76,7 +76,8 @@ ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN ]
7676

7777
<para>
7878
Issuing <command>ROLLBACK</command> outside of a transaction
79-
block emits a warning and otherwise has no effect.
79+
block emits a warning and otherwise has no effect. <command>ROLLBACK AND
80+
CHAIN</command> outside of a transaction block is an error.
8081
</para>
8182
</refsect1>
8283

‎src/backend/access/transam/xact.c

Lines changed: 41 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -3721,13 +3721,21 @@ EndTransactionBlock(bool chain)
37213721
break;
37223722

37233723
/*
3724-
* In an implicit transaction block, commit, but issue a warning
3724+
* We are in an implicit transaction block. If AND CHAIN was
3725+
* specified, error. Otherwise commit, but issue a warning
37253726
* because there was no explicit BEGIN before this.
37263727
*/
37273728
caseTBLOCK_IMPLICIT_INPROGRESS:
3728-
ereport(WARNING,
3729-
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3730-
errmsg("there is no transaction in progress")));
3729+
if (chain)
3730+
ereport(ERROR,
3731+
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3732+
/* translator: %s represents an SQL statement name */
3733+
errmsg("%s can only be used in transaction blocks",
3734+
"COMMIT AND CHAIN")));
3735+
else
3736+
ereport(WARNING,
3737+
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3738+
errmsg("there is no transaction in progress")));
37313739
s->blockState=TBLOCK_END;
37323740
result= true;
37333741
break;
@@ -3789,15 +3797,24 @@ EndTransactionBlock(bool chain)
37893797
break;
37903798

37913799
/*
3792-
* The user issued COMMIT when not inside a transaction. Issue a
3793-
* WARNING, staying in TBLOCK_STARTED state. The upcoming call to
3800+
* The user issued COMMIT when not inside a transaction. For
3801+
* COMMIT without CHAIN, issue a WARNING, staying in
3802+
* TBLOCK_STARTED state. The upcoming call to
37943803
* CommitTransactionCommand() will then close the transaction and
3795-
* put us back into the default state.
3804+
* put us back into the default state. For COMMIT AND CHAIN,
3805+
* error.
37963806
*/
37973807
caseTBLOCK_STARTED:
3798-
ereport(WARNING,
3799-
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3800-
errmsg("there is no transaction in progress")));
3808+
if (chain)
3809+
ereport(ERROR,
3810+
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3811+
/* translator: %s represents an SQL statement name */
3812+
errmsg("%s can only be used in transaction blocks",
3813+
"COMMIT AND CHAIN")));
3814+
else
3815+
ereport(WARNING,
3816+
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3817+
errmsg("there is no transaction in progress")));
38013818
result= true;
38023819
break;
38033820

@@ -3899,10 +3916,10 @@ UserAbortTransactionBlock(bool chain)
38993916
break;
39003917

39013918
/*
3902-
* The user issued ABORT when not inside a transaction.Issue a
3903-
* WARNING and go to abort state. The upcoming call to
3904-
* CommitTransactionCommand() will then put us back into the
3905-
* default state.
3919+
* The user issued ABORT when not inside a transaction. For
3920+
*ROLLBACK without CHAIN, issue aWARNING and go to abort state.
3921+
*The upcoming call toCommitTransactionCommand() will then put
3922+
*us back into thedefault state. For ROLLBACK AND CHAIN, error.
39063923
*
39073924
* We do the same thing with ABORT inside an implicit transaction,
39083925
* although in this case we might be rolling back actual database
@@ -3911,9 +3928,16 @@ UserAbortTransactionBlock(bool chain)
39113928
*/
39123929
caseTBLOCK_STARTED:
39133930
caseTBLOCK_IMPLICIT_INPROGRESS:
3914-
ereport(WARNING,
3915-
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3916-
errmsg("there is no transaction in progress")));
3931+
if (chain)
3932+
ereport(ERROR,
3933+
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3934+
/* translator: %s represents an SQL statement name */
3935+
errmsg("%s can only be used in transaction blocks",
3936+
"ROLLBACK AND CHAIN")));
3937+
else
3938+
ereport(WARNING,
3939+
(errcode(ERRCODE_NO_ACTIVE_SQL_TRANSACTION),
3940+
errmsg("there is no transaction in progress")));
39173941
s->blockState=TBLOCK_ABORT_PENDING;
39183942
break;
39193943

‎src/test/regress/expected/transactions.out

Lines changed: 78 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -839,6 +839,11 @@ SHOW transaction_deferrable;
839839
(1 row)
840840

841841
ROLLBACK;
842+
-- not allowed outside a transaction block
843+
COMMIT AND CHAIN; -- error
844+
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
845+
ROLLBACK AND CHAIN; -- error
846+
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
842847
SELECT * FROM abc ORDER BY 1;
843848
a
844849
---
@@ -934,6 +939,79 @@ SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
934939
ERROR: RELEASE SAVEPOINT can only be used in transaction blocks
935940
-- but this is OK, because the BEGIN converts it to a regular xact
936941
SELECT 1\; BEGIN\; SAVEPOINT sp\; ROLLBACK TO SAVEPOINT sp\; COMMIT;
942+
-- Tests for AND CHAIN in implicit transaction blocks
943+
SET TRANSACTION READ WRITE\; COMMIT AND CHAIN; -- error
944+
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
945+
SHOW transaction_read_only;
946+
transaction_read_only
947+
-----------------------
948+
off
949+
(1 row)
950+
951+
SET TRANSACTION READ WRITE\; ROLLBACK AND CHAIN; -- error
952+
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
953+
SHOW transaction_read_only;
954+
transaction_read_only
955+
-----------------------
956+
off
957+
(1 row)
958+
959+
CREATE TABLE abc (a int);
960+
-- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
961+
INSERT INTO abc VALUES (7)\; COMMIT\; INSERT INTO abc VALUES (8)\; COMMIT AND CHAIN; -- 7 commit, 8 error
962+
WARNING: there is no transaction in progress
963+
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
964+
INSERT INTO abc VALUES (9)\; ROLLBACK\; INSERT INTO abc VALUES (10)\; ROLLBACK AND CHAIN; -- 9 rollback, 10 error
965+
WARNING: there is no transaction in progress
966+
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
967+
-- COMMIT/ROLLBACK AND CHAIN + COMMIT/ROLLBACK
968+
INSERT INTO abc VALUES (11)\; COMMIT AND CHAIN\; INSERT INTO abc VALUES (12)\; COMMIT; -- 11 error, 12 not reached
969+
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
970+
INSERT INTO abc VALUES (13)\; ROLLBACK AND CHAIN\; INSERT INTO abc VALUES (14)\; ROLLBACK; -- 13 error, 14 not reached
971+
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
972+
-- START TRANSACTION + COMMIT/ROLLBACK AND CHAIN
973+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (15)\; COMMIT AND CHAIN; -- 15 ok
974+
SHOW transaction_isolation; -- transaction is active at this point
975+
transaction_isolation
976+
-----------------------
977+
repeatable read
978+
(1 row)
979+
980+
COMMIT;
981+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (16)\; ROLLBACK AND CHAIN; -- 16 ok
982+
SHOW transaction_isolation; -- transaction is active at this point
983+
transaction_isolation
984+
-----------------------
985+
repeatable read
986+
(1 row)
987+
988+
ROLLBACK;
989+
-- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
990+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (17)\; COMMIT\; INSERT INTO abc VALUES (18)\; COMMIT AND CHAIN; -- 17 commit, 18 error
991+
ERROR: COMMIT AND CHAIN can only be used in transaction blocks
992+
SHOW transaction_isolation; -- out of transaction block
993+
transaction_isolation
994+
-----------------------
995+
read committed
996+
(1 row)
997+
998+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\; INSERT INTO abc VALUES (19)\; ROLLBACK\; INSERT INTO abc VALUES (20)\; ROLLBACK AND CHAIN; -- 19 rollback, 20 error
999+
ERROR: ROLLBACK AND CHAIN can only be used in transaction blocks
1000+
SHOW transaction_isolation; -- out of transaction block
1001+
transaction_isolation
1002+
-----------------------
1003+
read committed
1004+
(1 row)
1005+
1006+
SELECT * FROM abc ORDER BY 1;
1007+
a
1008+
----
1009+
7
1010+
15
1011+
17
1012+
(3 rows)
1013+
1014+
DROP TABLE abc;
9371015
-- Test for successful cleanup of an aborted transaction at session exit.
9381016
-- THIS MUST BE THE LAST TEST IN THIS FILE.
9391017
begin;

‎src/test/regress/sql/transactions.sql

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -475,6 +475,10 @@ SHOW transaction_read_only;
475475
SHOW transaction_deferrable;
476476
ROLLBACK;
477477

478+
-- not allowed outside a transaction block
479+
COMMITAND CHAIN;-- error
480+
ROLLBACKAND CHAIN;-- error
481+
478482
SELECT*FROM abcORDER BY1;
479483

480484
RESET default_transaction_read_only;
@@ -536,6 +540,45 @@ SELECT 2\; RELEASE SAVEPOINT sp\; SELECT 3;
536540
SELECT1\;BEGIN\; SAVEPOINT sp\;ROLLBACK TO SAVEPOINT sp\;COMMIT;
537541

538542

543+
-- Tests for AND CHAIN in implicit transaction blocks
544+
545+
SET TRANSACTION READ WRITE\;COMMITAND CHAIN;-- error
546+
SHOW transaction_read_only;
547+
548+
SET TRANSACTION READ WRITE\;ROLLBACKAND CHAIN;-- error
549+
SHOW transaction_read_only;
550+
551+
CREATETABLEabc (aint);
552+
553+
-- COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
554+
INSERT INTO abcVALUES (7)\;COMMIT\;INSERT INTO abcVALUES (8)\;COMMITAND CHAIN;-- 7 commit, 8 error
555+
INSERT INTO abcVALUES (9)\;ROLLBACK\;INSERT INTO abcVALUES (10)\;ROLLBACKAND CHAIN;-- 9 rollback, 10 error
556+
557+
-- COMMIT/ROLLBACK AND CHAIN + COMMIT/ROLLBACK
558+
INSERT INTO abcVALUES (11)\;COMMITAND CHAIN\;INSERT INTO abcVALUES (12)\;COMMIT;-- 11 error, 12 not reached
559+
INSERT INTO abcVALUES (13)\;ROLLBACKAND CHAIN\;INSERT INTO abcVALUES (14)\;ROLLBACK;-- 13 error, 14 not reached
560+
561+
-- START TRANSACTION + COMMIT/ROLLBACK AND CHAIN
562+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTO abcVALUES (15)\;COMMITAND CHAIN;-- 15 ok
563+
SHOW transaction_isolation;-- transaction is active at this point
564+
COMMIT;
565+
566+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTO abcVALUES (16)\;ROLLBACKAND CHAIN;-- 16 ok
567+
SHOW transaction_isolation;-- transaction is active at this point
568+
ROLLBACK;
569+
570+
-- START TRANSACTION + COMMIT/ROLLBACK + COMMIT/ROLLBACK AND CHAIN
571+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTO abcVALUES (17)\;COMMIT\;INSERT INTO abcVALUES (18)\;COMMITAND CHAIN;-- 17 commit, 18 error
572+
SHOW transaction_isolation;-- out of transaction block
573+
574+
START TRANSACTION ISOLATION LEVEL REPEATABLE READ\;INSERT INTO abcVALUES (19)\;ROLLBACK\;INSERT INTO abcVALUES (20)\;ROLLBACKAND CHAIN;-- 19 rollback, 20 error
575+
SHOW transaction_isolation;-- out of transaction block
576+
577+
SELECT*FROM abcORDER BY1;
578+
579+
DROPTABLE abc;
580+
581+
539582
-- Test for successful cleanup of an aborted transaction at session exit.
540583
-- THIS MUST BE THE LAST TEST IN THIS FILE.
541584

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp