On Thu, Apr 21, 2022 at 10:46 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote:
> 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; David J.
