Re: existence of a savepoint?

On Sun, May 27, 2018 at 6:04 PM, Stuart McGraw <smcg4191(at)mtneva(dot)com> wrote:

> Is there some way to to test if a savepoint of a given name
> exists? Or better yet, the number of stacked savepoints of
> that name?
>
>
This is kind of backwards, but you can determine if a savepoint by a
certain name exists by trying to release it. You can do so without
damaging the transaction by creating a savepoint immediately beforehand,
but this will cause you to lose the state of the named savepoint. Example
below.

=# begin;
BEGIN
=# insert into brian.test values (1);
INSERT 0 1
=# savepoint target_point;
SAVEPOINT
=# insert into brian.test values (2);
INSERT 0 1
=# savepoint buffer;
SAVEPOINT
=# release target_point;
RELEASE <------ savepoint existed
=# commit;
COMMIT

=# begin;
BEGIN
=# insert into brian.test values (3);
INSERT 0 1
=# savepoint buffer;
SAVEPOINT
=# release target_point;
ERROR: no such savepoint <----- savepoint did not exist
=# rollback to buffer;
ROLLBACK
=# select * from brian.test;
x
---
3

In theory you could do two savepoints "target_point" and
"target_point_test" and check for the _test version to maintain your
transaction states and be able to test for it once.

BEGIN
=# insert into brian.test values (1);
INSERT 0 1
=# savepoint target_point;
SAVEPOINT
=# savepoint target_point_test;
SAVEPOINT
=# insert into brian.test values (2);
INSERT 0 1
=# savepoint buffer;
SAVEPOINT
=# release target_point_test; <----- check if target_point exists
RELEASE
=# rollback to target_point; <----- rollback to it
ROLLBACK

Cheers,

-Brian Dunavant