AUTOCOMMIT Variable Overrides COPY Command NO COMMIT Option!

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

AUTOCOMMIT Variable Overrides COPY Command NO COMMIT Option!

Post by JimKnicely » Fri Feb 15, 2013 8:21 pm

Something to keep in mind...

According to the documentation, the NO COMMIT option of the COPY command "Prevents the COPY statement from committing its transaction automatically when it finishes copying data."

However, that's not the case if the AUTOCOMMIT variable is set to 'on'...

Example:

I have a table named JULES that I will load 3 records into using the COPY command with the NO COMMIT option after setting the AUTOCOMMIT variable to 'on'...

Code: Select all

dbadmin=> \echo :AUTOCOMMIT
off
dbadmin=> \! cat /usr/home/dbadmin/j.txt
1
2
3
dbadmin=> SELECT * FROM jules;
 c
---
(0 rows)

dbadmin=> \set AUTOCOMMIT on
dbadmin=> \echo :AUTOCOMMIT
on
dbadmin=> COPY jules FROM '/usr/home/dbadmin/j.txt' NO COMMIT;
 Rows Loaded
-------------
           3
(1 row)

dbadmin=> SELECT * FROM jules;
 c
---
 3
 1
 2
(3 rows)
I then exit out of vsql and immediately jump back in:

Code: Select all

dbadmin=> \q
bash-3.2$ vsql
SET
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=>
Next I select from the table and find that the rows were committed even though I specified in the COPY command not to :roll:

Code: Select all

dbadmin=> SELECT * FROM jules;
 c
---
 3
 1
 2
(3 rows)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1804
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: AUTOCOMMIT Variable Overrides COPY Command NO COMMIT Opt

Post by JimKnicely » Fri Feb 15, 2013 8:21 pm

UPDATE...

I just read this statement in the 6.1 Programmer's Guide from page 236:
The COPY statement, by default, commits on completion, so it does not matter which AUTOCOMMIT mode you use, unless you issue COPY NO COMMIT.
The "...so it does not matter which AUTOCOMMIT mode you use..." part is incorrect - IMHO.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”