On 2022-Apr-20, David G. Johnston wrote:
> v0001-doc-savepoint-name-reuse (-docs, reply to user request for
> improvement)
> https://www.postgresql.org/message-id/CAKFQuwYzSb9OW5qTFgc0v9RWMN8bX83wpe8okQ7x6vtcmfA2KQ%40mail.gmail.com
This one is incorrect; rolling back to a savepoint does not remove the
savepoint, so if you ROLLBACK TO it again afterwards, you'll get the
same one again. In fact, Your proposed example doesn't work as your
comments intend.
The way to get the effect you show is to first RELEASE the second
savepoint, then roll back to the earliest one. Maybe like this:
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
ROLLBACK TO SAVEPOINT my_savepoint;
SELECT * FROM table1; -- shows rows 1, 2
RELEASE SAVEPOINT my_savepoint; -- gets rid of the latest one without rolling back anything
ROLLBACK TO SAVEPOINT my_savepoint; -- rolls back to the earliest one
SELECT * FROM table1; -- just 1
COMMIT;
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
