Is commit still necessary when using INSERT /*+direct*/ INTO ... ?
jiahe1224
Vertica Customer ✭
I know it sounds silly, but the keyword "direct" gives me the feeling that I don't need to commit.
1
Best Answer
-
Bryan_H
Vertica Employee Administrator
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 | offYou 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.0
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
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.
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?
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 | onCould be something as trivial as having omitted the semicolon at the end of the SET SESSION command?
@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