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

Making use of SAVEPOINT

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=# SELECT 2; ERROR: current transaction is aborted, commands ignored until end of transaction block
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 SAVEPOINT to make sure that the application can always return to this point inside the transaction. As you can see, SAVEPOINT has a name, which is referred to later.

 After returning the savepoint called 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 this.

If you want to remove a SAVEPOINT from inside a transaction, there is a 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, 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.

主站蜘蛛池模板: 繁昌县| 类乌齐县| 六枝特区| 澜沧| 永清县| 浦江县| 海城市| 茶陵县| 邵东县| 保康县| 宝清县| 冷水江市| 阿鲁科尔沁旗| 花莲县| 巨野县| 长泰县| 湟中县| 禹城市| 南投县| 枣阳市| 台北县| 石景山区| 邹城市| 巴中市| 漳州市| 南部县| 黑水县| 江油市| 岚皋县| 白朗县| 林周县| 木兰县| 汉沽区| 洪雅县| 梁山县| 高密市| 兴国县| 舒兰市| 光山县| 克东县| 琼海市|