AUTOCOMMIT Variable Overrides COPY Command NO COMMIT Option!

mo

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’…

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:

dbadmin=> \q
bash-3.2$ 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
dbadmin=>

Next I select from the table and find that the rows were committed even though I specified in the COPY command not to do that!

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

Have fun!

Twitter Digg Delicious Stumbleupon Technorati Facebook Email

No comments yet... Be the first to leave a reply!

Leave a Reply

You must be logged in to post a comment.