AUTOCOMMIT Variable Overrides COPY Command NO COMMIT Option!

[JimKnicely]

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

Code: Select all

dbadmin=> SELECT * FROM jules;
 c
---
 3
 1
 2
(3 rows)

Have fun!

[JimKnicely]

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.