rollback_to_savepoint(7)

1ROLLBACK TO SAVEPOINT()          SQL Commands          ROLLBACK TO SAVEPOINT()
2
3
4

NAME

6       ROLLBACK TO SAVEPOINT - roll back to a savepoint
7
8

SYNOPSIS

10       ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
11
12

DESCRIPTION

14       Roll  back  all  commands  that  were  executed after the savepoint was
15       established. The savepoint remains valid and  can  be  rolled  back  to
16       again later, if needed.
17
18       ROLLBACK  TO  SAVEPOINT  implicitly  destroys  all savepoints that were
19       established after the named savepoint.
20

PARAMETERS

22       savepoint_name
23              The savepoint to roll back to.
24

NOTES

26       Use RELEASE SAVEPOINT [release_savepoint(7)]  to  destroy  a  savepoint
27       without discarding the effects of commands executed after it was estab‐
28       lished.
29
30       Specifying a savepoint name that has not been established is an error.
31
32       Cursors have somewhat non-transactional behavior with respect to  save‐
33       points.  Any  cursor  that  is opened inside a savepoint will be closed
34       when the savepoint is rolled back. If a  previously  opened  cursor  is
35       affected  by  a  FETCH  command inside a savepoint that is later rolled
36       back, the cursor position remains at the position that  FETCH  left  it
37       pointing  to  (that is, FETCH is not rolled back).  Closing a cursor is
38       not undone by rolling back, either.  A cursor whose execution causes  a
39       transaction  to  abort  is  put  in a can't-execute state, so while the
40       transaction can be restored using ROLLBACK TO SAVEPOINT, the cursor can
41       no longer be used.
42

EXAMPLES

44       To  undo  the  effects  of the commands executed after my_savepoint was
45       established:
46
47       ROLLBACK TO SAVEPOINT my_savepoint;
48
49
50       Cursor positions are not affected by savepoint rollback:
51
52       BEGIN;
53
54       DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
55
56       SAVEPOINT foo;
57
58       FETCH 1 FROM foo;
59        ?column?
60       ----------
61               1
62
63       ROLLBACK TO SAVEPOINT foo;
64
65       FETCH 1 FROM foo;
66        ?column?
67       ----------
68               2
69
70       COMMIT;
71
72

COMPATIBILITY

74       The SQL standard specifies that the key word  SAVEPOINT  is  mandatory,
75       but PostgreSQL and Oracle allow it to be omitted. SQL allows only WORK,
76       not TRANSACTION, as a noise word  after  ROLLBACK.  Also,  SQL  has  an
77       optional  clause  AND  [ NO ] CHAIN which is not currently supported by
78       PostgreSQL. Otherwise, this command conforms to the SQL standard.
79