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