SAVEPOINT

SAVEPOINT — Define a new savepoint within the current transaction

Synopsis

SAVEPOINT savepoint_name

Description

SAVEPOINT establishes a new savepoint within the current transaction.

A savepoint is a special marker within a transaction that allows all commands executed after it was established to be rolled back, restoring the transaction's state to what it was at the savepoint.

Parameters

savepoint_name

The name to give to the new savepoint.

Notes

Use ROLLBACK TO SAVEPOINT to roll back to a savepoint. Use RELEASE SAVEPOINT to destroy a savepoint while keeping the effects of commands executed after it was established.

Savepoints can only be established within a transaction block. Multiple savepoints can be defined within a single 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;

-- The above transaction will insert 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;

-- The above transaction will insert 3 and 4.

See Also

BEGIN, COMMIT, RELEASE SAVEPOINT, ROLLBACK, ROLLBACK TO SAVEPOINT