Using Transactions to Stage a Load
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
You can use
COPY…NO COMMIT in two ways:
- Execute multiple
COPYcommands 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.