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

jiahe1224

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

Bryan_H

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.

Francois

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

mosheg

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.

jiahe1224

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.

jiahe1224

@Bryan_H

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?

marcothesane

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?

SruthiA

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

jiahe1224

@SruthiA @marcothesane

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