From a583675108e60c55d0d4947d6c474bb9cb166d1a Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 12 Aug 2004 19:12:21 +0000 Subject: [PATCH] Allow optional SAVEPOINT keyword in RELEASE and ROLLBACK TO, for greater compliance with SQL2003 spec syntax. Oliver Jowett --- doc/src/sgml/ref/allfiles.sgml | 4 +- .../{release.sgml => release_savepoint.sgml} | 26 ++++----- doc/src/sgml/ref/rollback_to.sgml | 34 +++++++----- doc/src/sgml/ref/savepoint.sgml | 16 +++--- src/backend/parser/gram.y | 22 ++++++-- src/backend/tcop/utility.c | 6 +-- src/test/regress/expected/transactions.out | 54 +++++++++---------- src/test/regress/sql/transactions.sql | 50 ++++++++--------- 8 files changed, 118 insertions(+), 94 deletions(-) rename doc/src/sgml/ref/{release.sgml => release_savepoint.sgml} (77%) diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 2ab20c9c690..cdbbaf89bb1 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -88,7 +88,7 @@ Complete list of usable sgml source files in this directory. - + diff --git a/doc/src/sgml/ref/release.sgml b/doc/src/sgml/ref/release_savepoint.sgml similarity index 77% rename from doc/src/sgml/ref/release.sgml rename to doc/src/sgml/ref/release_savepoint.sgml index 17ef14ee3c5..f75bce9f70b 100644 --- a/doc/src/sgml/ref/release.sgml +++ b/doc/src/sgml/ref/release_savepoint.sgml @@ -1,31 +1,31 @@ - + - RELEASE + RELEASE SAVEPOINT SQL - Language Statements - RELEASE + RELEASE SAVEPOINT destroy a previously defined savepoint - - RELEASE + + RELEASE SAVEPOINT - + savepoints releasing -RELEASE savepoint_name +RELEASE [ SAVEPOINT ] savepoint_name @@ -33,7 +33,7 @@ RELEASE savepoint_name Description - RELEASE destroys a savepoint previously defined + RELEASE SAVEPOINT destroys a savepoint previously defined in the current transaction. @@ -48,7 +48,7 @@ RELEASE savepoint_name - RELEASE also destroys all savepoints that were + RELEASE SAVEPOINT also destroys all savepoints that were established after the named savepoint was established. @@ -97,7 +97,7 @@ BEGIN; INSERT INTO table VALUES (3); SAVEPOINT my_savepoint; INSERT INTO table VALUES (4); - RELEASE my_savepoint; + RELEASE SAVEPOINT my_savepoint; COMMIT; The above transaction will insert both 3 and 4. @@ -108,7 +108,9 @@ COMMIT; Compatibility - RELEASE is fully conforming to the SQL standard. + The SQL2003 standard specifies that the keyword SAVEPOINT is mandatory. + PostgreSQL allows the SAVEPOINT keyword to be + omitted. Otherwise, this command is fully conforming. diff --git a/doc/src/sgml/ref/rollback_to.sgml b/doc/src/sgml/ref/rollback_to.sgml index e38c4f4c4fd..150145f383c 100644 --- a/doc/src/sgml/ref/rollback_to.sgml +++ b/doc/src/sgml/ref/rollback_to.sgml @@ -1,21 +1,21 @@ - ROLLBACK TO + ROLLBACK TO SAVEPOINT SQL - Language Statements - ROLLBACK TO + ROLLBACK TO SAVEPOINT roll back to a savepoint - ROLLBACK TO + ROLLBACK TO SAVEPOINT @@ -25,7 +25,7 @@ PostgreSQL documentation -ROLLBACK TO savepoint_name +ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name @@ -39,7 +39,7 @@ ROLLBACK TO savepoint_name - ROLLBACK TO implicitly destroys all savepoints that + ROLLBACK TO SAVEPOINT implicitly destroys all savepoints that were established after the named savepoint. @@ -63,9 +63,9 @@ ROLLBACK TO savepoint_name Notes - Use to - destroy a savepoint without discarding the effects of commands executed - after it was established. + Use to destroy a savepoint without + discarding the effects of commands executed after it was established. @@ -81,7 +81,7 @@ ROLLBACK TO savepoint_name left it pointing to (that is, FETCH is not rolled back). A cursor whose execution causes a transaction to abort is put in a can't-execute state, so while the transaction can be restored using - ROLLBACK TO, the cursor can no longer be used. + ROLLBACK TO SAVEPOINT, the cursor can no longer be used. @@ -92,7 +92,7 @@ ROLLBACK TO savepoint_name To undo the effects of the commands executed after my_savepoint was established: -ROLLBACK TO my_savepoint; +ROLLBACK TO SAVEPOINT my_savepoint; @@ -110,7 +110,7 @@ FETCH 1 FROM foo; ---------- 1 -ROLLBACK TO foo; +ROLLBACK TO SAVEPOINT foo; FETCH 1 FROM foo; ?column? @@ -128,7 +128,13 @@ COMMIT; Compatibility - This command is fully SQL standard conforming. + The SQL2003 standard specifies that the keyword SAVEPOINT is mandatory. + PostgreSQL and Oracle + allow the SAVEPOINT keyword to be omitted. SQL2003 allows only + WORK, not TRANSACTION, as a noise word after ROLLBACK. Also, SQL2003 + has an optional clause AND [ NO ] CHAIN which is not currently supported + by PostgreSQL. Otherwise, this command is + fully conforming. @@ -139,7 +145,7 @@ COMMIT; - + diff --git a/doc/src/sgml/ref/savepoint.sgml b/doc/src/sgml/ref/savepoint.sgml index b881191c436..69f5dfb4e2d 100644 --- a/doc/src/sgml/ref/savepoint.sgml +++ b/doc/src/sgml/ref/savepoint.sgml @@ -1,5 +1,5 @@ @@ -64,8 +64,8 @@ SAVEPOINT savepoint_name Use to - rollback to a savepoint. Use to destroy a savepoint, keeping + rollback to a savepoint. Use to destroy a savepoint, keeping the effects of commands executed after it was established. @@ -86,7 +86,7 @@ BEGIN; INSERT INTO table VALUES (1); SAVEPOINT my_savepoint; INSERT INTO table VALUES (2); - ROLLBACK TO my_savepoint; + ROLLBACK TO SAVEPOINT my_savepoint; INSERT INTO table VALUES (3); COMMIT; @@ -100,7 +100,7 @@ BEGIN; INSERT INTO table VALUES (3); SAVEPOINT my_savepoint; INSERT INTO table VALUES (4); - RELEASE my_savepoint; + RELEASE SAVEPOINT my_savepoint; COMMIT; The above transaction will insert both 3 and 4. @@ -116,8 +116,8 @@ COMMIT; PostgreSQL, the old savepoint is kept, though only the more recent one will be used when rolling back or releasing. (Releasing the newer savepoint will cause the older one to again become accessible to - ROLLBACK TO and RELEASE.) - Other than that, SAVEPOINT is fully SQL conforming. + ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.) + Otherwise, SAVEPOINT is fully SQL conforming. @@ -128,7 +128,7 @@ COMMIT; - + diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index eddfb6e80d2..6a27714b05e 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.469 2004/08/02 04:26:35 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.470 2004/08/12 19:12:21 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -3982,6 +3982,14 @@ TransactionStmt: (Node *)makeString($2))); $$ = (Node *)n; } + | RELEASE SAVEPOINT ColId + { + TransactionStmt *n = makeNode(TransactionStmt); + n->kind = TRANS_STMT_RELEASE; + n->options = list_make1(makeDefElem("savepoint_name", + (Node *)makeString($3))); + $$ = (Node *)n; + } | RELEASE ColId { TransactionStmt *n = makeNode(TransactionStmt); @@ -3990,12 +3998,20 @@ TransactionStmt: (Node *)makeString($2))); $$ = (Node *)n; } - | ROLLBACK TO ColId + | ROLLBACK opt_transaction TO SAVEPOINT ColId { TransactionStmt *n = makeNode(TransactionStmt); n->kind = TRANS_STMT_ROLLBACK_TO; n->options = list_make1(makeDefElem("savepoint_name", - (Node *)makeString($3))); + (Node *)makeString($5))); + $$ = (Node *)n; + } + | ROLLBACK opt_transaction TO ColId + { + TransactionStmt *n = makeNode(TransactionStmt); + n->kind = TRANS_STMT_ROLLBACK_TO; + n->options = list_make1(makeDefElem("savepoint_name", + (Node *)makeString($4))); $$ = (Node *)n; } ; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index b9c50d7d81c..89ac4843ba8 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.223 2004/08/02 01:30:45 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.224 2004/08/12 19:12:21 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -388,12 +388,12 @@ ProcessUtility(Node *parsetree, break; case TRANS_STMT_RELEASE: - RequireTransactionChain((void *)stmt, "RELEASE"); + RequireTransactionChain((void *)stmt, "RELEASE SAVEPOINT"); ReleaseSavepoint(stmt->options); break; case TRANS_STMT_ROLLBACK_TO: - RequireTransactionChain((void *)stmt, "ROLLBACK TO"); + RequireTransactionChain((void *)stmt, "ROLLBACK TO SAVEPOINT"); RollbackToSavepoint(stmt->options); /* * CommitTransactionCommand is in charge diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out index d5a2c0c5fa8..2b8470c931d 100644 --- a/src/test/regress/expected/transactions.out +++ b/src/test/regress/expected/transactions.out @@ -77,11 +77,11 @@ BEGIN; SAVEPOINT one; DROP TABLE foo; CREATE TABLE bar (a int); - ROLLBACK TO one; - RELEASE one; + ROLLBACK TO SAVEPOINT one; + RELEASE SAVEPOINT one; SAVEPOINT two; CREATE TABLE baz (a int); - RELEASE two; + RELEASE SAVEPOINT two; drop TABLE foobar; CREATE TABLE barbaz (a int); COMMIT; @@ -110,16 +110,16 @@ BEGIN; INSERT into bar VALUES (1); ERROR: relation "bar" does not exist ROLLBACK TO one; - RELEASE one; + RELEASE SAVEPOINT one; SAVEPOINT two; INSERT into barbaz VALUES (1); RELEASE two; SAVEPOINT three; SAVEPOINT four; INSERT INTO foo VALUES (2); - RELEASE four; - ROLLBACK TO three; - RELEASE three; + RELEASE SAVEPOINT four; + ROLLBACK TO SAVEPOINT three; + RELEASE SAVEPOINT three; INSERT INTO foo VALUES (3); COMMIT; SELECT * FROM foo; -- should have 1 and 3 @@ -140,8 +140,8 @@ BEGIN; SAVEPOINT one; SELECT foo; ERROR: column "foo" does not exist - ROLLBACK TO one; - RELEASE one; + ROLLBACK TO SAVEPOINT one; + RELEASE SAVEPOINT one; SAVEPOINT two; CREATE TABLE savepoints (a int); SAVEPOINT three; @@ -150,7 +150,7 @@ ERROR: column "foo" does not exist INSERT INTO savepoints VALUES (2); SAVEPOINT five; INSERT INTO savepoints VALUES (3); - ROLLBACK TO five; + ROLLBACK TO SAVEPOINT five; COMMIT; COMMIT; -- should not be in a transaction block WARNING: there is no transaction in progress @@ -165,7 +165,7 @@ SELECT * FROM savepoints; BEGIN; SAVEPOINT one; DELETE FROM savepoints WHERE a=1; - RELEASE one; + RELEASE SAVEPOINT one; SAVEPOINT two; DELETE FROM savepoints WHERE a=1; SAVEPOINT three; @@ -200,7 +200,7 @@ BEGIN; INSERT INTO savepoints VALUES (6); SAVEPOINT one; INSERT INTO savepoints VALUES (7); - RELEASE one; + RELEASE SAVEPOINT one; INSERT INTO savepoints VALUES (8); COMMIT; -- rows 6 and 8 should have been created by the same xact @@ -221,7 +221,7 @@ BEGIN; INSERT INTO savepoints VALUES (9); SAVEPOINT one; INSERT INTO savepoints VALUES (10); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (11); COMMIT; SELECT a FROM savepoints WHERE a in (9, 10, 11); @@ -244,7 +244,7 @@ BEGIN; INSERT INTO savepoints VALUES (13); SAVEPOINT two; INSERT INTO savepoints VALUES (14); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (15); SAVEPOINT two; INSERT INTO savepoints VALUES (16); @@ -266,9 +266,9 @@ BEGIN; INSERT INTO savepoints VALUES (19); SAVEPOINT two; INSERT INTO savepoints VALUES (20); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (21); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (22); COMMIT; SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22; @@ -282,10 +282,10 @@ DROP TABLE savepoints; -- only in a transaction block: SAVEPOINT one; ERROR: SAVEPOINT may only be used in transaction blocks -ROLLBACK TO one; -ERROR: ROLLBACK TO may only be used in transaction blocks -RELEASE one; -ERROR: RELEASE may only be used in transaction blocks +ROLLBACK TO SAVEPOINT one; +ERROR: ROLLBACK TO SAVEPOINT may only be used in transaction blocks +RELEASE SAVEPOINT one; +ERROR: RELEASE SAVEPOINT may only be used in transaction blocks -- Only "rollback to" allowed in aborted state BEGIN; SAVEPOINT one; @@ -293,9 +293,9 @@ BEGIN; ERROR: division by zero SAVEPOINT two; -- ignored till the end of ... ERROR: current transaction is aborted, commands ignored until end of transaction block - RELEASE one; -- ignored till the end of ... + RELEASE SAVEPOINT one; -- ignored till the end of ... ERROR: current transaction is aborted, commands ignored until end of transaction block - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; SELECT 1; ?column? ---------- @@ -328,7 +328,7 @@ BEGIN; 9 (10 rows) - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; FETCH 10 FROM c; unique2 --------- @@ -344,7 +344,7 @@ BEGIN; 19 (10 rows) - RELEASE one; + RELEASE SAVEPOINT one; FETCH 10 FROM c; unique2 --------- @@ -365,12 +365,12 @@ BEGIN; SAVEPOINT two; FETCH 10 FROM c; ERROR: division by zero - ROLLBACK TO two; + ROLLBACK TO SAVEPOINT two; -- c is now dead to the world ... FETCH 10 FROM c; ERROR: portal "c" cannot be run - ROLLBACK TO two; - RELEASE two; + ROLLBACK TO SAVEPOINT two; + RELEASE SAVEPOINT two; FETCH 10 FROM c; ERROR: portal "c" cannot be run COMMIT; diff --git a/src/test/regress/sql/transactions.sql b/src/test/regress/sql/transactions.sql index d101ff305dd..d83a9f077fa 100644 --- a/src/test/regress/sql/transactions.sql +++ b/src/test/regress/sql/transactions.sql @@ -64,11 +64,11 @@ BEGIN; SAVEPOINT one; DROP TABLE foo; CREATE TABLE bar (a int); - ROLLBACK TO one; - RELEASE one; + ROLLBACK TO SAVEPOINT one; + RELEASE SAVEPOINT one; SAVEPOINT two; CREATE TABLE baz (a int); - RELEASE two; + RELEASE SAVEPOINT two; drop TABLE foobar; CREATE TABLE barbaz (a int); COMMIT; @@ -84,16 +84,16 @@ BEGIN; SAVEPOINT one; INSERT into bar VALUES (1); ROLLBACK TO one; - RELEASE one; + RELEASE SAVEPOINT one; SAVEPOINT two; INSERT into barbaz VALUES (1); RELEASE two; SAVEPOINT three; SAVEPOINT four; INSERT INTO foo VALUES (2); - RELEASE four; - ROLLBACK TO three; - RELEASE three; + RELEASE SAVEPOINT four; + ROLLBACK TO SAVEPOINT three; + RELEASE SAVEPOINT three; INSERT INTO foo VALUES (3); COMMIT; SELECT * FROM foo; -- should have 1 and 3 @@ -103,8 +103,8 @@ SELECT * FROM barbaz; -- should have 1 BEGIN; SAVEPOINT one; SELECT foo; - ROLLBACK TO one; - RELEASE one; + ROLLBACK TO SAVEPOINT one; + RELEASE SAVEPOINT one; SAVEPOINT two; CREATE TABLE savepoints (a int); SAVEPOINT three; @@ -113,7 +113,7 @@ BEGIN; INSERT INTO savepoints VALUES (2); SAVEPOINT five; INSERT INTO savepoints VALUES (3); - ROLLBACK TO five; + ROLLBACK TO SAVEPOINT five; COMMIT; COMMIT; -- should not be in a transaction block SELECT * FROM savepoints; @@ -122,7 +122,7 @@ SELECT * FROM savepoints; BEGIN; SAVEPOINT one; DELETE FROM savepoints WHERE a=1; - RELEASE one; + RELEASE SAVEPOINT one; SAVEPOINT two; DELETE FROM savepoints WHERE a=1; SAVEPOINT three; @@ -145,7 +145,7 @@ BEGIN; INSERT INTO savepoints VALUES (6); SAVEPOINT one; INSERT INTO savepoints VALUES (7); - RELEASE one; + RELEASE SAVEPOINT one; INSERT INTO savepoints VALUES (8); COMMIT; -- rows 6 and 8 should have been created by the same xact @@ -157,7 +157,7 @@ BEGIN; INSERT INTO savepoints VALUES (9); SAVEPOINT one; INSERT INTO savepoints VALUES (10); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (11); COMMIT; SELECT a FROM savepoints WHERE a in (9, 10, 11); @@ -170,7 +170,7 @@ BEGIN; INSERT INTO savepoints VALUES (13); SAVEPOINT two; INSERT INTO savepoints VALUES (14); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (15); SAVEPOINT two; INSERT INTO savepoints VALUES (16); @@ -185,9 +185,9 @@ BEGIN; INSERT INTO savepoints VALUES (19); SAVEPOINT two; INSERT INTO savepoints VALUES (20); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (21); - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; INSERT INTO savepoints VALUES (22); COMMIT; SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22; @@ -196,16 +196,16 @@ DROP TABLE savepoints; -- only in a transaction block: SAVEPOINT one; -ROLLBACK TO one; -RELEASE one; +ROLLBACK TO SAVEPOINT one; +RELEASE SAVEPOINT one; -- Only "rollback to" allowed in aborted state BEGIN; SAVEPOINT one; SELECT 0/0; SAVEPOINT two; -- ignored till the end of ... - RELEASE one; -- ignored till the end of ... - ROLLBACK TO one; + RELEASE SAVEPOINT one; -- ignored till the end of ... + ROLLBACK TO SAVEPOINT one; SELECT 1; COMMIT; SELECT 1; -- this should work @@ -215,19 +215,19 @@ BEGIN; DECLARE c CURSOR FOR SELECT unique2 FROM tenk1; SAVEPOINT one; FETCH 10 FROM c; - ROLLBACK TO one; + ROLLBACK TO SAVEPOINT one; FETCH 10 FROM c; - RELEASE one; + RELEASE SAVEPOINT one; FETCH 10 FROM c; CLOSE c; DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1; SAVEPOINT two; FETCH 10 FROM c; - ROLLBACK TO two; + ROLLBACK TO SAVEPOINT two; -- c is now dead to the world ... FETCH 10 FROM c; - ROLLBACK TO two; - RELEASE two; + ROLLBACK TO SAVEPOINT two; + RELEASE SAVEPOINT two; FETCH 10 FROM c; COMMIT; -- 2.39.5