官术网_书友最值得收藏!

Making use of savepoints

In professional applications, it can be pretty hard to write reasonably long transactions without ever encountering a single error. To solve the problem, users can utilize something called SAVEPOINT. As the name indicates, it is a safe place inside a transaction that the application can return to in the event things go terribly wrong. Here is an example:

test=# BEGIN; 
BEGIN
test=# SELECT 1;
?column?
----------
1
(1 row)

test=# SAVEPOINT a;
SAVEPOINT
test=# SELECT 2 / 0;
ERROR: division by zero
test=# ROLLBACK TO SAVEPOINT a;
ROLLBACK
test=# SELECT 3;
?column?
----------
3
(1 row)

test=# COMMIT;
COMMIT

After the first SELECT clause, I decided to create a SAVEPOINT to make sure that the application can always return to this point inside the transaction. As you can see, a SAVEPOINT has a name, which is referred to later.

After returning to a, the transaction can proceed normally. The code has jumped back before the error, so everything is fine.

The number of savepoints inside a transaction is practically unlimited. We have seen customers with over 250,000 savepoints in a single operation. PostgreSQL can easily handle that.

If you want to remove a savepoint from inside a transaction, there is RELEASE SAVEPOINT:

test=# h RELEASE SAVEPOINT  
Command: RELEASE SAVEPOINT
Description: destroy a previously defined savepoint
Syntax:
RELEASE [ SAVEPOINT ] savepoint_name

Many people ask, What will happen if you try to reach a savepoint after a transaction has ended? The answer is that the life of a savepoint ends as soon as the transaction ends. In other words, there is no way to return to a certain point in time after the transactions have been completed.

主站蜘蛛池模板: 凌源市| 乌兰察布市| 永福县| 梧州市| 寿宁县| 墨江| 邵东县| 忻州市| 福贡县| 桓台县| 公安县| 凤庆县| 吉林市| 靖安县| 博白县| 黄浦区| 卓尼县| 木兰县| 香港 | 石嘴山市| 南雄市| 交城县| 青川县| 郁南县| 镇平县| 辽源市| 公主岭市| 双柏县| 海门市| 南召县| 读书| 甘泉县| 北川| 乌拉特后旗| 桃江县| 吴川市| 靖宇县| 嘉峪关市| 萝北县| 石家庄市| 肃宁县|