Re: Assorted small doc patches

On Thu, Apr 21, 2022 at 10:46 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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.