vsql transaction model

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 ...

I might add:

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

Oh, and COPY.

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)