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. 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;
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.