How do I list my savepoints?

Moderator: NorbertKrupa

Post Reply
User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

How do I list my savepoints?

Post by piglet » Thu Nov 15, 2012 8:32 pm

Hi guys,

I'm running a bunch of transactions separated by save points. Is there a way that I can list what save points are active?

Code: Select all

dbadmin=> insert into t values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> savepoint t1;
SAVEPOINT
dbadmin=> insert into t values (2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> savepoint t2;
SAVEPOINT
dbadmin=> insert into t values (3);
 OUTPUT
--------
      1
(1 row)

dbadmin=> savepoint t3;
 OUTPUT
--------
      1
(1 row)
Is there some sort of SHOW SAVEPOINTS command?

Gracias!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How do I list my savepoints?

Post by JimKnicely » Mon Nov 26, 2012 3:09 pm

Hi piglet,

I do not think it's possible to list save points directly in vsql.

The only thing I can think that may help is to list your command history in vsql using the \s meta-command. Perhaps you can peruse the output from the command to decipher which save points are available...

For example, if I run this SQL:

Code: Select all

dbadmin=> CREATE TABLE t1 (c1 varchar(1));
CREATE TABLE
dbadmin=> INSERT INTO t1 VALUES ('A');
 OUTPUT
--------
      1
(1 row)

dbadmin=> SAVEPOINT t1;
SAVEPOINT
dbadmin=> INSERT INTO t1 VALUES ('B');
 OUTPUT
--------
      1
(1 row)

dbadmin=> SAVEPOINT t2;
SAVEPOINT
dbadmin=> INSERT INTO t1 VALUES ('C');
 OUTPUT
--------
      1
(1 row)

dbadmin=> ROLLBACK TO SAVEPOINT t2;
ROLLBACK
dbadmin=> SELECT * FROM t1;
 c1
----
 A
 B
(2 rows)
Now I can review my command history with the \s meta-command:

Code: Select all

dbadmin=> \s
CREATE TABLE t1 (c1 varchar(1));
INSERT INTO t1 VALUES ('A');
SAVEPOINT t1;
INSERT INTO t1 VALUES ('B');
SAVEPOINT t2;
INSERT INTO t1 VALUES ('C');
ROLLBACK TO SAVEPOINT t2;
SELECT * FROM t1;
\s
By reviewing the output of the \s meta-command I can see that my t1 save point is still available...

Hope this helps a little!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
piglet
Beginner
Beginner
Posts: 45
Joined: Tue Feb 07, 2012 4:04 pm

Re: How do I list my savepoints?

Post by piglet » Tue Nov 27, 2012 3:30 pm

Thanks, Jim. It'd be cool if save point info was stored in a system table so that we could access it. But I don't think there is any database out there that gives us that.

Post Reply

Return to “New to Vertica SQL”