17.00 - Other Restrictions on Atomic Upsert Feature - Parallel Data Pump

Teradata® Parallel Data Pump Reference

Parallel Data Pump
June 2020
Programming Reference

Some of these restrictions concern syntax that is supported in UPDATE and INSERT statements separately, but not when combined in an Atomic upsert statement. Other restrictions concern the upsert feature's not supporting certain database features such as triggers and join/hash indexes, meaning that the upsert statement cannot be used on any table utilizing those features.

The following restrictions are not supported by the Atomic upsert feature, and return an error if submitted to the database:
  • INSERT … SELECT: Syntax not supported. The INSERT may not use a subquery to specify any of the inserted values. Note that support of this syntax is likely to be linked to support of subqueries in the UPDATE's WHERE clause constraints as described above, and may involve new syntax features to allow the UPDATE and INSERT to effectively reference the same subquery.
  • UPDATE-WHERE-CURRENT: Syntax not supported. The WHERE clause cannot use an updatable cursor to do what is called a positioned UPDATE. (It is unlikely that this syntax will ever be supported.) Note that this restriction does not prevent cursors from being used in other ways with Atomic upsert statements. For example, a DECLARE CURSOR statement may include upsert statements among those to be executed when the cursor is opened, as long as the upserts are otherwise valid.
  • UPDATE-FROM: Not supported. The SET clause cannot use a FROM clause table reference in the expression for the updated value for a column.
  • UPDATE-WHERE SUBQUERIES: Not supported. The WHERE clause cannot use a subquery either to specify the primary index or to constrain a nonindex column. Note that supporting this UPDATE syntax would also require support for either INSERT … SELECT or some other INSERT syntax feature that lets it specify the same primary index value as the UPDATE.
  • UPDATE-PRIMARY INDEX: Not supported. The UPDATE cannot change the primary index. This is sometimes called unreasonable update.
  • TRIGGERS: Feature not supported if either the UPDATE or INSERT could cause a trigger to be fired. The restriction applies as if the UPDATE and INSERT were both executed, because the parser trigger logic will not attempt to account for their conditional execution. UPDATE triggers on columns not referenced by the UPDATE clause, however, will never be fired by the upsert and are therefore permitted. DELETE triggers cannot be fired at all by an upsert and are likewise permitted. Note that an upsert could be used as a trigger action but it would be subject to the same constraints as any other upsert. Because an upsert is not allowed to fire any triggers itself, an upsert trigger action must not generate any further cascaded trigger actions.
  • JOIN/HASH INDEXES: Feature not supported if either the UPDATE or INSERT could cause the join/hash index to be updated. As with triggers, the restriction applies to each upsert as if the UPDATE and INSERT were both executed. While the UPDATE could escape this restriction if the join/hash index does not reference any of the updated columns, it is much less likely (and maybe impossible) for the INSERT to escape. If the benefit of lifting the restriction for a few unlikely join/hash index cases turns out to be not worth the implementation cost, the restriction may have to be applied more broadly to any table with an associated join/hash index.

Treat the failed constraint as a nonfatal error, report the error in the job log for diagnostic purposes, and continue with the job by reverting to the old non-Atomic upsert protocol.