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

jiahe1224jiahe1224 Vertica Customer

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 Vertica Employee 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                      | en_US@collation=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.


  • FrancoisFrancois - Select Field - Employee

    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


  • moshegmosheg Vertica Employee Administrator
    edited November 2022

    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 Vertica Customer
    edited November 2022

    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.

  • jiahe1224jiahe1224 Vertica Customer

    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 - Select Field - 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=> INSERT INTO  foo VALUES ('Trillian');
    (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
    sbx=> COMMIT;
    INFO 2372:  Cannot commit; no transaction in progress
    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 Vertica Employee Administrator

    @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?

  • jiahe1224jiahe1224 Vertica Customer

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

Leave a Comment

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