SAVEPOINT保存点

SAVEPOINT

保存点仅在当前事物中起作用。
A savepoint is a special mark inside a transaction that allows all commands that are executed after it was established to be rolled back, restoring the transaction state to what it was at the time of the savepoint.


PG支持保存点，在一个大事物中，可以把操作过程分成几部分，第一个部分成功后，可以建一个保存点，若后面的执行失败，则回滚到这个保存点，而不必把整个事物都回滚掉。

highgo=# begin;
BEGIN
highgo=# select * from test;
 id | name 
----+------
  1 | 
(1 row)


highgo=# insert into test values (1);
INSERT 0 1
highgo=# insert into test values (2);
INSERT 0 1
highgo=# savepoint test_savepoint_01;
SAVEPOINT
highgo=# 
highgo=# insert into test values (3);
INSERT 0 1
highgo=# select * from test;
 id | name 
----+------
  1 | 
  1 | 
  2 | 
  3 | 
(4 rows)


highgo=# rollback to savepoint test_savepoint_01;
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
  1 | 
  1 | 
  2 | 
(3 rows)


highgo=# 


highgo=# insert into test values (3);
INSERT 0 1
highgo=# select * from test;
 id | name 
----+------
  1 | 
  1 | 
  2 | 
  3 | 
(4 rows)




To establish and later destroy a savepoint:


highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# BEGIN;
BEGIN
highgo=#     INSERT INTO test VALUES (3);
INSERT 0 1
highgo=#     SAVEPOINT my_savepoint;
SAVEPOINT
highgo=#     INSERT INTO test VALUES (4);
INSERT 0 1
highgo=#     RELEASE SAVEPOINT my_savepoint;    --销毁savepoint
RELEASE
highgo=# COMMIT;
COMMIT
highgo=# select * from test;
 id | name 
----+------
  3 | 
  4 | 
(2 rows)


****************************************************************************************
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.


highgo=# begin;
BEGIN
highgo=# insert into test values (1);
INSERT 0 1
highgo=# savepoint my_savepoint;
SAVEPOINT
highgo=# insert into test values (2);
INSERT 0 1
highgo=# rollback to savepoint my_savepoint;
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
  1 | 
(1 row)


highgo=# insert into test values (2);
INSERT 0 1
highgo=# insert into test values ('2');
INSERT 0 1
highgo=# insert into test values ('aaa');
错误:  无效的整数类型输入语法: "aaa"
LINE 1: insert into test values ('aaa');
                                 ^
highgo=# select * from test;
错误:  当前事务被终止, 事务块结束之前的查询被忽略
highgo=# rollback to savepoint my_savepoint;                 --在一个事物中，一条语句执行失败，有savepoint的情况下可以rollback到sacepoint；没有saveepoint则回退所有事物
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
  1 | 
(1 row)
************************************************************************************
highgo=# begin;
BEGIN
highgo=# 
highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# insert into test values (1);
INSERT 0 1
highgo=# insert into test values (2);
INSERT 0 1
highgo=# insert into test values ('aaaa');
错误:  无效的整数类型输入语法: "aaaa"
LINE 1: insert into test values ('aaaa');
                                 ^
highgo=# select * from test;
错误:  当前事务被终止, 事务块结束之前的查询被忽略   
highgo=# rollback;
ROLLBACK
highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# 
----------------------------------------------
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.


highgo=# begin;
BEGIN
highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=# insert into test values (1);
INSERT 0 1
highgo=# insert into test values (2);
INSERT 0 1
highgo=# insert into test values ('aaaa');
错误:  无效的整数类型输入语法: "aaaa"
LINE 1: insert into test values ('aaaa');
                                 ^
highgo=# select * from test;
错误:  当前事务被终止, 事务块结束之前的查询被忽略
highgo=# 
highgo=# \q
[highgo@sourcedb ~]$ psql
psql (3.1.4)
Type "help" for help.


highgo=# select * from test;
 id | name 
----+------
(0 rows)


highgo=#