The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

vsql transaction model

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

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

  • Jim_KnicelyJim_Knicely Administrator

    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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.