On Sun, May 27, 2018 at 6:04 PM, Stuart McGraw <smcg4191@xxxxxxxxxx> 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
