Rollback COPY on ANY failure to load ALL records

usao

We are having an issue where the COPY command is not loading all records yet the rejected records are showing up in the logs rather than the base table. We need to find a way to get the COPY command to commit only when ALL records are loaded. Any failure to load should cause a rollback of the entire copy. Is that possible?

Ben_Vandiver

Use the REJECTMAX parameter.

See https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/CapturingLoadExceptionsAndRejections.htm

usao

It also looks like "ABORT ON ERROR" will do the same thing. Im not exactly sure what the difference is between REJECTMAX and "ABORT ON ERROR" though, so I may specify both.

Jim_Knicely

Hi @usao,

I believe for your case, where you said:

We need to find a way to get the COPY command to commit only when ALL records are loaded. Any failure to load should cause a rollback of the entire copy.

... "REJECTMAX 1" is the same as "ABORT ON ERROR", except for the error message...

Example:

dbadmin=> create table test (c1 int);
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/test.txt
1
2
3
A

dbadmin=> copy test from '/home/dbadmin/test.txt' rejectmax 1;
ERROR 7293:  COPY: [1] records have been rejected

dbadmin=> select * from test;
 c1
----
(0 rows)

dbadmin=> copy test from '/home/dbadmin/test.txt' abort on error;
ERROR 2035:  COPY: Input record 4 has been rejected (Invalid integer format 'A' for column 1 (c1))

dbadmin=> select * from test;
 c1
----
(0 rows)

usao

This has been resolved. The "ABORT ON ERROR" seems to do what I need.