From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Assorted small doc patches |
Date: | 2022-04-21 19:15:51 |
Message-ID: | CAKFQuwZXOK2r1bn=Brb=WseWvUAa+D3aXy5sV6NMwQUQ_pVq=g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2022-04-21 19:25:11 | Re: preserving db/ts/relfilenode OIDs across pg_upgrade (was Re: storing an explicit nonce) |
Previous Message | Tom Lane | 2022-04-21 19:03:33 | Re: Assert failure in CTE inlining with view and correlated subquery |