Thread: Re: [HACKERS] SAVEPOINT syntax again
Making the assumption that we want standards-conforming syntax here, I
went ahead and did the necessary changes:
Oliver Jowett wrote:
> Comments:
>
> 1) We have a different syntax to the SQL200n draft (and Oracle by the
> looks of it) for ROLLBACK. The draft says:
>
>> <rollback statement> ::= ROLLBACK [ WORK ] [ AND [ NO ] CHAIN ] [
>> <savepoint clause> ]
>> <savepoint clause> ::= TO SAVEPOINT <savepoint specifier>
Oracle has ROLLBACK TO [ SAVEPOINT ] <savepoint specifier>
DB2 has ROLLBACK TO SAVEPOINT <savepoint specifier>
> 2) We have a different syntax for RELEASE too. The SQL200n draft says:
>
>> <release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier>
Oracle does not have RELEASE SAVEPOINT.
DB2 has RELEASE [ TO ] SAVEPOINT <savepoint specifier>
The attached patch implements an approximate union of the above syntaxes:
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] <savepoint specifier>
RELEASE [ TO ] SAVEPOINT <savepoint specifier>
Note that this means that "RELEASE foo" is no longer valid. It seems
solely a postgresql-ism -- anyone particularly attached to that syntax?
Also in the patch are documentation and regression test updates to
reflect the new syntax. I have changed the examples in the docs and the
regression tests to prefer the standard-conforming variants. Error
messages now refer to ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT rather
than ROLLBACK TO and RELEASE.
-O
? GNUmakefile
? config.log
? config.status
? src/Makefile.global
? src/include/pg_config.h
? src/include/stamp-h
Index: doc/src/sgml/ref/release.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/release.sgml,v
retrieving revision 1.1
diff -u -c -r1.1 release.sgml
*** doc/src/sgml/ref/release.sgml 1 Aug 2004 17:32:13 -0000 1.1
--- doc/src/sgml/ref/release.sgml 11 Aug 2004 23:38:37 -0000
***************
*** 5,21 ****
<refentry id="SQL-RELEASE">
<refmeta>
! <refentrytitle id="SQL-RELEASE-TITLE">RELEASE</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>RELEASE</refname>
<refpurpose>destroy a previously defined savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-release">
! <primary>RELEASE</primary>
</indexterm>
<indexterm zone="sql-release">
--- 5,21 ----
<refentry id="SQL-RELEASE">
<refmeta>
! <refentrytitle id="SQL-RELEASE-TITLE">RELEASE SAVEPOINT</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>RELEASE SAVEPOINT</refname>
<refpurpose>destroy a previously defined savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-release">
! <primary>RELEASE SAVEPOINT</primary>
</indexterm>
<indexterm zone="sql-release">
***************
*** 25,31 ****
<refsynopsisdiv>
<synopsis>
! RELEASE <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
--- 25,31 ----
<refsynopsisdiv>
<synopsis>
! RELEASE [ TO ] SAVEPOINT <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 33,39 ****
<title>Description</title>
<para>
! <command>RELEASE</command> destroys a savepoint previously defined
in the current transaction.
</para>
--- 33,39 ----
<title>Description</title>
<para>
! <command>RELEASE SAVEPOINT</command> destroys a savepoint previously defined
in the current transaction.
</para>
***************
*** 48,54 ****
</para>
<para>
! <command>RELEASE</command> also destroys all savepoints that were
established after the named savepoint was established.
</para>
</refsect1>
--- 48,54 ----
</para>
<para>
! <command>RELEASE SAVEPOINT</command> also destroys all savepoints that were
established after the named savepoint was established.
</para>
</refsect1>
***************
*** 97,103 ****
INSERT INTO table VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table VALUES (4);
! RELEASE my_savepoint;
COMMIT;
</programlisting>
The above transaction will insert both 3 and 4.
--- 97,103 ----
INSERT INTO table VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table VALUES (4);
! RELEASE SAVEPOINT my_savepoint;
COMMIT;
</programlisting>
The above transaction will insert both 3 and 4.
***************
*** 108,114 ****
<title>Compatibility</title>
<para>
! RELEASE is fully conforming to the SQL standard.
</para>
</refsect1>
--- 108,117 ----
<title>Compatibility</title>
<para>
! The SQL2003 standard specifies only a RELEASE SAVEPOINT form.
! <productname>PostgreSQL</productname> and <productname>DB2</productname>
! also allow RELEASE TO SAVEPOINT. Otherwise, this command is
! fully conforming.
</para>
</refsect1>
Index: doc/src/sgml/ref/rollback_to.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/rollback_to.sgml,v
retrieving revision 1.1
diff -u -c -r1.1 rollback_to.sgml
*** doc/src/sgml/ref/rollback_to.sgml 1 Aug 2004 17:32:13 -0000 1.1
--- doc/src/sgml/ref/rollback_to.sgml 11 Aug 2004 23:38:37 -0000
***************
*** 5,21 ****
<refentry id="SQL-ROLLBACK-TO">
<refmeta>
! <refentrytitle id="SQL-ROLLBACK-TO-TITLE">ROLLBACK TO</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>ROLLBACK TO</refname>
<refpurpose>roll back to a savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-rollback-to">
! <primary>ROLLBACK TO</primary>
</indexterm>
<indexterm zone="sql-rollback-to">
--- 5,21 ----
<refentry id="SQL-ROLLBACK-TO">
<refmeta>
! <refentrytitle id="SQL-ROLLBACK-TO-TITLE">ROLLBACK TO SAVEPOINT</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>ROLLBACK TO SAVEPOINT</refname>
<refpurpose>roll back to a savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-rollback-to">
! <primary>ROLLBACK TO SAVEPOINT</primary>
</indexterm>
<indexterm zone="sql-rollback-to">
***************
*** 25,31 ****
<refsynopsisdiv>
<synopsis>
! ROLLBACK TO <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
--- 25,31 ----
<refsynopsisdiv>
<synopsis>
! ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 39,45 ****
</para>
<para>
! <command>ROLLBACK TO</> implicitly destroys all savepoints that
were established after the named savepoint.
</para>
</refsect1>
--- 39,45 ----
</para>
<para>
! <command>ROLLBACK TO SAVEPOINT</> implicitly destroys all savepoints that
were established after the named savepoint.
</para>
</refsect1>
***************
*** 81,87 ****
left it pointing to (that is, <command>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
! <command>ROLLBACK TO</>, the cursor can no longer be used.
</para>
</refsect1>
--- 81,87 ----
left it pointing to (that is, <command>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
! <command>ROLLBACK TO SAVEPOINT</>, the cursor can no longer be used.
</para>
</refsect1>
***************
*** 92,98 ****
To undo the effects of the commands executed after <literal>my_savepoint</literal>
was established:
<programlisting>
! ROLLBACK TO my_savepoint;
</programlisting>
</para>
--- 92,98 ----
To undo the effects of the commands executed after <literal>my_savepoint</literal>
was established:
<programlisting>
! ROLLBACK TO SAVEPOINT my_savepoint;
</programlisting>
</para>
***************
*** 110,116 ****
----------
1
! ROLLBACK TO foo;
FETCH 1 FROM foo;
?column?
--- 110,116 ----
----------
1
! ROLLBACK TO SAVEPOINT foo;
FETCH 1 FROM foo;
?column?
***************
*** 128,134 ****
<title>Compatibility</title>
<para>
! This command is fully SQL standard conforming.
</para>
</refsect1>
--- 128,137 ----
<title>Compatibility</title>
<para>
! The SQL2003 standard specifies that the keyword SAVEPOINT is mandatory.
! <productname>PostgreSQL</productname> and <productname>Oracle</productname>
! allow the SAVEPOINT keyword to be omitted. Otherwise, this command is
! fully conforming.
</para>
</refsect1>
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.469
diff -u -c -r2.469 gram.y
*** src/backend/parser/gram.y 2 Aug 2004 04:26:35 -0000 2.469
--- src/backend/parser/gram.y 11 Aug 2004 23:38:38 -0000
***************
*** 3982,4001 ****
(Node *)makeString($2)));
$$ = (Node *)n;
}
! | RELEASE ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_RELEASE;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($2)));
$$ = (Node *)n;
}
! | ROLLBACK TO ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_ROLLBACK_TO;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($3)));
$$ = (Node *)n;
}
;
--- 3982,4001 ----
(Node *)makeString($2)));
$$ = (Node *)n;
}
! | RELEASE opt_to SAVEPOINT ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_RELEASE;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($4)));
$$ = (Node *)n;
}
! | ROLLBACK opt_transaction TO opt_savepoint ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_ROLLBACK_TO;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($5)));
$$ = (Node *)n;
}
;
***************
*** 4005,4010 ****
--- 4005,4018 ----
| /*EMPTY*/ {}
;
+ opt_savepoint: SAVEPOINT {}
+ | /*EMPTY*/ {}
+ ;
+
+ opt_to: TO {}
+ | /*EMPTY*/ {}
+ ;
+
transaction_mode_list:
ISOLATION LEVEL iso_level
{ $$ = list_make1(makeDefElem("transaction_isolation",
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.223
diff -u -c -r1.223 utility.c
*** src/backend/tcop/utility.c 2 Aug 2004 01:30:45 -0000 1.223
--- src/backend/tcop/utility.c 11 Aug 2004 23:38:38 -0000
***************
*** 388,399 ****
break;
case TRANS_STMT_RELEASE:
! RequireTransactionChain((void *)stmt, "RELEASE");
ReleaseSavepoint(stmt->options);
break;
case TRANS_STMT_ROLLBACK_TO:
! RequireTransactionChain((void *)stmt, "ROLLBACK TO");
RollbackToSavepoint(stmt->options);
/*
* CommitTransactionCommand is in charge
--- 388,399 ----
break;
case TRANS_STMT_RELEASE:
! RequireTransactionChain((void *)stmt, "RELEASE SAVEPOINT");
ReleaseSavepoint(stmt->options);
break;
case TRANS_STMT_ROLLBACK_TO:
! RequireTransactionChain((void *)stmt, "ROLLBACK TO SAVEPOINT");
RollbackToSavepoint(stmt->options);
/*
* CommitTransactionCommand is in charge
Index: src/test/regress/expected/transactions.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/transactions.out,v
retrieving revision 1.7
diff -u -c -r1.7 transactions.out
*** src/test/regress/expected/transactions.out 27 Jul 2004 05:11:38 -0000 1.7
--- src/test/regress/expected/transactions.out 11 Aug 2004 23:38:38 -0000
***************
*** 77,87 ****
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
--- 77,87 ----
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE SAVEPOINT two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
***************
*** 109,125 ****
SAVEPOINT one;
INSERT into bar VALUES (1);
ERROR: relation "bar" does not exist
! ROLLBACK TO one;
! RELEASE 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;
INSERT INTO foo VALUES (3);
COMMIT;
SELECT * FROM foo; -- should have 1 and 3
--- 109,125 ----
SAVEPOINT one;
INSERT into bar VALUES (1);
ERROR: relation "bar" does not exist
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
INSERT into barbaz VALUES (1);
! RELEASE SAVEPOINT two;
SAVEPOINT three;
SAVEPOINT four;
INSERT INTO foo VALUES (2);
! 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,147 ****
SAVEPOINT one;
SELECT foo;
ERROR: column "foo" does not exist
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
--- 140,147 ----
SAVEPOINT one;
SELECT foo;
ERROR: column "foo" does not exist
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
***************
*** 150,156 ****
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO five;
COMMIT;
COMMIT; -- should not be in a transaction block
WARNING: there is no transaction in progress
--- 150,156 ----
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO SAVEPOINT five;
COMMIT;
COMMIT; -- should not be in a transaction block
WARNING: there is no transaction in progress
***************
*** 165,171 ****
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
--- 165,171 ----
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
***************
*** 200,206 ****
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
--- 200,206 ----
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE SAVEPOINT one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
***************
*** 221,227 ****
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
--- 221,227 ----
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
***************
*** 244,250 ****
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
--- 244,250 ----
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
***************
*** 266,274 ****
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
--- 266,274 ----
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
***************
*** 282,291 ****
-- 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
-- Only "rollback to" allowed in aborted state
BEGIN;
SAVEPOINT one;
--- 282,291 ----
-- only in a transaction block:
SAVEPOINT one;
ERROR: SAVEPOINT 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,301 ****
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 ...
ERROR: current transaction is aborted, commands ignored until end of transaction block
! ROLLBACK TO one;
SELECT 1;
?column?
----------
--- 293,301 ----
ERROR: division by zero
SAVEPOINT two; -- ignored till the end of ...
ERROR: current transaction is aborted, commands ignored until end of transaction block
! RELEASE SAVEPOINT one; -- ignored till the end of ...
ERROR: current transaction is aborted, commands ignored until end of transaction block
! ROLLBACK TO SAVEPOINT one;
SELECT 1;
?column?
----------
***************
*** 328,334 ****
9
(10 rows)
! ROLLBACK TO one;
FETCH 10 FROM c;
unique2
---------
--- 328,334 ----
9
(10 rows)
! ROLLBACK TO SAVEPOINT one;
FETCH 10 FROM c;
unique2
---------
***************
*** 344,350 ****
19
(10 rows)
! RELEASE one;
FETCH 10 FROM c;
unique2
---------
--- 344,350 ----
19
(10 rows)
! RELEASE SAVEPOINT one;
FETCH 10 FROM c;
unique2
---------
***************
*** 365,376 ****
SAVEPOINT two;
FETCH 10 FROM c;
ERROR: division by zero
! ROLLBACK TO two;
-- c is now dead to the world ...
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
! ROLLBACK TO two;
! RELEASE two;
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
COMMIT;
--- 365,376 ----
SAVEPOINT two;
FETCH 10 FROM c;
ERROR: division by zero
! ROLLBACK TO SAVEPOINT two;
-- c is now dead to the world ...
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
! ROLLBACK TO SAVEPOINT two;
! RELEASE SAVEPOINT two;
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
COMMIT;
Index: src/test/regress/sql/transactions.sql
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/transactions.sql,v
retrieving revision 1.7
diff -u -c -r1.7 transactions.sql
*** src/test/regress/sql/transactions.sql 27 Jul 2004 05:11:48 -0000 1.7
--- src/test/regress/sql/transactions.sql 11 Aug 2004 23:38:38 -0000
***************
*** 64,74 ****
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
--- 64,74 ----
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE SAVEPOINT two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
***************
*** 83,99 ****
INSERT INTO foo VALUES (1);
SAVEPOINT one;
INSERT into bar VALUES (1);
! ROLLBACK TO one;
! RELEASE 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;
INSERT INTO foo VALUES (3);
COMMIT;
SELECT * FROM foo; -- should have 1 and 3
--- 83,99 ----
INSERT INTO foo VALUES (1);
SAVEPOINT one;
INSERT into bar VALUES (1);
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
INSERT into barbaz VALUES (1);
! RELEASE SAVEPOINT two;
SAVEPOINT three;
SAVEPOINT four;
INSERT INTO foo VALUES (2);
! 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,110 ****
BEGIN;
SAVEPOINT one;
SELECT foo;
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
--- 103,110 ----
BEGIN;
SAVEPOINT one;
SELECT foo;
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
***************
*** 113,119 ****
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO five;
COMMIT;
COMMIT; -- should not be in a transaction block
SELECT * FROM savepoints;
--- 113,119 ----
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO SAVEPOINT five;
COMMIT;
COMMIT; -- should not be in a transaction block
SELECT * FROM savepoints;
***************
*** 122,128 ****
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
--- 122,128 ----
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
***************
*** 145,151 ****
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
--- 145,151 ----
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE SAVEPOINT one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
***************
*** 157,163 ****
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
--- 157,163 ----
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
***************
*** 170,176 ****
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
--- 170,176 ----
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
***************
*** 185,193 ****
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
--- 185,193 ----
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
***************
*** 196,211 ****
-- only in a transaction block:
SAVEPOINT one;
! ROLLBACK TO one;
! RELEASE 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;
SELECT 1;
COMMIT;
SELECT 1; -- this should work
--- 196,211 ----
-- only in a transaction block:
SAVEPOINT 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 SAVEPOINT one; -- ignored till the end of ...
! ROLLBACK TO SAVEPOINT one;
SELECT 1;
COMMIT;
SELECT 1; -- this should work
***************
*** 215,233 ****
DECLARE c CURSOR FOR SELECT unique2 FROM tenk1;
SAVEPOINT one;
FETCH 10 FROM c;
! ROLLBACK TO one;
FETCH 10 FROM c;
! RELEASE 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;
-- c is now dead to the world ...
FETCH 10 FROM c;
! ROLLBACK TO two;
! RELEASE two;
FETCH 10 FROM c;
COMMIT;
--- 215,233 ----
DECLARE c CURSOR FOR SELECT unique2 FROM tenk1;
SAVEPOINT one;
FETCH 10 FROM c;
! ROLLBACK TO SAVEPOINT one;
FETCH 10 FROM c;
! 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 SAVEPOINT two;
-- c is now dead to the world ...
FETCH 10 FROM c;
! ROLLBACK TO SAVEPOINT two;
! RELEASE SAVEPOINT two;
FETCH 10 FROM c;
COMMIT;
Oliver Jowett <oliver@opencloud.com> writes:
> Oracle has ROLLBACK TO [ SAVEPOINT ] <savepoint specifier>
> DB2 has ROLLBACK TO SAVEPOINT <savepoint specifier>
I would go with Oracle's lead here, first because they are the de facto
standard, and second because I don't want to have to type out SAVEPOINT
every time I use one of these commands.
> <release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier>
> Oracle does not have RELEASE SAVEPOINT.
> DB2 has RELEASE [ TO ] SAVEPOINT <savepoint specifier>
I'd vote for RELEASE [ SAVEPOINT ] <name> (for brevity, and for
consistency with ROLLBACK). I feel no urge to copy DB2.
regards, tom lane
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > > Oracle has ROLLBACK TO [ SAVEPOINT ] <savepoint specifier> > > DB2 has ROLLBACK TO SAVEPOINT <savepoint specifier> > > I would go with Oracle's lead here, first because they are the de facto > standard, and second because I don't want to have to type out SAVEPOINT > every time I use one of these commands. > > > <release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier> > > > Oracle does not have RELEASE SAVEPOINT. > > DB2 has RELEASE [ TO ] SAVEPOINT <savepoint specifier> > > I'd vote for RELEASE [ SAVEPOINT ] <name> (for brevity, and for > consistency with ROLLBACK). I feel no urge to copy DB2. Are we ever going to use "RELEASE" for prepared statements? If so making SAVEPOINT optional might be a bad idea. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Are we ever going to use "RELEASE" for prepared statements? If so
> making SAVEPOINT optional might be a bad idea.
No, why would we? Their verb is DEALLOCATE.
regards, tom lane
Tom Lane wrote:
> Oliver Jowett <oliver@opencloud.com> writes:
>
>><release savepoint statement> ::= RELEASE SAVEPOINT <savepoint specifier>
>
>
>>Oracle does not have RELEASE SAVEPOINT.
>>DB2 has RELEASE [ TO ] SAVEPOINT <savepoint specifier>
>
>
> I'd vote for RELEASE [ SAVEPOINT ] <name> (for brevity, and for
> consistency with ROLLBACK). I feel no urge to copy DB2.
Here's an updated patch that supports the syntax you suggest. I kept the
error messages, doc examples and regression tests using RELEASE
SAVEPOINT in full to follow the standard.
-O
? GNUmakefile
? config.log
? config.status
? src/Makefile.global
? src/include/pg_config.h
? src/include/stamp-h
Index: doc/src/sgml/ref/release.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/release.sgml,v
retrieving revision 1.1
diff -u -c -r1.1 release.sgml
*** doc/src/sgml/ref/release.sgml 1 Aug 2004 17:32:13 -0000 1.1
--- doc/src/sgml/ref/release.sgml 12 Aug 2004 11:15:33 -0000
***************
*** 5,21 ****
<refentry id="SQL-RELEASE">
<refmeta>
! <refentrytitle id="SQL-RELEASE-TITLE">RELEASE</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>RELEASE</refname>
<refpurpose>destroy a previously defined savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-release">
! <primary>RELEASE</primary>
</indexterm>
<indexterm zone="sql-release">
--- 5,21 ----
<refentry id="SQL-RELEASE">
<refmeta>
! <refentrytitle id="SQL-RELEASE-TITLE">RELEASE SAVEPOINT</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>RELEASE SAVEPOINT</refname>
<refpurpose>destroy a previously defined savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-release">
! <primary>RELEASE SAVEPOINT</primary>
</indexterm>
<indexterm zone="sql-release">
***************
*** 25,31 ****
<refsynopsisdiv>
<synopsis>
! RELEASE <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
--- 25,31 ----
<refsynopsisdiv>
<synopsis>
! RELEASE [ SAVEPOINT ] <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 33,39 ****
<title>Description</title>
<para>
! <command>RELEASE</command> destroys a savepoint previously defined
in the current transaction.
</para>
--- 33,39 ----
<title>Description</title>
<para>
! <command>RELEASE SAVEPOINT</command> destroys a savepoint previously defined
in the current transaction.
</para>
***************
*** 48,54 ****
</para>
<para>
! <command>RELEASE</command> also destroys all savepoints that were
established after the named savepoint was established.
</para>
</refsect1>
--- 48,54 ----
</para>
<para>
! <command>RELEASE SAVEPOINT</command> also destroys all savepoints that were
established after the named savepoint was established.
</para>
</refsect1>
***************
*** 97,103 ****
INSERT INTO table VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table VALUES (4);
! RELEASE my_savepoint;
COMMIT;
</programlisting>
The above transaction will insert both 3 and 4.
--- 97,103 ----
INSERT INTO table VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table VALUES (4);
! RELEASE SAVEPOINT my_savepoint;
COMMIT;
</programlisting>
The above transaction will insert both 3 and 4.
***************
*** 108,114 ****
<title>Compatibility</title>
<para>
! RELEASE is fully conforming to the SQL standard.
</para>
</refsect1>
--- 108,116 ----
<title>Compatibility</title>
<para>
! The SQL2003 standard specifies that the keyword SAVEPOINT is mandatory.
! <productname>PostgreSQL</productname> allows the SAVEPOINT keyword to be
! omitted. Otherwise, this command is fully conforming.
</para>
</refsect1>
Index: doc/src/sgml/ref/rollback_to.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/rollback_to.sgml,v
retrieving revision 1.1
diff -u -c -r1.1 rollback_to.sgml
*** doc/src/sgml/ref/rollback_to.sgml 1 Aug 2004 17:32:13 -0000 1.1
--- doc/src/sgml/ref/rollback_to.sgml 12 Aug 2004 11:15:33 -0000
***************
*** 5,21 ****
<refentry id="SQL-ROLLBACK-TO">
<refmeta>
! <refentrytitle id="SQL-ROLLBACK-TO-TITLE">ROLLBACK TO</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>ROLLBACK TO</refname>
<refpurpose>roll back to a savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-rollback-to">
! <primary>ROLLBACK TO</primary>
</indexterm>
<indexterm zone="sql-rollback-to">
--- 5,21 ----
<refentry id="SQL-ROLLBACK-TO">
<refmeta>
! <refentrytitle id="SQL-ROLLBACK-TO-TITLE">ROLLBACK TO SAVEPOINT</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
! <refname>ROLLBACK TO SAVEPOINT</refname>
<refpurpose>roll back to a savepoint</refpurpose>
</refnamediv>
<indexterm zone="sql-rollback-to">
! <primary>ROLLBACK TO SAVEPOINT</primary>
</indexterm>
<indexterm zone="sql-rollback-to">
***************
*** 25,31 ****
<refsynopsisdiv>
<synopsis>
! ROLLBACK TO <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
--- 25,31 ----
<refsynopsisdiv>
<synopsis>
! ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] <replaceable>savepoint_name</replaceable>
</synopsis>
</refsynopsisdiv>
***************
*** 39,45 ****
</para>
<para>
! <command>ROLLBACK TO</> implicitly destroys all savepoints that
were established after the named savepoint.
</para>
</refsect1>
--- 39,45 ----
</para>
<para>
! <command>ROLLBACK TO SAVEPOINT</> implicitly destroys all savepoints that
were established after the named savepoint.
</para>
</refsect1>
***************
*** 81,87 ****
left it pointing to (that is, <command>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
! <command>ROLLBACK TO</>, the cursor can no longer be used.
</para>
</refsect1>
--- 81,87 ----
left it pointing to (that is, <command>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
! <command>ROLLBACK TO SAVEPOINT</>, the cursor can no longer be used.
</para>
</refsect1>
***************
*** 92,98 ****
To undo the effects of the commands executed after <literal>my_savepoint</literal>
was established:
<programlisting>
! ROLLBACK TO my_savepoint;
</programlisting>
</para>
--- 92,98 ----
To undo the effects of the commands executed after <literal>my_savepoint</literal>
was established:
<programlisting>
! ROLLBACK TO SAVEPOINT my_savepoint;
</programlisting>
</para>
***************
*** 110,116 ****
----------
1
! ROLLBACK TO foo;
FETCH 1 FROM foo;
?column?
--- 110,116 ----
----------
1
! ROLLBACK TO SAVEPOINT foo;
FETCH 1 FROM foo;
?column?
***************
*** 128,134 ****
<title>Compatibility</title>
<para>
! This command is fully SQL standard conforming.
</para>
</refsect1>
--- 128,137 ----
<title>Compatibility</title>
<para>
! The SQL2003 standard specifies that the keyword SAVEPOINT is mandatory.
! <productname>PostgreSQL</productname> and <productname>Oracle</productname>
! allow the SAVEPOINT keyword to be omitted. Otherwise, this command is
! fully conforming.
</para>
</refsect1>
Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.469
diff -u -c -r2.469 gram.y
*** src/backend/parser/gram.y 2 Aug 2004 04:26:35 -0000 2.469
--- src/backend/parser/gram.y 12 Aug 2004 11:15:35 -0000
***************
*** 3982,4001 ****
(Node *)makeString($2)));
$$ = (Node *)n;
}
! | RELEASE ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_RELEASE;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($2)));
$$ = (Node *)n;
}
! | ROLLBACK TO ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_ROLLBACK_TO;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($3)));
$$ = (Node *)n;
}
;
--- 3982,4001 ----
(Node *)makeString($2)));
$$ = (Node *)n;
}
! | RELEASE opt_savepoint ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_RELEASE;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($3)));
$$ = (Node *)n;
}
! | ROLLBACK opt_transaction TO opt_savepoint ColId
{
TransactionStmt *n = makeNode(TransactionStmt);
n->kind = TRANS_STMT_ROLLBACK_TO;
n->options = list_make1(makeDefElem("savepoint_name",
! (Node *)makeString($5)));
$$ = (Node *)n;
}
;
***************
*** 4005,4010 ****
--- 4005,4014 ----
| /*EMPTY*/ {}
;
+ opt_savepoint: SAVEPOINT {}
+ | /*EMPTY*/ {}
+ ;
+
transaction_mode_list:
ISOLATION LEVEL iso_level
{ $$ = list_make1(makeDefElem("transaction_isolation",
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/tcop/utility.c,v
retrieving revision 1.223
diff -u -c -r1.223 utility.c
*** src/backend/tcop/utility.c 2 Aug 2004 01:30:45 -0000 1.223
--- src/backend/tcop/utility.c 12 Aug 2004 11:15:35 -0000
***************
*** 388,399 ****
break;
case TRANS_STMT_RELEASE:
! RequireTransactionChain((void *)stmt, "RELEASE");
ReleaseSavepoint(stmt->options);
break;
case TRANS_STMT_ROLLBACK_TO:
! RequireTransactionChain((void *)stmt, "ROLLBACK TO");
RollbackToSavepoint(stmt->options);
/*
* CommitTransactionCommand is in charge
--- 388,399 ----
break;
case TRANS_STMT_RELEASE:
! RequireTransactionChain((void *)stmt, "RELEASE SAVEPOINT");
ReleaseSavepoint(stmt->options);
break;
case TRANS_STMT_ROLLBACK_TO:
! RequireTransactionChain((void *)stmt, "ROLLBACK TO SAVEPOINT");
RollbackToSavepoint(stmt->options);
/*
* CommitTransactionCommand is in charge
Index: src/test/regress/expected/transactions.out
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/expected/transactions.out,v
retrieving revision 1.7
diff -u -c -r1.7 transactions.out
*** src/test/regress/expected/transactions.out 27 Jul 2004 05:11:38 -0000 1.7
--- src/test/regress/expected/transactions.out 12 Aug 2004 11:15:40 -0000
***************
*** 77,87 ****
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
--- 77,87 ----
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE SAVEPOINT two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
***************
*** 109,125 ****
SAVEPOINT one;
INSERT into bar VALUES (1);
ERROR: relation "bar" does not exist
! ROLLBACK TO one;
! RELEASE 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;
INSERT INTO foo VALUES (3);
COMMIT;
SELECT * FROM foo; -- should have 1 and 3
--- 109,125 ----
SAVEPOINT one;
INSERT into bar VALUES (1);
ERROR: relation "bar" does not exist
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
INSERT into barbaz VALUES (1);
! RELEASE SAVEPOINT two;
SAVEPOINT three;
SAVEPOINT four;
INSERT INTO foo VALUES (2);
! 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,147 ****
SAVEPOINT one;
SELECT foo;
ERROR: column "foo" does not exist
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
--- 140,147 ----
SAVEPOINT one;
SELECT foo;
ERROR: column "foo" does not exist
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
***************
*** 150,156 ****
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO five;
COMMIT;
COMMIT; -- should not be in a transaction block
WARNING: there is no transaction in progress
--- 150,156 ----
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO SAVEPOINT five;
COMMIT;
COMMIT; -- should not be in a transaction block
WARNING: there is no transaction in progress
***************
*** 165,171 ****
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
--- 165,171 ----
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
***************
*** 200,206 ****
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
--- 200,206 ----
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE SAVEPOINT one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
***************
*** 221,227 ****
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
--- 221,227 ----
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
***************
*** 244,250 ****
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
--- 244,250 ----
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
***************
*** 266,274 ****
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
--- 266,274 ----
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
***************
*** 282,291 ****
-- 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
-- Only "rollback to" allowed in aborted state
BEGIN;
SAVEPOINT one;
--- 282,291 ----
-- only in a transaction block:
SAVEPOINT one;
ERROR: SAVEPOINT 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,301 ****
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 ...
ERROR: current transaction is aborted, commands ignored until end of transaction block
! ROLLBACK TO one;
SELECT 1;
?column?
----------
--- 293,301 ----
ERROR: division by zero
SAVEPOINT two; -- ignored till the end of ...
ERROR: current transaction is aborted, commands ignored until end of transaction block
! RELEASE SAVEPOINT one; -- ignored till the end of ...
ERROR: current transaction is aborted, commands ignored until end of transaction block
! ROLLBACK TO SAVEPOINT one;
SELECT 1;
?column?
----------
***************
*** 328,334 ****
9
(10 rows)
! ROLLBACK TO one;
FETCH 10 FROM c;
unique2
---------
--- 328,334 ----
9
(10 rows)
! ROLLBACK TO SAVEPOINT one;
FETCH 10 FROM c;
unique2
---------
***************
*** 344,350 ****
19
(10 rows)
! RELEASE one;
FETCH 10 FROM c;
unique2
---------
--- 344,350 ----
19
(10 rows)
! RELEASE SAVEPOINT one;
FETCH 10 FROM c;
unique2
---------
***************
*** 365,376 ****
SAVEPOINT two;
FETCH 10 FROM c;
ERROR: division by zero
! ROLLBACK TO two;
-- c is now dead to the world ...
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
! ROLLBACK TO two;
! RELEASE two;
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
COMMIT;
--- 365,376 ----
SAVEPOINT two;
FETCH 10 FROM c;
ERROR: division by zero
! ROLLBACK TO SAVEPOINT two;
-- c is now dead to the world ...
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
! ROLLBACK TO SAVEPOINT two;
! RELEASE SAVEPOINT two;
FETCH 10 FROM c;
ERROR: portal "c" cannot be run
COMMIT;
Index: src/test/regress/sql/transactions.sql
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/test/regress/sql/transactions.sql,v
retrieving revision 1.7
diff -u -c -r1.7 transactions.sql
*** src/test/regress/sql/transactions.sql 27 Jul 2004 05:11:48 -0000 1.7
--- src/test/regress/sql/transactions.sql 12 Aug 2004 11:15:40 -0000
***************
*** 64,74 ****
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
--- 64,74 ----
SAVEPOINT one;
DROP TABLE foo;
CREATE TABLE bar (a int);
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE baz (a int);
! RELEASE SAVEPOINT two;
drop TABLE foobar;
CREATE TABLE barbaz (a int);
COMMIT;
***************
*** 83,99 ****
INSERT INTO foo VALUES (1);
SAVEPOINT one;
INSERT into bar VALUES (1);
! ROLLBACK TO one;
! RELEASE 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;
INSERT INTO foo VALUES (3);
COMMIT;
SELECT * FROM foo; -- should have 1 and 3
--- 83,99 ----
INSERT INTO foo VALUES (1);
SAVEPOINT one;
INSERT into bar VALUES (1);
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
INSERT into barbaz VALUES (1);
! RELEASE SAVEPOINT two;
SAVEPOINT three;
SAVEPOINT four;
INSERT INTO foo VALUES (2);
! 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,110 ****
BEGIN;
SAVEPOINT one;
SELECT foo;
! ROLLBACK TO one;
! RELEASE one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
--- 103,110 ----
BEGIN;
SAVEPOINT one;
SELECT foo;
! ROLLBACK TO SAVEPOINT one;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
CREATE TABLE savepoints (a int);
SAVEPOINT three;
***************
*** 113,119 ****
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO five;
COMMIT;
COMMIT; -- should not be in a transaction block
SELECT * FROM savepoints;
--- 113,119 ----
INSERT INTO savepoints VALUES (2);
SAVEPOINT five;
INSERT INTO savepoints VALUES (3);
! ROLLBACK TO SAVEPOINT five;
COMMIT;
COMMIT; -- should not be in a transaction block
SELECT * FROM savepoints;
***************
*** 122,128 ****
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
--- 122,128 ----
BEGIN;
SAVEPOINT one;
DELETE FROM savepoints WHERE a=1;
! RELEASE SAVEPOINT one;
SAVEPOINT two;
DELETE FROM savepoints WHERE a=1;
SAVEPOINT three;
***************
*** 145,151 ****
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
--- 145,151 ----
INSERT INTO savepoints VALUES (6);
SAVEPOINT one;
INSERT INTO savepoints VALUES (7);
! RELEASE SAVEPOINT one;
INSERT INTO savepoints VALUES (8);
COMMIT;
-- rows 6 and 8 should have been created by the same xact
***************
*** 157,163 ****
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
--- 157,163 ----
INSERT INTO savepoints VALUES (9);
SAVEPOINT one;
INSERT INTO savepoints VALUES (10);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (11);
COMMIT;
SELECT a FROM savepoints WHERE a in (9, 10, 11);
***************
*** 170,176 ****
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
--- 170,176 ----
INSERT INTO savepoints VALUES (13);
SAVEPOINT two;
INSERT INTO savepoints VALUES (14);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (15);
SAVEPOINT two;
INSERT INTO savepoints VALUES (16);
***************
*** 185,193 ****
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
--- 185,193 ----
INSERT INTO savepoints VALUES (19);
SAVEPOINT two;
INSERT INTO savepoints VALUES (20);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (21);
! ROLLBACK TO SAVEPOINT one;
INSERT INTO savepoints VALUES (22);
COMMIT;
SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22;
***************
*** 196,211 ****
-- only in a transaction block:
SAVEPOINT one;
! ROLLBACK TO one;
! RELEASE 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;
SELECT 1;
COMMIT;
SELECT 1; -- this should work
--- 196,211 ----
-- only in a transaction block:
SAVEPOINT 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 SAVEPOINT one; -- ignored till the end of ...
! ROLLBACK TO SAVEPOINT one;
SELECT 1;
COMMIT;
SELECT 1; -- this should work
***************
*** 215,233 ****
DECLARE c CURSOR FOR SELECT unique2 FROM tenk1;
SAVEPOINT one;
FETCH 10 FROM c;
! ROLLBACK TO one;
FETCH 10 FROM c;
! RELEASE 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;
-- c is now dead to the world ...
FETCH 10 FROM c;
! ROLLBACK TO two;
! RELEASE two;
FETCH 10 FROM c;
COMMIT;
--- 215,233 ----
DECLARE c CURSOR FOR SELECT unique2 FROM tenk1;
SAVEPOINT one;
FETCH 10 FROM c;
! ROLLBACK TO SAVEPOINT one;
FETCH 10 FROM c;
! 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 SAVEPOINT two;
-- c is now dead to the world ...
FETCH 10 FROM c;
! ROLLBACK TO SAVEPOINT two;
! RELEASE SAVEPOINT two;
FETCH 10 FROM c;
COMMIT;
Oliver Jowett <oliver@opencloud.com> writes:
> Here's an updated patch that supports the syntax you suggest. I kept the
> error messages, doc examples and regression tests using RELEASE
> SAVEPOINT in full to follow the standard.
As long as we're tweaking syntax to agree with the spec ...
I notice that both SQL99 and SQL2003 require commas between the options
for START TRANSACTION and SET TRANSACTION, whereas our current grammar
has no commas. I propose that we fix the grammar to allow optional
commas there, to support the standard syntax without breaking existing
apps.
Unless someone objects, I'll add this to Oliver's patch and commit.
regards, tom lane
Oliver Jowett <oliver@opencloud.com> writes:
> Here's an updated patch that supports the syntax you suggest. I kept the
> error messages, doc examples and regression tests using RELEASE
> SAVEPOINT in full to follow the standard.
Applied. I had to tweak the grammar changes a bit --- the patch as
given created shift/reduce warnings, which we have a project policy
against allowing.
regards, tom lane