描述

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 允許省略它。

另請參閱