Using transactions to stage a load

By default, COPY automatically commits itself and other current transactions except when loading temporary tables or querying external tables.

By default, COPY automatically commits itself and other current transactions except when loading temporary tables or querying external 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:

Execute multiple COPY commands as a single transaction.
Check data for constraint violations before committing the load.

Combine multiple COPY statements in the 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

Be sure to commit or roll back any previous DML operations before you use COPY...NO COMMIT. Otherwise, COPY...NO COMMIT is liable to include earlier operations that are still in progress, such as INSERT, in its own transaction. In this case, the previous operation and copy operation are combined as a single transaction and committed together.

Check constraint violations

If constraints are not enforced in the target table, COPY does not check for constraint violations when it loads data. To troubleshoot loaded data for constraint violations, use COPY...NO COMMIT with ANALYZE_CONSTRAINTS. Doing so enables you detect constraint violations before you commit the load operation and, if necessary, roll back the operation. For details, see Detecting constraint violations.