PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Does rolling back to a savepoint release interim savepoints?

I’m studying Postgres by reading the docs, and section 3.4 says this:

"Keep in mind that either releasing or rolling back to a savepoint will automatically release all savepoints that were defined after it.

https://postgresql.org/docs/current/tutorial-transactions.html

Surely they mean "will automatically release OR ROLLBACK all savepoints"?

It would make no sense to release (which means commit) a savepoint that you’re rolling back past, right?

Tags:

2

Answers


  1. 0

    Releasing a savepoint does not alter table data nor does it undo any work that is part of the current transaction: it just discards the information necessary to ROLLBACK to that savepoint. Any savepoints that were created after the released savepoint are also released.

    Performing a ROLLBACK TO SAVEPOINT effectively restores the current transaction to the state that existed when the savepoint was defined; therefore, savepoints that were created after the ROLLBACK savepoint are released because the transaction is no longer capable of restoring the state of those savepoints.

    Performing a COMMIT releases all savepoints.

    Login or Signup to reply.
  2. 0

    They don’t mean rollback as that would undo whatever happened in between the later savepoints, which it does not when you just release an earlier savepoint.

    The idea that release is a "subtransaction commit" is at least an oversimplification. You don’t really need to release savepoints to have their changes contribute to the transaction. Releasing a savepoint means you just no longer need the option to go that far back in time, which implies you also no longer need to be able to go any less back in time than that.
    If you’re satisfied with the work you’ve been doing for the past hour, it’s understood that you’re also ok with what you did 15 or 20 minutes ago. Undoing the past hour, also undoes stuff from 15 minutes ago.

    Automatically releasing the in-between savepoints makes sense: you can’t rollback forward to a savepoint you just went past. Why keep it?

    Consider this: demo at db<>fiddle

    begin;

insert into t values(1);
savepoint s1;
insert into t values(2);
savepoint s2;
insert into t values(3);
rollback to savepoint s1;

    Once you went back to s1, a rollback to savepoint s2 would mean going forward:

    1. You’re trying to undo what you did after you went back to s1 and instead re-do what you had done originally.
    2. You’re trying to replay s2 operations on top of what you just did.

    There are ways either of these could be implemented, but currently that only works in one direction, and undoing an undo just requires a re-do.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search