17.10 - Current Updates - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1182-171K
Language
English (United States)
CURRENT DML modifications can cause serializability issues for concurrent transactions. See Potential Concurrency Issues with Current Temporal DML for information on avoiding these issues.

A current update affects only rows that are valid at the current time. These are rows in valid-time or bitemporal tables with a PV that overlaps current time at the time of the update: Rows that have a valid-time period that contains or begins at TEMPORAL_TIMESTAMP (or TEMPORAL_DATE, depending on the type of the valid-time column).

If additional search conditions are specified in the UPDATE statement, they are applied to these current rows. The search condition may specify conditions on both the valid-time and transaction-time columns.

The following types of rows do not qualify for current updates:
  • Rows in valid-time or bitemporal tables with a PV that ends before or at current time. These rows are history rows in valid time, so do not qualify for a current update.
  • Rows in valid-time or bitemporal tables with a PV that begins after current time. These rows are future rows in valid time, so do not qualify for a current update.
  • Rows in bitemporal tables with a transaction-time period that ends before UNTIL_CLOSED (before 9999-12-31 23:59:59.999999+00:00). These rows are considered closed in transaction time, and are unavailable to most SQL. They are part of the automatic internal history of changes maintained by the database for tables with transaction time, and do not qualify for a current update.

A current update to a row can result in zero, one, or two additional rows being added to the database, depending on the relationship between the PV of the qualifying row and the current time, and on whether the table is a valid-time or bitemporal table.

Current Updates to Qualifying Rows in Valid-Time Tables

If the PV of the row contains the current time at the time of the update, the operation modifies one row and inserts a new row into the table:
  • The valid-time period for the original row, is set to end at the time of the update (TEMPORAL_TIMESTAMP or TEMPORAL_DATE, depending on the type of the valid-time column). No other column values are changed.

    This means that the original row is no longer valid. It becomes a history row, showing the original column values that were valid before the update.

  • A copy of the original row is inserted which has the new values in the updated columns. The valid-time period of this row is set to begin at the time of the update, and end at the same time as the original row.
If the PV of the row begins at the time of the update:
  • The current update operation updates the row.

    The PA of a current update begins at TEMPORAL_TIMESTAMP at the time of the update, and ends at UNTIL_CHANGED, an indefinite time in the future when the row is changed or deleted. Because this PA matches or contains the PV of the qualified row, the change is valid for the entire PV of the row. New rows do not need to be inserted in the database to account for row states that existed before or after the change.

Current updates to Qualifying Rows in Bitemporal Tables

Bitemporal tables include both a valid-time column and a transaction-time column. The results of a current update operation on a bitemporal table with respect to the valid-time column are the same as those for a valid-time table. Due to the transaction-time column, every row that is changed as a result of the current update generates an additional row in the database to track the change in transaction time by creating a snapshot of the row prior to the change:

If the PV of the row contains the current time at the time of the update, the operation modifies one row and inserts two new rows into the table:
  • The transaction-time period for the original row is set to end at the time of the update (TT_TIMESTAMP), marking the row as closed in the transaction-time dimension. No other values are changed in the row.

    This preserves the original row with the original values that existed before the modification, including the original period of validity. Because the row is closed in transaction-time, it becomes inaccessible to further modifications.

  • A copy of the original row is inserted. The row has the valid-time period set to end at the time of the update (TEMPORAL_TIMESTAMP or TEMPORAL_DATE, depending on the type of the valid-time column). No other column values are changed.

    This means that the original row is no longer valid. It becomes a history row, showing the original column values that were valid before the update.

    The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible as a history row to valid-time SQL.

  • A copy of the original row, which has the new values in the updated columns, is inserted.

    The valid-time period of this row is set to begin at the time of the update and end at the same time as the original row.

    The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible to valid-time SQL.

If the PV of the row begins at the time of the update, the operation modifies one row and inserts one new row into the table:
  • The transaction-time period for the original row is set to end at the time of the update (TT_TIMESTAMP), marking the row as closed in the transaction-time dimension. No other values are changed in the row.

    This preserves the original row with the original values that existed before the modification, including the original period of validity. Because the row is closed in transaction-time, it becomes inaccessible to further modifications.

  • The current update operation updates the row.

    A copy of the original row is inserted. The row has the new values in the updated columns. The PA of a current update begins at TEMPORAL_TIMESTAMP at the time of the update, and ends at UNTIL_CHANGED, an indefinite time in the future when the row is changed or deleted. Because this PA matches or contains the PV of the qualified row, the change is valid for the entire PV of the row. New rows do not need to be inserted in the database to account for row states that existed before or after the change.

    The value of the transaction-time column is set to (TT_TIMESTAMP, UNTIL_CLOSED), as it is for any new row that is inserted to a table having a transaction-time dimension. The row is therefore open in transaction time, and remains accessible to valid-time SQL.

Usage Notes

  • The value of TEMPORAL_TIMESTAMP used to stamp the valid-time column is the same for all rows produced as a result of a single update operation.
  • The value of TT_TIMESTAMP used to stamp the transaction-time column is the same for all rows produced as a result of a single update operation.
  • If an update to a qualified row does not actually change any column values in a row, temporal operations that close, open, and create new rows are not performed on the row. However, the activity count of the update operation includes these rows, and an update trigger qualifies these rows.
  • The modified and inserted rows must not violate any constraints on the table. If there are no uniqueness constraints, inserted rows are not checked for duplicates. If the table has any constraints defined, inserted rows are validated to ensure that the rows do not violate the constraints.