描述

RELEASE SAVEPOINT releases the named savepoint and all active savepoints that were created after the named savepoint, and frees their resources. All changes made since the creation of the savepoint that didn't already get rolled back are merged into the transaction or savepoint that was active when the named savepoint was created. Changes made after RELEASE SAVEPOINT will also be part of this active transaction or savepoint.

参数

注释

指定一个先前未定义的保存点名称将导致错误。

当事务处于中止状态时，无法释放保存点；要做到这一点，请使用 ROLLBACK TO SAVEPOINT。

如果多个保存点具有相同的名称，则只释放最近定义的、尚未释放的一个。重复的命令将释放更旧的保存点。

示例

建立并稍后释放一个保存点

BEGIN;
    INSERT INTO table1 VALUES (3);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (4);
    RELEASE SAVEPOINT my_savepoint;
COMMIT;

上面的事务将插入 3 和 4。

一个更复杂的示例，包含多个嵌套的子事务

BEGIN;
    INSERT INTO table1 VALUES (1);
    SAVEPOINT sp1;
    INSERT INTO table1 VALUES (2);
    SAVEPOINT sp2;
    INSERT INTO table1 VALUES (3);
    RELEASE SAVEPOINT sp2;
    INSERT INTO table1 VALUES (4))); -- generates an error

在此示例中，应用程序请求释放保存点 sp2，该保存点插入了 3。这会将插入的事务上下文更改为 sp1。当试图插入值 4 的语句出现错误时，插入 2 和 4 的操作将丢失，因为它们与已回滚的保存点在同一个事务中，而值 3 也在同一个事务上下文中。应用程序现在只能选择以下两个命令之一，因为所有其他命令都将被忽略。

ROLLBACK;
ROLLBACK TO SAVEPOINT sp1;

选择 ROLLBACK 将中止所有操作，包括值 1；而 ROLLBACK TO SAVEPOINT sp1 将保留值 1 并允许事务继续。

兼容性

此命令符合SQL标准。标准规定关键字 SAVEPOINT 是强制性的，但 PostgreSQL 允许省略它。

另请参阅