vsql transaction model

Comments

• marcothesane - Select Field - Administrator

  January 2019

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

  0
• marcothesane - Select Field - Administrator

  January 2019

  I might add:

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

  0
• marcothesane - Select Field - Administrator

  January 2019

  Oh, and COPY.

  0
• Jim_Knicely - Select Field - Administrator

  January 2019

  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)
  

  0