Postgresql 中文操作指南
SAVEPOINT
SAVEPOINT — 在当前事务中定义一个新的保存点
SAVEPOINT — define a new savepoint within the current transaction
Description
SAVEPOINT 在当前事务中建立一个新的保存点。
SAVEPOINT establishes a new savepoint within the current transaction.
保存点是事务中一个特殊的标记，允许在建立保存点后执行的所有命令回滚，将事务状态还原到保存点时的状态。
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.
Parameters
-
savepoint_name
-
The name to give to the new savepoint. If savepoints with the same name already exist, they will be inaccessible until newer identically-named savepoints are released.
-
Notes
使用 ROLLBACK TO 回滚到保存点。使用 RELEASE SAVEPOINT 销毁保存点，保留建立保存点后执行的命令的结果。
Use ROLLBACK TO to rollback to a savepoint. Use RELEASE SAVEPOINT to destroy a savepoint, keeping the effects of commands executed after it was established.
只在事务块内才能建立保存点。一个事务内可以定义多个保存点。
Savepoints can only be established when inside a transaction block. There can be multiple savepoints defined within a transaction.
Examples
要建立保存点并稍后撤销建立保存点后执行的所有命令的结果：
To establish a savepoint and later undo the effects of all commands executed after it was established:
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
COMMIT;上述事务将插入值 1 和 3，但不插入值 2。
The above transaction will insert the values 1 and 3, but not 2.
要建立并稍后销毁保存点：
To establish and later destroy a savepoint:
BEGIN;
INSERT INTO table1 VALUES (3);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (4);
RELEASE SAVEPOINT my_savepoint;
COMMIT;以上事务将插入 3 和 4。
The above transaction will insert both 3 and 4.
要使用一个保存点名称：
To use a single savepoint name:
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
-- rollback to the second savepoint
ROLLBACK TO SAVEPOINT my_savepoint;
SELECT * FROM table1; -- shows rows 1 and 2
-- release the second savepoint
RELEASE SAVEPOINT my_savepoint;
-- rollback to the first savepoint
ROLLBACK TO SAVEPOINT my_savepoint;
SELECT * FROM table1; -- shows only row 1
COMMIT;上述事务显示先回滚行 3，再回滚行 2。
The above transaction shows row 3 being rolled back first, then row 2.
Compatibility
SQL 要求在建立同名另一个保存点时自动销毁一个保存点。在 PostgreSQL 中，旧的保存点将得到保留，但在回滚或释放时只会使用较新的保存点。（使用 RELEASE SAVEPOINT 释放较新的保存点将导致较旧的保存点重新可供 ROLLBACK TO SAVEPOINT 和 RELEASE SAVEPOINT 使用。）否则， SAVEPOINT 完全符合 SQL 标准。
SQL requires a savepoint to be destroyed automatically when another savepoint with the same name is established. In PostgreSQL, the old savepoint is kept, though only the more recent one will be used when rolling back or releasing. (Releasing the newer savepoint with RELEASE SAVEPOINT will cause the older one to again become accessible to ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT.) Otherwise, SAVEPOINT is fully SQL conforming.