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;
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
