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.
Yeah, my bad for not testing things.
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;
I'm ok with that, though I decided to experiment a bit. I decided to use comments to make the example understandable without needing a server; self-contained AND easier to follow the status of both the table and the savepoint reference.
I explicitly demonstrate both release and rollback here along with the choice to use just a single savepoint name. We could make even more examples in a "unit test" type style but with the commentary I think this communicates the pertinent points quite well.
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
-- Savepoint: [1]; Table: [1]
INSERT INTO table1 VALUES (2);
SAVEPOINT my_savepoint;
-- Savepoint: [1,2]; Table: [1,2]
INSERT INTO table1 VALUES (3);
SAVEPOINT my_savepoint;
-- Savepoint: [1,2,3]; Table: [1,2,3]
INSERT INTO table1 VALUES (4);
-- Savepoint: [1,2,3]; Table: [1,2,3,4]
ROLLBACK TO SAVEPOINT my_savepoint;
-- Savepoint: [1,2,3]; Table: [1,2,3]
ROLLBACK TO SAVEPOINT my_savepoint; -- No Change
-- Savepoint: [1,2,3]; Table: [1,2,3]
SELECT * FROM table1;
RELEASE my_savepoint;
RELEASE my_savepoint;
-- Savepoint: [1]; Table: [1,2,3]
SELECT * FROM table1;
ROLLBACK TO SAVEPOINT my_savepoint;
-- Savepoint: [1]; Table: [1]
SELECT * FROM table1;
COMMIT;