Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Is commit still necessary when using INSERT /*+direct*/ INTO ... ?

I know it sounds silly, but the keyword "direct" gives me the feeling that I don't need to commit.

Best Answer

  • Bryan_HBryan_H Administrator
    Answer ✓

    There is a possible exception: all drivers set autocommit=on, EXCEPT vsql. Please check whether vsql sets autocommit with SHOW ALL. The default is autocommit=off:

    d2=> show all;
                name             |                          setting
    -----------------------------+-----------------------------------------------------------
     locale                      | [email protected]=binary (LEN_KBINARY)
     autocommit                  | off
    

    You can enable autocommit with "set session autocommit to on;"
    You can also enable autocommit by creating a config file "~/.vsqlrc" and add the line "set session autocommit to on;" to the file so it will run at every startup.

Answers

  • Hello,
    the keyword "direct" is/was useful up to version 9.3.x to avoid using WOS in transaction (write direct to disk and not in memory/WOS)

    You do need a commit in any case after an insert

    If you guess that you do not need a commit it may be because your session is open with "autocommit=on" or if you use another instruction with implicit commit (copy /truncate) following the insert

    Regards

  • moshegmosheg Administrator
    edited November 23

    And if you run "unnecessary" COMMIT when the previous statement(s) were auto-committed you will get
    INFO 2372: Cannot commit; no transaction in progress

    BTW, when you combine several INSERT or COPY statements with "NO COMMIT" in the same transaction,
    the data is consolidated into fewer Ros containers which is more efficient.

  • jiahe1224jiahe1224
    edited November 23

    Thanks everyone, I now know that "direct" is only related to ROS, I did turn on autocommit, but it seems that sometimes it has a problem, or there is a problem with the bash script, I haven't reproduced it yet, I will update when it can be reproduced.
    We are currently using Vertica Analytic Database v9.3.1-25.

  • I executed "set session autocommit to on", then I execute "show all", "autocommit" is still off, do I need to execute "set session autocommit to on" or "\set AUTOCOMMIT on" before each insert?

  • marcothesanemarcothesane Administrator

    What are you doing differently from me, then?
    See here:

    marco ~/1/Vertica/sql/demos $ 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
    sbx=> SET SESSION AUTOCOMMIT TO ON ;
    SET
     sbx=> INSERT INTO  foo VALUES ('Trillian');
     OUTPUT 
    --------
          1
    (1 row)
    -- If I fire a COMMIT now and get a warning that no transaction is in progress, 
    -- then the single-row-insert above has been auto-committed
    COMMIT;
    sbx=> COMMIT;
    INFO 2372:  Cannot commit; no transaction in progress
    COMMIT
    sbx=>SHOW autocommit;
        name    | setting 
    ------------+---------
     autocommit | on
    

    Could be something as trivial as having omitted the semicolon at the end of the SET SESSION command?

  • SruthiASruthiA Employee

    @jiahe1224 I just tested the set session autocommit on behaviour you mentioned in 12.0.1 which is latest version.. I see that show all shows that autocommit is on and is working as expected. Can you share screenshot from your environment?

  • Thank you, it is my problem, I forgot to add ';' when executing in vsql :s

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.