vsql transaction model

john8john8 Registered User

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?

Comments

  • marcothesanemarcothesane Employee, Registered User ✭✭

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

  • marcothesanemarcothesane Employee, Registered User ✭✭

    I might add:

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

  • marcothesanemarcothesane Employee, Registered User ✭✭

    Oh, and COPY.

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert admin

    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
    
    [[email protected] ~]$ 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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file