vsql transaction model

john8

The vsql command-line linux utility seems to secretly commit some statements at end of session, but not all statements. CREATE TABLE and DROP TABLE seem to get committed (if you quit vsql and then start a new session, you see your previous changes), but INSERT and DELETE do not.

Is there documentation on what gets secretly committed and what doesn't? Does it depend only on the statement issued, or also what other transactions are in progress?

marcothesane

Dear John

In Vertica, we behave like any ANSI compliant database should:

• DDL (data definition language) statements commit any previously open transactions in the session and cannot be rolled back: CREATE, DROP, ALTER, TRUNCATE, for example.
• DML (data manipulation language) statements, if AUTOCOMMIT is off (as is the default in vsql), require an explicit COMMIT if you don't want them to be rolled back at the end of the session.

Hope this helps ...

marcothesane

I might add:

DML statements are: INSERT, UPDATE, DELETE and MERGE.

marcothesane

Oh, and COPY.

Jim_Knicely

Per @marcothesane comments, note that running a DDL operation (i.e. CREATE, DROP, ALTER, TRUNCATE, etc.) will commit any uncommitted DML operations (INSERT, UPDATE, DELETE, MERGE) statements in the transaction.

Example:

dbadmin=> INSERT INTO test SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> CREATE TABLE test2 (c INT);
CREATE TABLE

dbadmin=> INSERT INTO test SELECT 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> \q

[dbadmin@s18384357 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> SELECT * FROM test;
 c
---
 1
(1 row)