Overriding COPY Auto Commit

By default, COPY automatically commits itself and other current transactions except when loading temporary tables. You can override this behavior by qualifying the COPY statement with the NO COMMIT option. When you specify NO COMMIT, Vertica does not commit the transaction until you explicitly issue a COMMIT statement.

You can use COPY...NO COMMIT in two ways:

Combine Multiple COPY Statements in Same Transaction

When you combine multiple COPY...NO COMMIT statements in the same transaction, Vertica can consolidate the data for all operations into fewer ROS containers, and thereby perform more efficiently.

For example, the following set of COPY...NO COMMIT statements performs several copy statements sequentially, and then commits them all. In this way, all of the copied data is either committed or rolled back as a single transaction. 

COPY... NO COMMIT;    
COPY... NO COMMIT;
COPY... NO COMMIT;
COPY X FROM LOCAL NO COMMIT;
COMMIT;

Tip: Vertica recommends that you COMMIT or ROLLBACK the current transaction before you use COPY...NO COMMIT. Otherwise, if a previous operation such as INSERT is in progress, COPY...NO COMMIT adds that operation to its own transaction. In this case, the previous operation and copy operation are combined as a single transaction, which requires an explicit COMMIT statement.

Check Constraint Violations

Unless you have enabled enforcement of primary key, unique, or check constraints, Vertica does not check for constraint violations when loading data. You can use COPY...NO COMMIT to troubleshoot loaded data for constraint violations. Before committing the load, test the data with ANALYZE_CONSTRAINTS. If you find any constraint violations, you can roll back the load.

For details on ANALYZE_CONSTRAINTS, see Detecting Constraint Violations with ANALYZE_CONSTRAINTS.

For details on automatic enforcement of primary key, unique, and check constraints, see Enforcing Primary Key, Unique Key, and Check Constraints Automatically.